按所有列分组的SQL

Is there any way to group by all the columns of a table without specifying the column names? Like:

是否有任何方法可以不指定列名而对表的所有列进行分组?如:

select * from table group by *

9 个解决方案

#1

The DISTINCT Keyword

I believe what you are trying to do is:

SELECT DISTINCT * FROM MyFooTable;

If you group by all columns, you are just requesting that duplicate data be removed.

如果对所有列进行分组，您只是请求删除重复的数据。

For example a table with the following data:

例如，一个包含以下数据的表:

 id |     value      
----+----------------
  1 | foo
  2 | bar
  1 | foo
  3 | something else

If you perform the following query which is essentially the same as SELECT * FROM MyFooTable GROUP BY * if you are assuming * means all columns:

如果您执行以下查询，该查询本质上与从MyFooTable GROUP BY *中选择*相同，如果您假设*表示所有列:

SELECT * FROM MyFooTable GROUP BY id, value;

根据id、值从MyFooTable组中选择*;

 id |     value      
----+----------------
  1 | foo
  3 | something else
  2 | bar

It removes all duplicate values, which essentially makes it semantically identical to using the DISTINCT keyword with the exception of the ordering of results. For example:

它删除所有重复的值，这基本上使它在语义上与使用不同的关键字相同，但结果的顺序不同。例如:

SELECT DISTINCT * FROM MyFooTable;

从MyFooTable中选择DISTINCT *;

 id |     value      
----+----------------
  1 | foo
  2 | bar
  3 | something else

#2

If you are using SqlServer the distinct keyword should work for you. (Not sure about other databases)

如果您正在使用SqlServer，那么这个独特的关键字应该适合您。(不确定其他数据库)

declare @t table (a int , b int)

insert into @t (a,b) select 1, 1
insert into @t (a,b) select 1, 2
insert into @t (a,b) select 1, 1

select distinct * from @t

results in

结果

a b
1 1
1 2

#3

He is trying find and display the duplicate rows in a table.

他试图在表中找到并显示重复的行。

SELECT *, COUNT(*) AS NoOfOccurrences
FROM TableName GROUP BY *
HAVING COUNT(*) > 1

Do we have a simple way to accomplish this?

我们有一个简单的方法来完成这个吗?

#4

No because this fundamentally means that you will not be grouping anything. If you group by all columns (and have a properly defined table w/ a unique index) then SELECT * FROM table is essentially the same thing as SELECT * FROM table GROUP BY *.

不，因为这从根本上意味着你不会对任何东西进行分组。如果您对所有列进行分组(并且有一个正确定义的表w/唯一索引)，那么从表中选择*从表中选择*从表组中选择*从表组中选择*从表组by *中选择*。

#5

nope. are you trying to do some aggregation? if so, you could do something like this to get what you need

不。你想要进行聚合吗?如果是的话，你可以这样做来得到你需要的东西

;with a as
(
     select sum(IntField) as Total
     from Table
     group by CharField
)
select *, a.Total
from Table t
inner join a
on t.Field=a.Field

#6

I wanted to do counts and sums over full resultset. I achieved grouping by all with GROUP BY 1=1.

我想对全部结果集进行计数和求和。我以1=1进行分组。

#7

Short answer: no. GROUP BY clauses intrinsically require order to the way they arrange your results. A different order of field groupings would lead to different results.

简短的回答:没有。按子句分组本质上需要按照它们排列结果的方式进行排序。不同的场分组顺序会导致不同的结果。

Specifying a wildcard would leave the statement open to interpretation and unpredictable behaviour.

指定通配符将使该语句具有解释性和不可预知的行为。

#8

You can use Group by All but be careful as Group by All will be removed from future versions of SQL server.

您可以使用Group by All，但要小心，因为Group by All将从SQL server的未来版本中删除。

#9

Here is my suggestion:

这是我的建议:

DECLARE @FIELDS VARCHAR(MAX), @NUM INT

--DROP TABLE #FIELD_LIST

SET @NUM = 1
SET @FIELDS = ''

SELECT 
'SEQ' = IDENTITY(int,1,1) ,
COLUMN_NAME
INTO #FIELD_LIST
FROM Req.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'new340B'

WHILE @NUM <= (SELECT COUNT(*) FROM #FIELD_LIST)
BEGIN
SET @FIELDS = @FIELDS + ',' + (SELECT COLUMN_NAME FROM #FIELD_LIST WHERE SEQ = @NUM)
SET @NUM = @NUM + 1
END

SET @FIELDS = RIGHT(@FIELDS,LEN(@FIELDS)-1)

EXEC('SELECT ' + @FIELDS + ', COUNT(*) AS QTY FROM [Req].[dbo].[new340B] GROUP BY ' + @FIELDS + ' HAVING COUNT(*) > 1  ')