如何在t-sql中使用group by [英] How to use group by with union in t-sql

问题描述

如何通过t-sql中的union使用group？我想按照联合的结果的第一列进行分组，我写了下面的sql，但它不起作用。我只是不知道如何引用联合结果的指定列（在本例中为1）。
非常感谢。

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.

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

推荐答案

GROUP BY 1

GROUP BY 1

我从来不知道GROUP BY支持使用序号，只有ORDER BY。无论哪种方式，只有MySQL支持GROUP BY不包括没有对其执行聚合函数的所有列。不推荐使用惯例，因为如果它们基于SELECT的顺序 - 如果这种变化，你的ORDER BY（或者GROUP BY，如果支持的话）也是如此。

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).

在使用 UNION 时，不需要在内容上运行 GROUP BY - UNION确保重复项是删除; UNION ALL 会更快，因为它不会 - 在这种情况下，您需要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...

您的查询只需要：

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

这篇关于如何在t-sql中使用group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！