来自更多表的MySQL连接/联合结果

Question

我有一系列MySQL表，其中第一列是Unix时间格式的日期，第二列是每个表具有不同格式的一些数据。

我使用与此类似的查询按月对数据进行分组：

SELECT YEAR(FROM_UNIXTIME(date)) AS year, MONTH(FROM_UNIXTIME(date)) AS month, SUM(views) AS views,
FROM stats
GROUP BY year, month

我需要什么样的查询才能将所有这些“第二”列输出到按年和月分组的一行中？例如，每个返回的行都包含"year，month，views，column2，column3“……

我尝试过使用UNION，但是，因为“第二”列都不同，所以它为每个表返回一行。例如：

year1, month1, views, 0, 0...  
year1, month1, 0, column2, 0...  
year1, month1, 0, 0, column3...  
year2, month2, views, 0, 0...  
year2, month2, 0, column2, 0...  
year2, month2, 0, 0, column3...

Answer 1

您必须执行类似于以下操作的操作：

SELECT MAX(views) as views,
    MAX(column2) as column2,
    MAX(column3) as column3
FROM (
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        SUM(views) AS views,
        0 AS column2,
        0 AS column3
    FROM stats
    GROUP BY year, month
    UNION
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        0 AS views,
        sum(column2) AS column2,
        0 AS column3
    FROM stats
    GROUP BY year, month
    UNION
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        0 AS views,
        0 AS column2,
        sum(column3) AS column3
    FROM stats
    GROUP BY year, month
)
GROUP BY year, month;

由于每个select包含相同的列，因此您将能够聚合整个内容，并为每一行选择最大值。显然，如果您的数据不是数字或小于0，那么您必须替换一个不同的聚合函数。