如何在t-sql中使用group with with union

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 with union？我想按联合结果的第一列分组，我写了下面的sql但它不起作用。我只是不知道如何引用union结果的指定列（在本例中为1）。万分感谢。

SELECT  *
FROM    ( SELECT    a.id ,
                    a.time
          FROM      dbo.a
          UNION
          SELECT    b.id ,
                    b.time
          FROM      dbo.b
        )
GROUP BY 1

4 个解决方案

#1

GROUP BY 1

GROUP BY 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 BY on the contents when you're using UNION - UNION ensures that duplicates are removed; UNION ALL is faster because it doesn't - and in that case you would need the GROUP BY...

当您使用UNION时，不需要对内容运行GROUP BY - 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

#2

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

#3

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 UNION rather than UNION ALL, you won't have entirely duplicated rows 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（时间）等等！

#4

-2

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

#1