获取表 SQL Server 中每一列的每个值的计数 [英] get a count of each value from every column in a table SQL Server

问题描述

所以我查了一下，这个问题非常相似，但缺少一个关键部分:SQL Server 计算表的每一列中不同值的数量

So I looked this up and this question is very similar but it's missing a key piece: SQL Server count number of distinct values in each column of a table

所以在那个问题中，他们想要每列的不同计数.我想要做的是获取表中每一列的每个不同值的计数(我正在为特定数据库中的所有表执行此操作，这就是为什么我希望尝试尽可能多地自动化尽可能).目前我的代码看起来像这样，我必须为每一列运行:

So in that question they want the distinct count for each column. What I am looking to do is to get a count of each distinct value for each column in a table (and I'm doing this for all the tables in a particular database which is why I'm looking to try to automate this as much as possible). Currently my code looks like this which I have to run for each column:

select mycol1, COUNT(*) as [Count] 
from mytable
group by mycol1
order by [Count] desc

理想情况下，我的输出如下所示:

Ideally my output would look like this:

ColumnName1 Count
val1        24457620
val2        17958530
val3        13350

ColumnName2 Count
val1        24457620
val2        17958530
val3        13350
val4 12

对表中的所有列以此类推

and so on for all the columns in the table

以下来自上一个问题的答案(由@beargle 提供)真的接近我想要做的，但我似乎无法找到让它工作的方法对于我正在尝试做的事情，我将不胜感激.

This answer below (provided by @beargle) from that previous question is really close to what I'm looking to do but I can't seem to figure out a way to get it to work for what I am trying to do so I would appreciate any help.

DECLARE @Table SYSNAME = 'TableName'; 

-- REVERSE and STUFF used to remove trailing UNION in string
SELECT REVERSE(STUFF(REVERSE((SELECT 'SELECT ''' + name 
                                     + ''' AS [Column], COUNT(DISTINCT(' 
                                     + QUOTENAME(name) + ')) AS [Count] FROM ' 
                                     + QUOTENAME(@Table) + ' UNION ' 
                              -- get column name from sys.columns  
                              FROM   sys.columns 
                              WHERE  object_id = Object_id(@Table)
                              -- concatenate result strings with FOR XML PATH
                              FOR XML PATH (''))), 1, 7, ';'));

推荐答案

您可以使用:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name)
                    FROM   sys.columns 
                    WHERE  object_id = Object_id(@Table)
                    -- concatenate result strings with FOR XML PATH
                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

对于具有两列(Column1 和 Column2)的表，这将产生如下所示的 SQL

Which will produce SQL Like the following for a table with two columns (Column1 and Column2)

SELECT 'Column1' AS [Column], 
        CAST([Column1] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column1] 
UNION 
SELECT  'Column2' AS [Column], 
        CAST([Column2] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column2]

<小时>

编辑

如果您想为每一列设置一个新的结果集，请使用:

If you want a new result set for each column then use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = (SELECT ' SELECT ' + QUOTENAME(Name) 
                        + ', COUNT(*) AS [Count] FROM ' 
                        + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name) + ';'
                FROM   sys.columns 
                WHERE  object_id = Object_id(@Table)
                -- concatenate result strings with FOR XML PATH
                FOR XML PATH (''));

EXECUTE sp_executesql @SQL;

哪个会产生 SQL Like:

Which would produce SQL Like:

SELECT  [Column1], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column1];

SELECT  [Column2], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column2];

这篇关于获取表 SQL Server 中每一列的每个值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！