34,837
社区成员
发帖
与我相关
我的任务
分享计算表中各列非空的数目,计算表中各行已填列的总数
表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等字眼.
...全文
请发表友善的回复…
发表回复
超维电脑科技 2010-01-23
- 打赏
- 举报
好帖mark
csdyyr 2009-01-15
- 打赏
- 举报
--需求1
--對所有非系統表
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) '')
'
dobear_0922 2009-01-15
- 打赏
- 举报
如果你的表不多，第二个问可以用12楼的代码，表很多的话，nvarchar(4000)可能不够用，SQL2005可以改用nvarchar(max)，SQL2000下只能多用几个变量来拼接。
csdyyr 2009-01-15
- 打赏
- 举报
--处理一个表
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', null
DECLARE @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 TBdobear_0922 2009-01-15
- 打赏
- 举报
/*需求2 计算表中各行已填列的总数
因为表A各列在3行数据,a1填了3次,a2填了2次,a3填了1 ,所以得出总数是6
因为表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
go
exec p2
/*
--------------------
21
(1 行受影响)
*/
drop procedure p2dobear_0922 2009-01-15
- 打赏
- 举报
--需求2 计算表中各行已填列的总数
--
-- 因为表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 @sql
set @sql='select @nRows=count(*) from ('+@sql+') T'
exec sp_executesql @sql, N'@nRows bigint output', @nRows output
select @nRows
/*
--------------------
21
(1 行受影响)
*/
子陌红尘 2009-01-15
- 打赏
- 举报
只处理一个表的存储过程：
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
go
create 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',null
insert into b select 'bb','bb',null
insert into b select 'bb','bb',null
insert into b select 'bb','bb',null
go
exec 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
*/
go
drop table a,b
drop procedure sp_test
go
yikun 2009-01-15
- 打赏
- 举报
[Quote=引用 8 楼 dobear_0922 的回复:]
问题：通过存储过程 sql实现，可以两个存储过程分开实现。实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
晕，又是统计所有表的？
[/Quote]
对就是所有的表
问题：通过存储过程 sql实现，可以两个存储过程分开实现。实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
晕，又是统计所有表的？
[/Quote]
对就是所有的表
dobear_0922 2009-01-15
- 打赏
- 举报
问题：通过存储过程 sql实现，可以两个存储过程分开实现。实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
晕，又是统计所有表的？
所以不要出现A，B等字眼.
晕，又是统计所有表的？
yikun 2009-01-15
- 打赏
- 举报
[Quote=引用 5 楼 csdyyr 的回复:]
引用 3 楼 yikun 的回复:
实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
用动态SQL
[/Quote]
我要会动态 我就不用发贴问了.怎么动态查不同的表,我都不会.要不你简单写个动态查不同表的例子,
表名,有多少个表,多少列 全部都是动态的.
引用 3 楼 yikun 的回复:
实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
用动态SQL
[/Quote]
我要会动态 我就不用发贴问了.怎么动态查不同的表,我都不会.要不你简单写个动态查不同表的例子,
表名,有多少个表,多少列 全部都是动态的.
子陌红尘 2009-01-15
- 打赏
- 举报
declare @a table(a1 varchar(8),a2 varchar(8),a3 varchar(8))
declare @b table(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',null
insert into @b select 'bb','bb',null
insert into @b select 'bb','bb',null
insert into @b select 'bb','bb',null
select
sum(num)
from
(select max(case when a1 is not null then 1 else 0 end)
+ max(case when a2 is not null then 1 else 0 end)
+ max(case when a3 is not null then 1 else 0 end) as num
from @a
union all
select max(case when b1 is not null then 1 else 0 end)
+ max(case when b2 is not null then 1 else 0 end)
+ max(case when b3 is not null then 1 else 0 end) as num
from @b) t
/*
-----------
5
*/
select
sum(num)
from
(select sum(case when a1 is not null then 1 else 0 end)
+ sum(case when a2 is not null then 1 else 0 end)
+ sum(case when a3 is not null then 1 else 0 end) as num
from @a
union all
select sum(case when b1 is not null then 1 else 0 end)
+ sum(case when b2 is not null then 1 else 0 end)
+ sum(case when b3 is not null then 1 else 0 end) as num
from @b) t
/*
-----------
12
*/csdyyr 2009-01-15
- 打赏
- 举报
[Quote=引用 3 楼 yikun 的回复:]
实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
[/Quote]
用动态SQL
实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
[/Quote]
用动态SQL
oraclelogan 2009-01-15
- 打赏
- 举报
[Quote=引用 2 楼 csdyyr 的回复:]
SQL code--需求1
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+
…
[/Quote]
正解！
SQL code--需求1
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+
…
[/Quote]
正解！
yikun 2009-01-15
- 打赏
- 举报
实际情况下表名，列名都不清楚，
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
所以不要出现A，B等字眼.
a1..,b1..都不要出现.
写的有点复杂,明天我加到200分,如果可以的话. 谢谢!
csdyyr 2009-01-15
- 打赏
- 举报
--需求1
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
水族杰纶 2009-01-15
- 打赏
- 举报
看了有點繞~~~
