mysql - 使用 where 子句和连接聚合 sql

Question

抱歉，这与我之前的问题有关。 https://stackoverflow.com/q/29523379/2240163

但我想我必须问一个新问题。

SELECT adm.adm_Consultant, surg.Type AS 'Surgery Type', surg.srg_Details AS 'Procedure', count(surg.srg_Details) as SurgeryNum
FROM Admission adm
JOIN Link lnk ON lnk.lnk_admID = adm.adm_ID
JOIN Surgery surg ON surg.srg_lnkID = lnk.lnk_ID
WHERE  adm.adm_ReferralDate >= '2014-01-01' AND adm.adm_ReferralDate <= '2014-12-31'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY surg.srg_Details

为什么会失败，你甚至可以做这样的聚合查询吗？

这行得通，但我想尝试计算过程的

SELECT adm.adm_Consultant, surg.Type AS 'Surgery Type', surg.srg_Details AS 'Procedure'
FROM Admission adm
JOIN Link lnk ON lnk.lnk_admID = adm.adm_ID
JOIN Surgery surg ON surg.srg_lnkID = lnk.lnk_ID
WHERE  adm.adm_ReferralDate >= '2014-01-01' AND adm.adm_ReferralDate <= '2014-12-31'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
ORDER BY surg.srg_Details

Answer 1

尝试将 SELECT 子句中的所有列添加到 GROUP BY 子句:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

您可以阅读 documentation .

SELECT adm.adm_Consultant, surg.Type AS 'Surgery Type', surg.srg_Details AS 'Procedure', count(surg.srg_Details) as SurgeryNum
FROM Admission adm
JOIN Link lnk ON lnk.lnk_admID = adm.adm_ID
JOIN Surgery surg ON surg.srg_lnkID = lnk.lnk_ID
WHERE  adm.adm_ReferralDate >= '2014-01-01' AND adm.adm_ReferralDate <= '2014-12-31'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY adm.adm_Consultant, surg.Type, surg.srg_Details