如何在SQL中获取表中仅占用的值的计数 [英] How to get the count of only occupied values in a table in SQL

问题描述

大家好，



我有一张包含一些列和值的表格。我需要在一个查询中排除空值和空字段的唯一占用值的计数。任何帮助都将非常感谢。提前谢谢。



什么我试过了：

 id名称mob地址性别Dob 
 
 1 Sara 9884436546 NULL F NULL 
 2 Math abc city M 
 3 Asyu NULL NULL 1994-05-29 
 
这里的o / p应该像这样
 id 1：Count = 3 
 id 2：count = 3 
 id 3：count = 2 

解决方案

假设它只是这个表您感兴趣的是这个简单的技术可以工作

;   x   
（
  SELECT  id，hasvalue =  CASE  当 ISNULL（[名称]，' '）<> ' ' 那么  1   ELSE   0   END   FROM  #test 
  UNION   ALL  
  SELECT  id，hasvalue =  CASE   WHEN  ISNULL（mob，' '）<> ' ' 那么  1   ELSE   0   END   FROM  #test 
  UNION   ALL  
  SELECT  id，hasvalue =  CASE   WHEN  ISNULL（[地址]，' '）<> ' ' 那么  1   ELSE   0   END   FROM  #test 
  UNION   ALL  
  SELECT  id，hasvalue =  CASE   WHEN  ISNULL（性别，' '）<> ' ' 那么  1   ELSE   0   END   FROM  #test 
  UNION   ALL  
  SELECT  id，hasvalue =  CASE   WHEN  ISNULL（Dob，' '）<> ' ' 那么  1   ELSE   0   END   FROM  #test 
）
 选择 id，SUM（ hasvalue）来自 x  group   by  id 

评论：

- 我将你的表复制到 #test 中，将其替换为你的表格名称

- 您需要使用 UNION 所有 - 它不适用于 UNION （因为重复项将被删除）

- 您的数据有一个空白（非空）日期 - 我相信您没有将该日期存储在varchar列中！

Hi all,

I have a table with some columns and values. I need the count of only occupied values excluding null values and empty field in one single query.Any help will be really appreciated .Thanks in advance.

What I have tried:

id     Name      mob            address      gender      Dob

1      Sara     9884436546       NULL          F         NULL
2      Math                      abc city      M         
3      Asyu      NULL                          NULL      1994-05-29

Here the o/p should be like this 
id 1 : Count =3
id 2:  count =3
id 3:  count =2

解决方案

Assuming that it is only this table you are interested in then this simple technique would work

;with x as 
(
	SELECT id, hasvalue = CASE WHEN ISNULL([Name],'') <> '' THEN 1 ELSE 0 END FROM #test
	UNION ALL
	SELECT id, hasvalue = CASE WHEN ISNULL(mob,'') <> '' THEN 1 ELSE 0 END FROM #test
	UNION ALL
	SELECT id, hasvalue = CASE WHEN ISNULL([address],'') <> '' THEN 1 ELSE 0 END FROM #test
	UNION ALL
	SELECT id, hasvalue = CASE WHEN ISNULL(gender,'') <> '' THEN 1 ELSE 0 END FROM #test
	UNION ALL
	SELECT id, hasvalue = CASE WHEN ISNULL(Dob,'') <> '' THEN 1 ELSE 0 END FROM #test
)
select id, SUM(hasvalue) from x group by id

Comments:
- I duplicated your table into #test, replace that with your table name
- You need to use UNION ALL - it won't work with just UNION (as duplicates will be removed)
- Your data had a blank (not NULL) date - I trust you have not stored that date in a varchar column!

这篇关于如何在SQL中获取表中仅占用的值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！