SELECT
p.project_code AS 项目编码,
p.project_name AS 项目名称,
count( e.id ) AS 建筑数量,
count( d.id ) AS 设备数量
FROM
`emp_project` p
LEFT JOIN `architecture` e ON p.id = e.`project_id`
AND e.`is_active` = 1
AND e.`level` = 30
AND e.`source_type` = 'Z'
LEFT JOIN `project_device` d ON p.id = d.project_id
AND d.is_active = 1
AND d.device_type IN ( 1, 2, 3, 4 )
WHERE
p.`is_active` = 1
AND p.`source_type` = 'Z'
GROUP BY
p.id
SELECT
p.project_code AS 项目编码,
p.project_name AS 项目名称,
en.encount AS 建筑数量,
de.decount AS 设备数量
FROM
`emp_project` p
LEFT JOIN ( SELECT `project_id`, count(*) AS encount FROM `architecture` WHERE `level` = 30 AND `is_active` = 1
AND `source_type` = 'Z' GROUP BY `project_id` ) en ON p.id = en.`project_id`
LEFT JOIN (
SELECT
`project_id`,
count(*) AS decount
FROM
`project_device`
WHERE
`is_active` = 1
AND `device_type` IN ( 1, 2, 3, 4 )
GROUP BY
`project_id`
) de ON p.id = de.project_id
WHERE
p.`is_active` = 1
AND p.`source_type` = 'Z'
这两段 sql 在同一个库里执行，结果天差地别，但是从连接和分组以及查询条件来看，感觉不出来有什么差异，大佬们能不能一眼丁真帮看下原因
1
Select Language