如何将表中所有记录的值都为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
并且会自动删去重复行,如果只有一行为NULL的数据,就说明所有记录的这个属性的值都为NULL
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
字段多就用动态SQL .
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)
*/
不过我实在是菜,"动态SQL基于字段Group By"这句话是啥意思呢....
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
*/
(
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 行受影响)
------- 准备测试数据
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 行)
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
*/