英文:
Does there exist a 'single' SQL query for getting unique counts by column?
问题
Col1列唯一值的数量 = 2
Col2列唯一值的数量 = 3（或4，不确定如何处理null）
Col3列唯一值的数量 = 3（或4，不确定如何处理empty）
英文:
TABLE
Col1 Col2 Col3
a 1 Z
a 2 Z
a null empty
b 1 Z
b 2 X
b 3 Y
b 3 Y
I want to know the count of distinct values by column.
- Col1 count = 2
- Col2 count = 3 (or 4 uncertain null handling)
- Col3 count = 3 (or 4 uncertain empty handling)
I have been attempting different counting and grouping clauses, but this may be not the way.
答案1
得分: 3
count(distinct ...)应该可以解决问题
示例
Declare @YourTable Table ([Col1] varchar(50),[Col2] int,[Col3] varchar(50)) Insert Into @YourTable Values
('a',1,'Z')
,('a',2,'Z')
,('a',null,'') -- 注意空字符串
,('b',1,'Z')
,('b',2,'X')
,('b',3,'Y')
,('b',3,'Y')
Select Col1 = count(distinct col1)
,Col2 = count(distinct col2)
,Col3 = count(distinct nullif(col3,'')) -- 如果要计算空字符串，请删除 NullIf()
From @YourTable
结果
Col1 Col2 Col3
2 3 3
英文:
count(distinct ...) should do the trick
Example
Declare @YourTable Table ([Col1] varchar(50),[Col2] int,[Col3] varchar(50)) Insert Into @YourTable Values
('a',1,'Z')
,('a',2,'Z')
,('a',null,'') -- Note the empty string
,('b',1,'Z')
,('b',2,'X')
,('b',3,'Y')
,('b',3,'Y')
Select Col1 = count(distinct col1)
,Col2 = count(distinct col2)
,Col3 = count(distinct nullif(col3,'')) -- Remove NullIf() if you want to count empty strings
From @YourTable
Results
Col1 Col2 Col3
2 3 3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论