请教一个 SQL 的问题，来个大佬帮忙丁真下

[bingfengCoder]

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 在同一个库里执行，结果天差地别，但是从连接和分组以及查询条件来看，感觉不出来有什么差异，大佬们能不能一眼丁真帮看下原因

[bingfengCoder]

@liprais 能展开说下吗，想知道问题出在哪里

[sorcerer]

最简单的情况
表 1 一条记录 表二满足 join 条件的有两条记录 也就是 project id 一样的有两条 表三 project id 一样的有 3 条

这样通过方法 1 关连后 最终表里有 6 条记录 ,count 表二和表三的 proj id 都等于 6 方法二最终结果只有一条,凑 count 表二 表三的结果分边为 2 和 3

[cccmm]

architecture 表或 project_device 表的 project_id 有重复？

[kkwa56188]

第一个: 你 group by 什么字段, 就只能 select 什么字段, 其余列需要是 agg 函数, 不知道怎么跑的起来的.

第二个: (看了一半 算了, 你为什么要这么写, 还不如重写, 见下面三)

三: 这本身是个简单的查询, p 主表就不动它了, 不 join 不 GroupBy, 两个子表的话 再在子查询里 join 完了再 count 就好了,
这样: select p.id, ( 子查询 1 select count(e.id) ), ( 子查询 2 select count(d.id)) from p

[xuanbg]

@bingfengCoder 第一种的 count 其实是右表的行数，第二种 count 的是右表 id 的个数。
在 count 函数里面加 distinct ，写成以下的代码，结果就一致了：
SELECT
p.project_code AS 项目编码,
p.project_name AS 项目名称,
count(distinct e.id ) AS 建筑数量,
count(distinct 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

[bingfengCoder]

@xuanbg 试了下，结果一致了，感谢大佬

[bingfengCoder]

@kkwa56188 25 楼 大佬给出了 详细的写法，group by 可以跑起来的，结果一致了

[M48A1]

@ZZ74 相同想法，第一个 group by 为什么不报错…

[bingfengCoder]

@M48A1 现在第一个 group by 不仅不会报错，还能得到正确的结果了 详见 25 楼 XD

[bingfengCoder]

不知道为什么大家会把第一种 group by 主表字段 当成邪教用法，但是通过有效的 left join 可以很好的避免子查询产生的次数，我不太喜欢写很多个子查询然后 一个个 join ，更倾向直接 join 然后一把 group by 梭，感觉要来的更加方便

[M48A1]

@bingfengCoder 什么版本呀，我被淘汰了
server sql 必须加满 group by.

[bingfengCoder]

@M48A1 用的 mysql 5.7 ,mysql 8 加了 ONLY_FULL_GROUP_BY 模式 也是需要加满的

[Richared]

没仔细看，但是我感觉第一个种写法 count 的值有重复的。去重下？