Sql parse bug

[18688194042]

SELECT
ff.project AS 所属迭代,
ff.task_number AS 总任务数,
ff.finish_task AS 完成任务数,
ff.bug_number AS 总bug数,
ff.estimate AS 预计总消耗时间,
ff.consumed AS 开发时长,
ff.save_time AS 节省时间（天）,
ff.finish_ratio AS 任务开发完成率,
ff.bug_ratio AS 任务开发bug率,
dv.count_realname AS 开发总人数,
dv.estimated_hours AS 预计工时,
dv.consumed_hours AS 实际消耗,
dv.development_efficiency AS 开发效率,
dv.development_load AS 开发负载
FROM (
-- 原第一个子查询
SELECT
p2.name AS project,
COUNT(b.id) AS task_number,
IFNULL(SUM(CASE WHEN concat(p2.end," 23:59:59") >= b.finishedDate
AND b.estimate >= b.consumed
AND b.status != 'closed'
AND b.finishedDate IS NOT NULL THEN 1 ELSE 0 END), 0) AS finish_task,
a.bug AS bug_number,
ROUND(SUM(b.estimate), 1) AS estimate,
CONCAT(TRUNCATE(
(SUM(b.estimate) - SUM(b.consumed)) / 8,
1
), "") AS save_time,
ROUND(SUM(b.consumed), 1) AS consumed,
ROUND(
IFNULL(SUM(CASE WHEN concat(p2.end," 23:59:59") >= b.finishedDate
AND b.estimate >= b.consumed
AND b.status != 'closed'
AND b.finishedDate IS NOT NULL THEN 1 ELSE 0 END), 0)
/ NULLIF(COUNT(b.id), 0) * 100, 1) AS finish_ratio,
ROUND((IFNULL(a.bug, 0) / NULLIF(SUM(CASE WHEN b.finishedDate IS NOT NULL THEN 1 ELSE 0 END), 0)) * 100, 1) AS bug_ratio
FROM
zt_task b
LEFT JOIN zt_project AS p2 ON b.execution = p2.id
LEFT JOIN zt_user ul ON b.assignedTo = ul.account
LEFT JOIN zt_dept zd ON ul.dept = zd.id
LEFT JOIN (
SELECT
p3.name AS project,
COUNT(b.id) AS bug
FROM
zt_bug AS b
LEFT JOIN zt_project AS p3 ON b.execution = p3.id
LEFT JOIN zt_user u1 ON u1.account = b.resolvedBy
WHERE
b.type NOT IN ("task","others","product")
GROUP BY
p3.name
) AS a ON a.project = p2.name
WHERE
b.status != 'closed'
AND ul.realname NOT IN ("唐督川","李骏","obAdmin","张富达","彭宇","黄剑雄")
AND b.deleted != "1"
AND b.project = 9
AND p2.end > ${开始时间} AND p2.end < ${结束时间}
AND $CURR_USER_NAME$ NOT IN ("chengjinfu","yangbinbin","yangxiaoqing","zhengyanyun","lizhengwen","zhangjishi","zhuwenzhen")
GROUP BY
p2.name, a.bug
) AS ff
JOIN (
-- 原第二个子查询
SELECT
p2.name AS iteration_name,
dc.count_realname,
ROUND(SUM(b.estimate), 2) AS estimated_hours,
ROUND(SUM(b.consumed), 2) AS consumed_hours,
ROUND(SUM(b.estimate) / NULLIF(SUM(b.consumed), 0), 2) AS development_efficiency,
ROUND((((SUM(b.consumed) - dc.count_realname * 32) + dc.count_realname * 8) / (dc.count_realname * 8)), 2) AS development_load
FROM
zt_task AS b
LEFT JOIN zt_project AS p2 ON b.execution = p2.id
LEFT JOIN zt_user ul ON b.assignedTo = ul.account
LEFT JOIN zt_dept zd ON ul.dept = zd.id
LEFT JOIN (
SELECT
p.name AS iteration_name,
COUNT(DISTINCT u.realname) AS count_realname,
SUM(t.consumed) AS total_consumed
FROM zt_task AS t
LEFT JOIN zt_project AS p ON t.execution = p.id
LEFT JOIN zt_user AS u ON t.assignedTo = u.account
WHERE
u.realname NOT IN ("唐督川", "李骏", "obAdmin", "黄剑雄")
AND t.deleted != "1"
AND p.parent = 9
AND t.finishedDate IS NOT NULL
GROUP BY p.name
) AS dc ON p2.name = dc.iteration_name
WHERE
ul.realname NOT IN ("唐督川", "李骏", "obAdmin", "黄剑雄","张富达")
AND b.deleted != "1"
AND p2.parent = 9
AND b.finishedDate IS NOT NULL
GROUP BY
p2.name,dc.count_realname
) AS dv ON ff.project = dv.iteration_name
ORDER BY
ff.project