Question

我需要一个查询，可以返回一个表格，其中每列都是另一个表格列中不同值的计数。

我知道如何在一栏中计算不同的数值:

select count(distinct columnA) from table1;

我认为,我只能把这一条款变成一个真正的长期选择条款:

select count(distinct columnA), count(distinct columnB), ... from table1;

但这只字不提,难以编码。我更喜欢更灵活一些。

Answer 1

我赞赏所有答复。我认为,在这种情况下,对我最为有利的解决办法(在表一栏中,除名称外,对表无所知的外部方案列出不同价值的数目)如下:

表格1

排在栏目中,并 create出每一栏中不同的数值。问询将研究“单独一栏(A)”、计数(别栏B),......从表1看。

Answer 2

该守则应使表1中各栏各有不同价值,作为数据。

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)

Answer 3

试运行(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)

Answer 4

并且很难编码。

提供实地清单以备发言并非不易事。它具有共同和可接受的做法。

Answer 5

这必然会在表格中为每个领域赢得。例如,除非你将数字输入其他数据类型,并失去某种精确性,否则,你就可以在服务器的正文或图像领域进行数字定位。

Answer 6

Raj More s answer works well if you don t need to consider null as a value as count(distinct...) does not count null. Here is a modification to count values including null by converting values to a string and replacing null with "NULL AS SOME IMPOSSIBLE STRING":

DECLARE @TableName VarChar (1024) =  tableName 
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 coalesce(cast([  + COLUMN_NAME +  ] as varchar),
            NULL AS SOME IMPOSSIBLE STRING  )) AS   +      + COLUMN_NAME +     
          FROM INFORMATION_SCHEMA.COLUMNS COL2
          WHERE COL1.TABLE_NAME = COL2.TABLE_NAME
          FOR XML PATH (  )
      ) TableColumns (ColumnList)
    WHERE
      COL1.TABLE_NAME = @TableName
)

EXECUTE (@SqlString)

Answer 7

DISTINCT是邪恶。 Do COUNT/GROUP BY

友情链接