从表SQL Server中的每个列获取每个值的计数

小编典典

从表SQL Server中的每个列获取每个值的计数

sql

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

因此，在这个问题中，他们希望每列的计数都不同。我想要做的是获取表中每个列的每个不同值的计数（而我正在为特定数据库中的所有表执行此操作，这就是为什么我要尝试尽可能地自动化这一点的原因尽可能）。当前，我的代码看起来像这样，我必须为每一列运行：

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

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

ColumnName1 Count
val1        24457620
val2        17958530
val3        13350

ColumnName2 Count
val1        24457620
val2        17958530
val3        13350
val4 12

以此类推表中的所有列

以下是上一个问题的以下答案（由@beargle提供）确实与我要执行的操作非常
接近，但我似乎无法找出一种方法将其用于我要执行的操作，因此我会感谢您的帮助。

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, ';'));

阅读 285

2021-04-07

共1个答案

小编典典

您可以使用：

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

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]

编辑

如果要为每个列提供新的结果集，请使用：

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：

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

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

2021-04-07