表A 表B
a1 a2 a3 b1 b2 b3
行1 aa null null bb bb null
行2 aa aa aa bb bb null
行3 aa aa null bb bb null需求1 计算表中各列非空的数目
因为表A各行中a1,a2,a3都曾填了数据,不为空,所以得出总数是3
因为表B各行中b1,b2都增填了数据不为空,而b3为空,所以得出总数是2
表A与表B的总数为5
需求2 计算表中各行已填列的总数 因为表A各列在3行数据,a1填了3次,a2填了2次,a3填了1 ,所以得出总数是6
因为表B各列在3行数据,a1填了2次,a2填了2次,a3填了2 ,所以得出总数是6
表A与表B的总数为5 问题:通过存储过程 sql实现,可以两个存储过程分开实现。实际情况下表名,列名都不清楚,
所以不要出现A,B等字眼.
a1 a2 a3 b1 b2 b3
行1 aa null null bb bb null
行2 aa aa aa bb bb null
行3 aa aa null bb bb null需求1 计算表中各列非空的数目
因为表A各行中a1,a2,a3都曾填了数据,不为空,所以得出总数是3
因为表B各行中b1,b2都增填了数据不为空,而b3为空,所以得出总数是2
表A与表B的总数为5
需求2 计算表中各行已填列的总数 因为表A各列在3行数据,a1填了3次,a2填了2次,a3填了1 ,所以得出总数是6
因为表B各列在3行数据,a1填了2次,a2填了2次,a3填了2 ,所以得出总数是6
表A与表B的总数为5 问题:通过存储过程 sql实现,可以两个存储过程分开实现。实际情况下表名,列名都不清楚,
所以不要出现A,B等字眼.
DECLARE @TB TABLE(a1 VARCHAR(2), a2 VARCHAR(2), a3 VARCHAR(2), b1 VARCHAR(2), b2 VARCHAR(2), b3 VARCHAR(2))
INSERT @TB
SELECT 'aa', null, null, 'bb', 'bb', null UNION ALL
SELECT 'aa', 'aa', 'aa', 'bb', 'bb', null UNION ALL
SELECT 'aa', 'aa', null, 'bb', 'bb', null
SELECT CASE WHEN C1>0 THEN 1 ELSE 0 END+
CASE WHEN C2>0 THEN 1 ELSE 0 END+
CASE WHEN C3>0 THEN 1 ELSE 0 END+
CASE WHEN C4>0 THEN 1 ELSE 0 END+
CASE WHEN C5>0 THEN 1 ELSE 0 END+
CASE WHEN C6>0 THEN 1 ELSE 0 END
FROM (
SELECT
SUM(CASE WHEN A1 IS NOT NULL THEN 1 ELSE 0 END) AS C1,
SUM(CASE WHEN A2 IS NOT NULL THEN 1 ELSE 0 END) AS C2,
SUM(CASE WHEN A3 IS NOT NULL THEN 1 ELSE 0 END) AS C3,
SUM(CASE WHEN B1 IS NOT NULL THEN 1 ELSE 0 END) AS C4,
SUM(CASE WHEN B2 IS NOT NULL THEN 1 ELSE 0 END) AS C5,
SUM(CASE WHEN B3 IS NOT NULL THEN 1 ELSE 0 END) AS C6
FROM @TB
) T
所以不要出现A,B等字眼.
a1..,b1..都不要出现.写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
所以不要出现A,B等字眼. 晕,又是统计所有表的?
create procedure sp_test(@tname varchar(40),@type int)
as
begin
declare @sql varchar(8000)
set @sql=''
if (@type=0)
select @sql=@sql+'+max(case when '+name+' is not null then 1 else 0 end)' from syscolumns where id=object_id(@tname)
else
select @sql=@sql+'+sum(case when '+name+' is not null then 1 else 0 end)' from syscolumns where id=object_id(@tname)
set @sql='select '+stuff(@sql,1,1,'')+' as num from '+@tname
exec(@sql)
end
gocreate table a(a1 varchar(8),a2 varchar(8),a3 varchar(8))
create table b(b1 varchar(8),b2 varchar(8),b3 varchar(8))insert into a select 'aa',null,null
insert into a select 'aa','aa','aa'
insert into a select 'aa','aa',nullinsert into b select 'bb','bb',null
insert into b select 'bb','bb',null
insert into b select 'bb','bb',null
goexec sp_test 'a',0
/*
num
-----------
3
*/exec sp_test 'a',1
/*
num
-----------
6
*/
exec sp_test 'b',0
/*
num
-----------
2
*/
exec sp_test 'b',1
/*
num
-----------
6
*/godrop table a,b
drop procedure sp_test
go
--
-- 因为表A各列在3行数据,a1填了3次,a2填了2次,a3填了1 ,所以得出总数是6
-- 因为表B各列在3行数据,a1填了2次,a2填了2次,a3填了2 ,所以得出总数是6
-- 表A与表B的总数为5 declare @sql nvarchar(4000), @nRows bigint
select @sql=isnull(@sql+' union all ', '')+'select N=count(*) from '+quotename(name) from sysobjects where type='U'
--print @sqlset @sql='select @nRows=count(*) from ('+@sql+') T'
exec sp_executesql @sql, N'@nRows bigint output', @nRows output
select @nRows/*
--------------------
21(1 行受影响)
*/
因为表B各列在3行数据,a1填了2次,a2填了2次,a3填了2 ,所以得出总数是6
表A与表B的总数为5
*/create procedure p2
as
begin
declare @sql nvarchar(4000), @nRows bigint
select @sql=isnull(@sql+' union all ', '')+'select N=count(*) from '+quotename(name)
from sysobjects where type='U'
--print @sql set @sql='select @nRows=count(*) from ('+@sql+') T'
exec sp_executesql @sql, N'@nRows bigint output', @nRows output
select @nRows
end
goexec p2
/*
--------------------
21(1 行受影响)
*/drop procedure p2
CREATE TABLE TB(a1 VARCHAR(2), a2 VARCHAR(2), a3 VARCHAR(2), b1 VARCHAR(2), b2 VARCHAR(2), b3 VARCHAR(2))
INSERT TB
SELECT 'aa', null, null, 'bb', 'bb', null UNION ALL
SELECT 'aa', 'aa', 'aa', 'bb', 'bb', null UNION ALL
SELECT 'aa', 'aa', null, 'bb', 'bb', nullDECLARE @TB VARCHAR(255)
SET @TB='TB'EXEC('
DECLARE @SQL VARCHAR(8000)
SET @SQL=''''SELECT @SQL=@SQL+''+''+'' MAX(CASE WHEN ''+NAME+'' IS NOT NULL THEN 1 ELSE 0 END)''
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('''+@TB+''')SET @SQL=''SELECT ''+STUFF(@SQL,1,1,'''')+'' FROM TB''
EXEC(@SQL) ')DROP TABLE TB
--對所有非系統表
EXEC SP_MSFOREACHTABLE N'
EXEC(''
DECLARE @SQL VARCHAR(8000)
SET @SQL=''''''''SELECT @SQL=@SQL+''''+''''+'''' ISNULL(MAX(CASE WHEN ''''+NAME+'''' IS NOT NULL THEN 1 ELSE 0 END),0) ''''
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID(''''?'''')SET @SQL=''''SELECT ''''+STUFF(@SQL,1,1,'''''''')+'''' AS ''''+STUFF(''''?'''',1,CHARINDEX(''''.'''',''''?''''),'''''''')+'''' FROM ?''''
EXEC(@SQL) '')
'