MySQL SQL：计算每列中不同值的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1363576/
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: count number of distinct values in every column
提问by Ryan
I need a query that will return a table where each column is the count of distinct values in the columns of another table.
我需要一个查询来返回一个表，其中每一列是另一个表的列中不同值的计数。
I know how to count the distinct values in one column:
我知道如何计算一列中的不同值：
select count(distinct columnA) from table1;
I suppose that I could just make this a really long select clause:
我想我可以把它变成一个很长的选择子句：
select count(distinct columnA), count(distinct columnB), ... from table1;
but that isn't very elegant and it's hardcoded. I'd prefer something more flexible.
但这不是很优雅，而且是硬编码的。我更喜欢更灵活的东西。
采纳答案by Ryan
I appreciate all of the responses. I think the solution that will work best for me in this situation (counting the number of distinct values in each column of a table from an external program that has no knowledge of the table except its name) is as follows:
我感谢所有的回应。我认为在这种情况下最适合我的解决方案（计算来自外部程序的表的每一列中不同值的数量，该程序除了名称外不知道该表）如下：
Run "describe table1" and pull out the column names from the result.
运行“describe table1”并从结果中提取列名。
Loop through the column names and create the query to count the distinct values in each column. The query will look something like "select count(distinct columnA), count(distinct columnB), ... from table1".
遍历列名并创建查询以计算每列中的不同值。查询将类似于“选择计数（不同列A），计数（不同列B），...来自表1”。
回答by KM.
try this (sql server 2005 syntax):
试试这个（sql server 2005 语法）：
DECLARE @YourTable table (col1 varchar(5)
,col2 int
,col3 datetime
,col4 char(3)
)
insert into @YourTable values ('abcdf',123,'1/1/2009','aaa')
insert into @YourTable values ('aaaaa',456,'1/2/2009','bbb')
insert into @YourTable values ('bbbbb',789,'1/3/2009','aaa')
insert into @YourTable values ('ccccc',789,'1/4/2009','bbb')
insert into @YourTable values ('aaaaa',789,'1/5/2009','aaa')
insert into @YourTable values ('abcdf',789,'1/6/2009','aaa')
;with RankedYourTable AS
(
SELECT
ROW_NUMBER() OVER(PARTITION by col1 order by col1) AS col1Rank
,ROW_NUMBER() OVER(PARTITION by col2 order by col2) AS col2Rank
,ROW_NUMBER() OVER(PARTITION by col3 order by col3) AS col3Rank
,ROW_NUMBER() OVER(PARTITION by col4 order by col4) AS col4Rank
FROM @YourTable
)
SELECT
SUM(CASE WHEN col1Rank=1 THEN 1 ELSE 0 END) AS col1DistinctCount
,SUM(CASE WHEN col2Rank=1 THEN 1 ELSE 0 END) AS col2DistinctCount
,SUM(CASE WHEN col3Rank=1 THEN 1 ELSE 0 END) AS col3DistinctCount
,SUM(CASE WHEN col4Rank=1 THEN 1 ELSE 0 END) AS col4DistinctCount
FROM RankedYourTable
OUTPUT:
输出：
col1DistinctCount col2DistinctCount col3DistinctCount col4DistinctCount
----------------- ----------------- ----------------- -----------------
4 3 6 2
(1 row(s) affected)
回答by Raj More
This code should give you all the columns in 'table1' with the respective distinct value count for each one as data.
此代码应该为您提供“table1”中的所有列，并为每个列提供各自不同的值计数作为数据。
DECLARE @TableName VarChar (Max) = 'table1'
DECLARE @SqlString VarChar (Max)
set @SqlString = (
SELECT DISTINCT
'SELECT ' +
RIGHT (ColumnList, LEN (ColumnList)-1) +
' FROM ' + Table_Name
FROM INFORMATION_SCHEMA.COLUMNS COL1
CROSS AppLy (
SELECT ', COUNT (DISTINCT [' + COLUMN_NAME + ']) AS ' + '''' + COLUMN_NAME + ''''
FROM INFORMATION_SCHEMA.COLUMNS COL2
WHERE COL1.TABLE_NAME = COL2.TABLE_NAME
FOR XML PATH ('')
) TableColumns (ColumnList)
WHERE
1=1 AND
COL1.TABLE_NAME = @TableName
)
EXECUTE (@SqlString)
回答by Amy B
and it's hardcoded.
它是硬编码的。
It is not hardcoding to provide a field list for a sql statement. It's common and acceptable practice.
为 sql 语句提供字段列表并不是硬编码。这是常见且可接受的做法。
回答by MartW
This won't necessarily be possible for every field in a table. For example, you can't do a DISTINCT against a SQL Server ntext or image field unless you cast them to other data types and lose some precision.
这不一定适用于表中的每个字段。例如，您不能对 SQL Server ntext 或 image 字段执行 DISTINCT，除非您将它们转换为其他数据类型并失去一些精度。
回答by Cristian Cotovan
DISTINCT is evil. Do COUNT/GROUP BY
DISTINCT 是邪恶的。进行计数/分组
