34,590
社区成员
发帖
与我相关
我的任务
分享如何将表中所有记录的值都为NULL的那些字段列出来?
如何将表中所有记录的值都为NULL的那些字段列出来?
例如有下面的表
ID Field1 Field2 Field3 Field4 Field5
1 a NULL NULL NULL e
2 NULL NULL c NULL e
3 a NULL c NULL e
4 a NULL NULL NULL NULL
5 a NULL c NULL e
6 NULL NULL c NULL e
那么获得的字段列表应该为:
Field2 Field4
例如有下面的表
ID Field1 Field2 Field3 Field4 Field5
1 a NULL NULL NULL e
2 NULL NULL c NULL e
3 a NULL c NULL e
4 a NULL NULL NULL NULL
5 a NULL c NULL e
6 NULL NULL c NULL e
那么获得的字段列表应该为:
Field2 Field4
...全文
请发表友善的回复…
发表回复
dfpgb 2009-07-08
- 打赏
- 举报
谢谢大家!
zzz1975 2009-07-07
- 打赏
- 举报
[Quote=引用 15 楼 dfpgb 的回复:]
嘿~我倒是觉得10楼和14楼的办法最有意思呵
比较适合我这种菜鸟看明白 哈
[/Quote]
支持你！！！
嘿~我倒是觉得10楼和14楼的办法最有意思呵
比较适合我这种菜鸟看明白 哈
[/Quote]
支持你！！！
dfpgb 2009-07-07
- 打赏
- 举报
嘿~我倒是觉得10楼和14楼的办法最有意思呵
比较适合我这种菜鸟看明白 哈
比较适合我这种菜鸟看明白 哈
叶子 2009-07-07
- 打赏
- 举报
declare @table table (ID int,Field1 varchar(1),Field2 sql_variant,Field3 varchar(1),Field4 sql_variant,Field5 varchar(1))
insert into @table
select 1,'a',null,null,null,'e' union all
select 2,null,null,'c',null,'e' union all
select 3,'a',null,'c',null,'e' union all
select 4,'a',null,null,null,null union all
select 5,'a',null,'c',null,'e' union all
select 6,null,null,'c',null,'e'
select colname from (
select 'ID' as colname,count(ID) as 'count' from @table
union all
select 'Field1',count(Field1) from @table
union all
select 'Field2',count(Field2) from @table
union all
select 'Field3',count(Field3) from @table
union all
select 'Field4',count(Field4) from @table
union all
select 'Field5',count(Field5) from @table )bb
where [count]=0
/*
colname
-------
Field2
Field4
*/
dolphin12345 2009-07-07
- 打赏
- 举报
用GROUP BY分组
无锡阔微信息科技 2009-07-06
- 打赏
- 举报
关注 动态SQL基于字段Group By
Tomzzu 2009-07-06
- 打赏
- 举报
------- 准备测试数据
declare @tRet table
(
ID int ,
Field1 varchar(20) ,
Field2 varchar(20) ,
Field3 varchar(20) ,
Field4 varchar(20) ,
Field5 varchar(20)
)
insert into @tRet
select 1, 'a', NULL, NULL, NULL, 'e'
union all
select 2, NULL, NULL, 'c', NULL, 'e'
union all
select 3, 'a', NULL, 'c', NULL, 'e'
union all
select 4, 'a', NULL, NULL, NULL, NULL
union all
select 5, 'a', NULL, 'c', NULL, 'e'
union all
select 6, NULL, NULL, 'c', NULL, 'e'
-- 实际语句
select (case when max(Field1) = min(Field1) and min(Field1) = '' then 'Field1全为空' else '' end) as Field1,
(case when max(Field2) = min(Field2) and min(Field2) = '' then 'Field2全为空' else '' end) as Field2,
(case when max(Field3) = min(Field3) and min(Field3) = '' then 'Field3全为空' else '' end) as Field3,
(case when max(Field4) = min(Field4) and min(Field4) = '' then 'Field4全为空' else '' end) as Field4,
(case when max(Field5) = min(Field5) and min(Field5) = '' then 'Field5全为空' else '' end) as Field5
from
(select ID, isnull(Field1, '') as Field1, isnull(Field2, '') as Field2,
isnull(Field3, '') as Field3, isnull(Field4, '') as Field4,
isnull(Field5, '') as Field5
from @tRet
) as v
-- 结果
Field1 Field2 Field3 Field4 Field5
------------ ------------ ------------ ------------ ------------
Field2全为空 Field4全为空
（所影响的行数为 1 行）
cqhweb 2009-07-06
- 打赏
- 举报
好帖,收藏
zxkid 2009-07-06
- 打赏
- 举报
DECLARE @Table TABLE
(
ID int, Field1 nvarchar(10),Field2 nvarchar(10),Field3 nvarchar(10),Field4 nvarchar(10),Field5 nvarchar(10)
)
insert into @Table
select 1,'a',null,null,null,'e'
union all
select 2,null,null,'c',null,'e'
union all
select 3,'a',null,'c',null,'e'
union all
select 4,'a',null,null,null,null
union all
select 5,'a',null,'c',null,'e'
union all
select 6,null,null,'c',null,'e';
SELECT COUNT(DISTINCT Field1) Field1
, COUNT(DISTINCT Field2) Field2
, COUNT(DISTINCT Field3) Field3
, COUNT(DISTINCT Field4) Field4
, COUNT(DISTINCT Field5) Field5
FROM @Table(6 行受影响)
Field1 Field2 Field3 Field4 Field5
----------- ----------- ----------- ----------- -----------
1 0 1 0 1
警告: 聚合或其他 SET 操作消除了空值。
(1 行受影响)
张志峰 2009-07-06
- 打赏
- 举报
和尚的方法可以得到结果，
Zoezs 2009-07-06
- 打赏
- 举报
和尚厉害，我想到的部分就是和7楼一样的笨办法。
csdyyr 2009-07-06
- 打赏
- 举报
CREATE TABLE TB([ID] INT, [Field1] VARCHAR(10), [Field2] VARCHAR(10), [Field3] VARCHAR(10), [Field4] VARCHAR(10), [Field5] VARCHAR(10))
INSERT TB
SELECT 1, 'a', NULL, NULL, NULL, 'e' UNION ALL
SELECT 2, NULL, NULL, 'c', NULL, 'e' UNION ALL
SELECT 3, 'a', NULL, 'c', NULL, 'e' UNION ALL
SELECT 4, 'a', NULL, NULL, NULL, NULL UNION ALL
SELECT 5, 'a', NULL, 'c', NULL, 'e' UNION ALL
SELECT 6, NULL, NULL, 'c', NULL, 'e'
DECLARE @STR NVARCHAR(MAX)
SET @STR=N''
SELECT @STR=@STR+N'+CASE WHEN (SELECT COUNT(*) FROM TB WHERE '+NAME+' IS NOT NULL)=0 THEN '' ''+N'''+NAME+''' ELSE '''' END'
FROM syscolumns
WHERE ID=OBJECT_ID('TB')
SET @STR='SELECT '+STUFF(@STR,1,1,'')
EXEC(@STR)
DROP TABLE TB
/*
--------------------------------------
Field2 Field4
*/
dfpgb 2009-07-06
- 打赏
- 举报
多谢各位!我看看先
不过我实在是菜,"动态SQL基于字段Group By"这句话是啥意思呢....
不过我实在是菜,"动态SQL基于字段Group By"这句话是啥意思呢....
hery2002 2009-07-06
- 打赏
- 举报
drop table #
create table # (ID int, Field1 nvarchar(10),Field2 nvarchar(10),Field3 nvarchar(10),Field4 nvarchar(10),Field5 nvarchar(10))
insert into #
select 1,'a',null,null,null,'e'
union all
select 2,null,null,'c',null,'e'
union all
select 3,'a',null,'c',null,'e'
union all
select 4,'a',null,null,null,null
union all
select 5,'a',null,'c',null,'e'
union all
select 6,null,null,'c',null,'e'
select * from #
declare @a nvarchar(20),@b nvarchar(20),@c nvarchar(20),@d nvarchar(20),@e nvarchar(20),@fieldLists nvarchar(100)
select @a ='',@b='',@c ='',@d ='',@e='',@fieldLists =''
select @a = @a +isnull(Field1,''),
@b = @b +isnull(Field2,''),
@c = @c +isnull(Field3,''),
@d = @d +isnull(Field4,''),
@e = @e +isnull(Field5,'') from #
select @fieldLists = @fieldLists+ (case when @a ='' then 'Field1,' else ''end)
+ (case when @b ='' then 'Field2,' else ''end)
+ (case when @c ='' then 'Field3,' else ''end)
+ (case when @d ='' then 'Field4,' else ''end)
+ (case when @e ='' then 'Field5,' else ''end)
select left (@fieldLists,len(@fieldLists) -1) as FL
/*
(6 row(s) affected)
ID Field1 Field2 Field3 Field4 Field5
----------- ---------- ---------- ---------- ---------- ----------
1 a NULL NULL NULL e
2 NULL NULL c NULL e
3 a NULL c NULL e
4 a NULL NULL NULL NULL
5 a NULL c NULL e
6 NULL NULL c NULL e
(6 row(s) affected)
FL
----------------------------------------------------------------------------------------------------
Field2,Field4
(1 row(s) affected)
*/ 
JonasFeng 2009-07-06
- 打赏
- 举报
如果字段比较少的话，可以采用楼上的做法。
字段多就用动态SQL .
字段多就用动态SQL .
JonasFeng 2009-07-06
- 打赏
- 举报
DECLARE @STRFIELD VARCHAR(50)
SELECT @STRFIELD = ''
SELECT @STRFIELD = @STRFIELD + CASE WHEN MARK1 > 0 THEN 'Field1' ELSE '' END
+ CASE WHEN MARK2 > 0 THEN 'Field2' ELSE '' END
+ CASE WHEN MARK3 > 0 THEN 'Field3' ELSE '' END
+ CASE WHEN MARK4 > 0 THEN 'Field4' ELSE '' END
+ CASE WHEN MARK5 > 0 THEN 'Field5' ELSE '' END
FROM
(
SELECT
max(case when isnull(Field1,'0') <> '0' then 1 else 0 end) as Mark1,
max(case when isnull(Field2,'0') <> '0' then 1 else 0 end) as Mark2,
max(case when isnull(Field3,'0') <> '0' then 1 else 0 end) as Mark3,
max(case when isnull(Field4,'0') <> '0' then 1 else 0 end) as Mark4,
max(case when isnull(Field5,'0') <> '0' then 1 else 0 end) as Mark5
FROM LI) LO
PRINT @STRFIELD 
catici 2009-07-06
- 打赏
- 举报
一楼说的很对，就是用Group By 进行分组
并且会自动删去重复行，如果只有一行为NULL的数据，就说明所有记录的这个属性的值都为NULL
并且会自动删去重复行，如果只有一行为NULL的数据，就说明所有记录的这个属性的值都为NULL
hery2002 2009-07-06
- 打赏
- 举报
使用动态SQL基于字段Group By,如果只有一行为NULL的数据,则为字段中所有都为NULL.
