mysql - 如何在 MySQL 中正确使用 group by <>？

Question

下面是我表中的数据

| count(*) | system |
|        2 | sys1   |
|        7 | sys2   |
|        6 | sys1   |

我想要如下输出

| count(*) | system |
|        8 | sys1   |
|        7 | sys2   |

我该怎么做？
下面是我正在使用的查询。谁能告诉我这有什么问题吗？

select * from 

(select count(*),system from tbl1 where creationDate < CURDATE() AND creationDate >= CURDATE() - INTERVAL 1 DAY group by system 

UNION ALL 

select count(*),system from tbl2 where creationDate < CURDATE() AND creationDate >= CURDATE() - INTERVAL 1 DAY group by system) as tbl3 group by system;

Answer 1

您需要使用子查询来实现:

试试这个:

SELECT SUM(Total) AS `Count`, system
FROM
(
    SELECT COUNT(*) AS Total, system FROM tbl1 
            WHERE creationDate < CURDATE() 
              AND creationDate >= CURDATE() - INTERVAL 1 DAY GROUP BY system
    UNION ALL
     SELECT COUNT(*) AS Total, system FROM tbl2 
            WHERE creationDate < CURDATE() 
              AND creationDate >= CURDATE() - INTERVAL 1 DAY GROUP BY system
) A
GROUP BY system

输出:

| COUNT | system |
|     8 | sys1   |
|     7 | sys2   |