如何在 t-sql 中使用 group by 和 union
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1604815/
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
How to use group by with union in t-sql
提问by Just a learner
How can I using group by with union in t-sql? I want to group by the first column of a result of union, I wrote the following sql but it doesn't work. I just don't know how to reference the specified column (in this case is 1) of the union result. great thanks.
如何在 t-sql 中使用 group by 和 union？我想按联合结果的第一列进行分组，我写了以下 sql 但它不起作用。我只是不知道如何引用联合结果的指定列（在本例中为 1）。万分感谢。
SELECT *
FROM ( SELECT a.id ,
a.time
FROM dbo.a
UNION
SELECT b.id ,
b.time
FROM dbo.b
)
GROUP BY 1
采纳答案by OMG Ponies
GROUP BY 1
按 1 分组
I've never known GROUP BY to support using ordinals, only ORDER BY. Either way, only MySQL supports GROUP BY's not including all columns without aggregate functions performed on them. Ordinals aren't recommended practice either because if they're based on the order of the SELECT - if that changes, so does your ORDER BY (or GROUP BY if supported).
我从来不知道 GROUP BY 支持使用序数，只有 ORDER BY。无论哪种方式，只有 MySQL 支持 GROUP BY 不包括没有对它们执行聚合函数的所有列。也不推荐使用序数，因为如果它们基于 SELECT 的顺序 - 如果发生变化，那么您的 ORDER BY（或 GROUP BY 如果支持）也会发生变化。
There's no need to run GROUP BYon the contents when you're using UNION- UNION ensures that duplicates are removed; UNION ALLis faster because it doesn't - and in that case you would need the GROUP BY...
GROUP BY使用时无需对内容运行UNION- UNION 确保删除重复项；UNION ALL更快，因为它没有 - 在这种情况下，您将需要 GROUP BY ...
Your query only needs to be:
您的查询只需：
SELECT a.id,
a.time
FROM dbo.TABLE_A a
UNION
SELECT b.id,
b.time
FROM dbo.TABLE_B b
回答by Thomas
You need to alias the subquery. Thus, your statement should be:
您需要为子查询添加别名。因此，您的陈述应该是：
Select Z.id
From (
Select id, time
From dbo.tablea
Union All
Select id, time
From dbo.tableb
) As Z
Group By Z.id
回答by Alex Martelli
Identifying the column is easy:
识别列很容易：
SELECT *
FROM ( SELECT id,
time
FROM dbo.a
UNION
SELECT id,
time
FROM dbo.b
)
GROUP BY id
But it doesn't solve the main problem of this query: what's to be done with the second column values upon grouping by the first? Since (peculiarly!) you're using UNIONrather than UNION ALL, you won't have entirely duplicatedrows between the two subtables in the union, but you may still very well have several values of time for one value of the id, and you give no hint of what you want to do - min, max, avg, sum, or what?! The SQL engine should give an error because of that (though some such as mysql just pick a random-ish value out of the several, I believe sql-server is better than that).
但这并没有解决这个查询的主要问题：在按第一列分组后，第二列值要做什么？由于（独有的！）你正在使用UNION，而不是UNION ALL，你将不会完全复制在工会两个子表之间的行，但你仍然很可能有几个时间值的一个ID值，你给不提示您想要做什么 - 最小、最大、平均、总和，还是什么？！因此，SQL 引擎应该给出一个错误（尽管一些如 mysql 只是从几个中选择一个随机值，我相信 sql-server 比那个更好）。
So, for example, change the first line to SELECT id, MAX(time)or the like!
因此，例如，将第一行更改为之SELECT id, MAX(time)类的！
回答by Songe Nsunza Jr.
with UnionTable as
(
SELECT a.id, a.time FROM dbo.a
UNION
SELECT b.id, b.time FROM dbo.b
) SELECT id FROM UnionTable GROUP BY id
