将具有相同列的不同MySQL表上的数据合并到唯一行中，并对其运行查询

Question

下面是我用来分析MySQL数据库上的服务器日志的代码：

SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_1` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )
UNION
SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_2` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )
UNION
SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_3` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )

这给出了我的结果：

YEAR(datetime)  MONTH( datetime )   MIN(DATE(datetime)) MAX(DATE(datetime)) COUNT(DISTINCT (ip))    COUNT(ip)   Ratio
2015                12              2015-12-14              2015-12-30              16              20              1.2500
2016                1               2016-01-05              2016-01-27              15              20              1.3333
2016                2               2016-02-02              2016-02-29              27              36              1.3333
2016                3               2016-03-04              2016-03-29              24              32              1.3333
2016                4               2016-04-01              2016-04-08              5               8               1.6000
2016                4               2016-04-09              2016-04-29              19              27              1.4211
2016                5               2016-05-02              2016-05-28              21              31              1.4762
2016                6               2016-06-01              2016-06-30              28              34              1.2143
2016                7               2016-07-01              2016-07-20              14              16              1.1429
2016                7               2016-07-21              2016-07-21              1               1               1.0000

这些是每个数据库的准确结果，但是当您看到一个月被分成两个不同的数据库时(如2016-4和2016-7)，这会导致为该月生成2个不同的行。

我希望这些行作为单行生成，其中具有相应月份的值的总和。(每月只有一行)

此外，如果可能，请简化查询。

在2016-12年之后，我会遇到麻烦，因为按月分组将合并2015-12年和2016-12年的数据。我怎样才能避免这个问题呢？

你能写出正确的SQL语句吗？

Answer 1

在group by之前做union all怎么样？

SELECT YEAR(datetime), MONTH(datetime), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM (
    (SELECT datetime, ip FROM server_log_1 WHERE state = 'action' AND user_id = 9) UNION ALL
    (SELECT datetime, ip FROM server_log_2 WHERE state = 'action' AND user_id = 9) UNION ALL
    (SELECT datetime, ip FROM server_log_3 WHERE state = 'action' AND user_id = 9) 
) AS table_all
GROUP BY YEAR(datetime), MONTH(datetime);

在性能方面，您需要为state, user_id上的每个表创建一个索引(可能还需要添加datetime和ip)。