SELECT (SELECT COUNT(*) FROM TB WHERE ID IS NULL)+ (SELECT COUNT(*) FROM TB WHERE NAMES IS NULL)+ (SELECT COUNT(*) FROM TB WHERE AGE IS NULL)+ (SELECT COUNT(*) FROM TB WHERE ID [SEX] NULL) FROM TB简单的话就这样其它的用动态,多列
select sum (case when(字段1=null ,........ ) then 0 else 1)from 表名 你试试看这个不够活,不对 的地方还请包涵。希望对你有帮助,祝你好运!!!
/*--------------------------------- -- Author : htl258(Tony) -- Date : 2009-09-17 12:25:43 -- Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)---------------------------------*/ --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([a] NVARCHAR(10),[b] NVARCHAR(10),[c] NVARCHAR(10),[d] NVARCHAR(10),[e] NVARCHAR(10),[f] NVARCHAR(10),[g] NVARCHAR(10)) INSERT [tb] SELECT 'b1','a','d','a',NULL,'e',NULL UNION ALL SELECT NULL,NULL,NULL,NULL,'f','a',NULL UNION ALL SELECT 'b3',NULL,NULL,NULL,NULL,NULL,NULL UNION ALL SELECT 'b4','a','b','c','d','e','f' GO --SELECT * FROM [tb]-->SQL查询如下:declare @s varchar(8000)select @s=isnull(@s+'+','select ')+'sum(case when ['+name+'] is null then 1 else 0 end)' from syscolumns where object_id('tb')=idexec(@s+' as NULL值的数量 from tb') /* NULL值的数量 ----------- 13 */
DECLARE @SQL NVARCHAR(4000) SET @SQL='SELECT COUNT(*) FROM TB WHERE ' SELECT @SQL=ISNULL(@SQL,'')+NAME+' IS NULL OR ' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')SET @SQL=RIGHT(@SQL,LEN(@SQL)-2)--PRINT @SQLEXEC(@SQL)这样就可以了,我那样还复杂了
DECLARE @SQL NVARCHAR(4000) SET @SQL='SELECT COUNT(*) FROM TB WHERE ' SELECT @SQL=ISNULL(@SQL,'')+NAME+' IS NULL OR ' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')SET @SQL=LEFT(@SQL,LEN(@SQL)-3)PRINT @SQL--EXEC(@SQL) /*SELECT COUNT(*) FROM TB WHERE CreateDate IS NULL OR id IS NULL OR orderNO IS NULL OR state IS NULL上面弄错了
刚才吃饭去了 IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 INT,COL2 INT,COL3 INT) INSERT INTO TB SELECT 1,NULL,2 UNION ALL SELECT NULL,NULL,2 UNION ALL SELECT 1,NULL,NULL UNION ALL SELECT NULL,NULL,NULL UNION ALL SELECT 1,3,2 --统计表中全部NULL值 DECLARE @TBNAME VARCHAR(8000),@STR VARCHAR(8000) SET @TBNAME='TB' SELECT @STR=ISNULL(@STR+'(SELECT COUNT(*) FROM TB WHERE ['+[NAME]+'] IS NULL)+' ,'SELECT TOP 1 (SELECT COUNT(*) FROM TB WHERE '+[NAME]+' IS NULL)+') FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TBNAME) SELECT @STR=LEFT(@STR,LEN(@STR)-1)+' FROM ['+@TBNAME+']' EXEC (@STR) --8
四个字段 id names age sex
能给个具体语句吗
(SELECT COUNT(*) FROM TB WHERE ID IS NULL)+
(SELECT COUNT(*) FROM TB WHERE NAMES IS NULL)+
(SELECT COUNT(*) FROM TB WHERE AGE IS NULL)+
(SELECT COUNT(*) FROM TB WHERE ID [SEX] NULL)
FROM TB简单的话就这样其它的用动态,多列
sum (case when(字段1=null
,........
) then 0 else 1)from 表名
你试试看这个不够活,不对 的地方还请包涵。希望对你有帮助,祝你好运!!!
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-17 12:25:43
-- Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] NVARCHAR(10),[b] NVARCHAR(10),[c] NVARCHAR(10),[d] NVARCHAR(10),[e] NVARCHAR(10),[f] NVARCHAR(10),[g] NVARCHAR(10))
INSERT [tb]
SELECT 'b1','a','d','a',NULL,'e',NULL UNION ALL
SELECT NULL,NULL,NULL,NULL,'f','a',NULL UNION ALL
SELECT 'b3',NULL,NULL,NULL,NULL,NULL,NULL UNION ALL
SELECT 'b4','a','b','c','d','e','f'
GO
--SELECT * FROM [tb]-->SQL查询如下:declare @s varchar(8000)select @s=isnull(@s+'+','select ')+'sum(case when ['+name+'] is null then 1 else 0 end)'
from syscolumns
where object_id('tb')=idexec(@s+' as NULL值的数量 from tb')
/*
NULL值的数量
-----------
13
*/
DECLARE @SQL NVARCHAR(4000)
SET @SQL='SELECT COUNT(*) FROM TB WHERE '
SELECT @SQL=ISNULL(@SQL,'')+NAME+' IS NULL OR ' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')SET @SQL=RIGHT(@SQL,LEN(@SQL)-2)--PRINT @SQLEXEC(@SQL)这样就可以了,我那样还复杂了
DECLARE @SQL NVARCHAR(4000)
SET @SQL='SELECT COUNT(*) FROM TB WHERE '
SELECT @SQL=ISNULL(@SQL,'')+NAME+' IS NULL OR ' FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB')SET @SQL=LEFT(@SQL,LEN(@SQL)-3)PRINT @SQL--EXEC(@SQL)
/*SELECT COUNT(*) FROM TB WHERE CreateDate IS NULL OR id IS NULL OR orderNO IS NULL OR state IS NULL上面弄错了
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 INT,COL2 INT,COL3 INT)
INSERT INTO TB
SELECT 1,NULL,2 UNION ALL
SELECT NULL,NULL,2 UNION ALL
SELECT 1,NULL,NULL UNION ALL
SELECT NULL,NULL,NULL UNION ALL
SELECT 1,3,2 --统计表中全部NULL值
DECLARE @TBNAME VARCHAR(8000),@STR VARCHAR(8000)
SET @TBNAME='TB'
SELECT @STR=ISNULL(@STR+'(SELECT COUNT(*) FROM TB WHERE ['+[NAME]+'] IS NULL)+'
,'SELECT TOP 1 (SELECT COUNT(*) FROM TB WHERE '+[NAME]+' IS NULL)+')
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID(@TBNAME)
SELECT @STR=LEFT(@STR,LEN(@STR)-1)+' FROM ['+@TBNAME+']'
EXEC (@STR)
--8