mysql – 将多个计数组合到一个查询中 – group by
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 将多个计数组合到一个查询中 – group by,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2635字,纯文字阅读大概需要4分钟。
内容图文
我有3个SELECT语句,我想组合成一个:
SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname
FROM feedbacks A
INNER JOIN users B
ON A.userid = B.userid
WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
GROUP BY fullname
SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname
FROM feedbacks A
INNER JOIN users B
ON A.userid = B.userid
WHERE status = 'C'
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
GROUP BY fullname`
SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname
FROM feedbacks A
INNER JOIN users B ON A.userid = B.userid
WHERE caused_change = 1
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
GROUP BY fullname
但是组合它们似乎总是会返回一个错误:(操作数应该包含1列)
SELECT
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T1,
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T2,
(SELECT COUNT(A.id), CONCAT(B.fname,' ', B.lname) AS fullname FROM feedbacks A INNER JOIN users B ON A.userid = B.userid WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11' GROUP BY fullname) AS T3
所以我尝试从单个语句中删除JOIN和GROUP来制作:
SELECT CONCAT(B.fname, ' ', B.lname) AS fullname,
(SELECT COUNT(A.id) FROM feedbacks A WHERE DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T1,
(SELECT COUNT(A.id) FROM feedbacks A WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T2,
(SELECT COUNT(A.id) FROM feedbacks A WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11') AS T3
FROM feedbacks
INNER JOIN users B
ON feedbacks.userid = B.userid
GROUP BY fullname
但是,这会返回所有内容的总计与用户的细分(因为计数在where子句中没有说明有关userid或fullname的内容,毫无疑问).
我觉得我很亲密但却缺少一些东西.谁能指出我在这里的正确方向?我只是想了解我做错了什么.
感谢您的时间.
解决方法:
最好的方法是使用条件SUM:
SELECT CONCAT(B.fname, ' ', B.lname) AS fullname,
SUM(
CASE WHEN DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
THEN 1 ELSE 0 END
) AS T1,
SUM(
CASE WHEN status = 'C'
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
THEN 1 ELSE 0 END
) AS T2,
SUM(
CASE WHEN caused_change = 1
AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
THEN 1 ELSE 0 END
) AS T3
FROM feedbacks
INNER JOIN users B
ON feedbacks.userid = B.userid
GROUP BY fullname
内容总结
以上是互联网集市为您收集整理的mysql – 将多个计数组合到一个查询中 – group by全部内容,希望文章能够帮你解决mysql – 将多个计数组合到一个查询中 – group by所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。