将多个计数合并为一个查询 - 按组分组

Question

我有三个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，毫无疑问）。

我感觉我接近成功但还是缺少什么。有没有人能指点我正确的方向？我只是想知道我的错误在哪里。

谢谢您的时间。

- greatcaesarsghost

Answer 1

最好的方法是使用条件 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

- Cyril Gandon

---

谢谢！今天这个真的非常有帮助。 - Brian Armstrong

Answer 2

我不知道你是否可以将这三个查询合并为一个统一的查询。你有三个“where子句”重叠：

第一个查询：在日期范围内的所有记录
第二个查询：在日期范围内的所有记录，但状态也为“C”
第三个查询：在日期范围内的所有记录，但也导致更改=1

从逻辑上讲，第一个查询已经包含了第二个和第三个查询的所有记录，因此您会重复计算＃2和＃3的结果。

但是，如果您确实想将所有三个组合成单个结果，则使用外部查询方法：

SELECT sum(cnt), fullname
FROM (
    SELECT COUNT(A.id) as cnt, 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

    UNION

    ...
    WHERE status = 'C' AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'

    UNION

    ...
    WHERE caused_change = 1 AND DATE(origindate) BETWEEN '2011-03-01' AND '2011-07-11'
) AS innerquery
GROUP BY fullname

请注意第一个内部查询中count()的别名。这是为了使计数字段在外部查询中显示为“cnt”。

- Marc B

---

之前我是通过 PHP 获取该日期范围内的所有结果，并使用 PHP 执行逻辑和计数。有人建议我应该让数据库来处理这些繁重的任务。感谢您花时间解释。 - greatcaesarsghost

Answer 3

UNION和SUM应该可以完成这项工作。你的代码应该像这样：

 select sum(x.col1), x.fullname
from
(SELECT COUNT(A.id)as col1, 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
UNION
SELECT COUNT(A.id)as col1, 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
UNION
SELECT COUNT(A.id)as col1, 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
)x
group by x.fullname

- kutluk