按所有列分组的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/800910/
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
SQL grouping by all the columns
提问by
Is there any way to group by all the columns of a table without specifying the column names? Like:
有没有办法在不指定列名的情况下按表的所有列进行分组？喜欢：
select * from table group by *
回答by Elijah
The DISTINCT Keyword
DISTINCT 关键字
我相信你想要做的是：
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:
如果您执行以下查询，这与SELECT * FROM MyFooTable GROUP BY *您假设 * 表示所有列基本相同：
SELECT * FROM MyFooTable GROUP BY id, value;
SELECT * FROM MyFooTable GROUP BY id, value;
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:
它删除了所有重复值，这在本质上使其与使用 DISTINCT 关键字的语义相同，但结果的排序除外。例如：
SELECT DISTINCT * FROM MyFooTable;
SELECT DISTINCT * FROM MyFooTable;
id | value
----+----------------
1 | foo
2 | bar
3 | something else
回答by Girish
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?
我们有一个简单的方法来实现这一点吗？
回答by mjallday
If you are using SqlServer the distinct keyword should work for you. (Not sure about other databases)
如果您使用的是 SqlServer，distinct 关键字应该适合您。（不确定其他数据库）
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
回答by DForck42
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
回答by jellomonkey
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 tableis essentially the same thing as SELECT * FROM table GROUP BY *.
不，因为这从根本上意味着您不会对任何内容进行分组。如果您按所有列分组（并且有一个正确定义的带有唯一索引的表），那么SELECT * FROM table本质上与SELECT * FROM table GROUP BY *.
回答by Vojtěch
I wanted to do counts and sums over full resultset. I achieved grouping by all with GROUP BY 1=1.
我想对完整的结果集进行计数和求和。我用GROUP BY 1=1.
回答by womp
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.
简短的回答：没有。GROUP BY 子句本质上需要按照它们排列结果的方式进行排序。不同的字段分组顺序会导致不同的结果。
Specifying a wildcard would leave the statement open to interpretation and unpredictable behaviour.
指定通配符将使语句易于解释和不可预测的行为。
回答by NeverSayNever
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 ')
回答by Ravi
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 的未来版本中删除。
