SQL 使用 UNION ALL 分组和排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27819047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group By and Order By with UNION ALL
提问by HelpASisterOut
I have a stored procedure with the following query:
我有一个带有以下查询的存储过程：
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
I need to do a union all, so I could get sum of results but from TWO tables, and I want the result to be grouped by and ordered by the same way.
我需要做一个union all，所以我可以从两个表中得到结果的总和，我希望结果以相同的方式分组和排序。
This doesn't work:
这不起作用：
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
UNION ALL
SELECT (sum(addition)) AS [COUNT],
MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
AND [ServiceID]=@serid
GROUP BY CONVERT(VARCHAR(12),CREATED,102)
ORDER BY CONVERT(VARCHAR(12),CREATED,102)
I want to order the overall result, and group it by the date.
我想对整体结果进行排序，并按日期对其进行分组。
回答by Mureinik
You could apply the group byand order byafter the union all:
您可以应用group by和order by之后union all：
SELECT (SUM(addition)) AS [COUNT], MAX([date]) AS [max_date]
FROM (SELECT addition, CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_ONE]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid
UNION ALL
SELECT addition, (CONVERT(VARCHAR(12),CREATED,102)) as [date]
FROM [TABLE_TWO]
WHERE CONVERT(VARCHAR(12),CREATED,102)
BETWEEN CONVERT(date,@startdate) AND
CONVERT(date,@enddate)
AND [ServiceID]=@serid) t
GROUP BY [date]
ORDER BY 2
