在实际开发中我们可能需要如下的查询:
先执行Group By 语句查询出结果,再使用Join 方式 将其它数据聚合进来,示例代码如
SELECT a.*,u.nick_name FROM
(
SELECT
s.invUsrId AS userId,
count(1) AS cnt,
sum(s.amount) AS amount
FROM bus_subs s
WHERE (s.status ='01' OR s.status='04')
GROUP BY s.invUsrId
) a
LEFT JOIN user_info u on a.userId = u.uId
WHERE u.nick_name IS NOT NULL ;
经测试发现在MySQL支持在group by 之前进行连接操作,并且连接操作的列不必存在于group by 中,以上的查询可以修改为如下方式
SELECT
s.invUsrId AS userId,
count(1) AS cnt,
sum(s.amount) AS amount,
u.nickName as nickName
FROM bus_subs s
LEFT user_info u on s.invUsrId = u.uId
WHERE (s.status ='01' OR s.status='04') and u.nickName IS NOT NULL
GROUP BY s.invUsrId;
可以看到u.nickName 没有出现在group by 子句中,结果同上面的查询完全相同.
Note: 优先级问题
(s.status ='01' OR s.status='04') and u.nickName IS NOT NULL
如果前面两个没有加括号,会被SQL引擎解析为
s.status ='01' OR (s.status='04' and u.nickName IS NOT NULL)