```
SELECT su.real_name AS realName,
sd.name AS deptName, su.user_name AS userName, ahh.*
FROM (
SELECT
ROUND(SUM(IF(DAY(ah.audit_time) = '1', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '1'
, ROUND(SUM(IF(DAY(ah.audit_time) = '2', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '2'
, ROUND(SUM(IF(DAY(ah.audit_time) = '3', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '3'
, ROUND(SUM(IF(DAY(ah.audit_time) = '4', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '4'
, ROUND(SUM(IF(DAY(ah.audit_time) = '5', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '5'
, ROUND(SUM(IF(DAY(ah.audit_time) = '6', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '6'
, ROUND(SUM(IF(DAY(ah.audit_time) = '7', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '7'
, ROUND(SUM(IF(DAY(ah.audit_time) = '8', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '8'
, ROUND(SUM(IF(DAY(ah.audit_time) = '9', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '9'
, ROUND(SUM(IF(DAY(ah.audit_time) = '10', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '10'
, ROUND(SUM(IF(DAY(ah.audit_time) = '11', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '11'
, ROUND(SUM(IF(DAY(ah.audit_time) = '12', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '12'
, ROUND(SUM(IF(DAY(ah.audit_time) = '13', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '13'
, ROUND(SUM(IF(DAY(ah.audit_time) = '14', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '14'
, ROUND(SUM(IF(DAY(ah.audit_time) = '15', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '15'
, ROUND(SUM(IF(DAY(ah.audit_time) = '16', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '16'
, ROUND(SUM(IF(DAY(ah.audit_time) = '17', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '17'
, ROUND(SUM(IF(DAY(ah.audit_time) = '18', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '18'
, ROUND(SUM(IF(DAY(ah.audit_time) = '19', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '19'
, ROUND(SUM(IF(DAY(ah.audit_time) = '20', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '20'
, ROUND(SUM(IF(DAY(ah.audit_time) = '21', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '21'
, ROUND(SUM(IF(DAY(ah.audit_time) = '22', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '22'
, ROUND(SUM(IF(DAY(ah.audit_time) = '23', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '23'
, ROUND(SUM(IF(DAY(ah.audit_time) = '24', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '24'
, ROUND(SUM(IF(DAY(ah.audit_time) = '25', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '25'
, ROUND(SUM(IF(DAY(ah.audit_time) = '26', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '26'
, ROUND(SUM(IF(DAY(ah.audit_time) = '27', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '27'
, ROUND(SUM(IF(DAY(ah.audit_time) = '28', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '28'
, ROUND(SUM(IF(DAY(ah.audit_time) = '29', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '29'
, ROUND(SUM(IF(DAY(ah.audit_time) = '30', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '30'
, ROUND(SUM(IF(DAY(ah.audit_time) = '31', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '31'
, SUM(ROUND(ah.audit_num * ifnull(kpi.kpi_value, 1), 2)) AS auditCount
, ah.account_number AS empNumber, ah.emp_name AS employeeName
, SUM(ROUND(ah.pass_num * ifnull(kpi.kpi_value, 1), 2)) AS passCount
, SUM(ROUND(ah.delete_Num * ifnull(kpi.kpi_value, 1), 2)) AS delCount
FROM audit_days ah
LEFT JOIN skill_group_kpi kpi
ON ah.group_name = kpi.group_name
AND kpi.is_deleted = 0
WHERE 1 = 1
GROUP BY ah.account_number
ORDER BY NULL
) ahh
LEFT JOIN sys_user su
ON su.qq LIKE concat('%', ahh.empNumber, '%')
AND ahh.empNumber != 0
LEFT JOIN sys_department sd ON
sd.id = su.department_id
```
我看了你的 SQL,特么有个问题想问下你
```
ON su.qq LIKE concat('%', ahh.empNumber, '%')
```
关联表的时候不应该强关联么? 怎么还有一个 on like? 哪个大神能解释一下?
其次
自己跑下解析计划,就不就知道哪里慢了? 我这里有没有数据,光看 SQL 估计有点难,目前就看出来了一个 on like 很叼,叼爆了