有如下表
ID STAT1 STAT2 STAT3 D1 D2 D3 D4 D5...D100
1 NULL NULL NULL 1 3 2 3 2 3
2 NULL NULL NULL 3 1 2 1 2 2
3 NULL NULL NULL 2 3 2 3 2 3
...
1000 NULL NULL NULL 2 3 2 3 2 1
表有1000条记录,字段D1到D100的值为:1或2或3。现在要统计每一行1,2,3的个数,并分别写到对应行的STAT1(写入1的个数), STAT2(写入2的个数),STAT3(写入3的个数)。请问在delphi中如何写这个sql程序来实现!
ID STAT1 STAT2 STAT3 D1 D2 D3 D4 D5...D100
1 NULL NULL NULL 1 3 2 3 2 3
2 NULL NULL NULL 3 1 2 1 2 2
3 NULL NULL NULL 2 3 2 3 2 3
...
1000 NULL NULL NULL 2 3 2 3 2 1
表有1000条记录,字段D1到D100的值为:1或2或3。现在要统计每一行1,2,3的个数,并分别写到对应行的STAT1(写入1的个数), STAT2(写入2的个数),STAT3(写入3的个数)。请问在delphi中如何写这个sql程序来实现!
-- Author: T.O.P
-- Create date: 20091201
-- Version: SQL SERVER 2000
-- =============================================
if object_id('[tb]') is not null drop table [tb1]
go
create table [tb]([ID] int,[STAT1] int,[STAT2] int,[STAT3] int,[D1] int,[D2] int,[D3] int,[D4] int,[D5] int,[d6] int)
insert [tb]
select 1,null,null,null,1,3,2,3,2,3 union all
select 2,null,null,null,3,1,2,1,2,2 union all
select 3,null,null,null,2,3,2,3,2,3
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select ID, [STAT3] = ' + quotename(Name) + ' from tb'
from syscolumns
where name<>'ID' and name<>'STAT1' and name<>'STAT2' and name<>'STAT3' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
print (@sql + ' order by ID')set @sql='select id, stat3 , count(*) as icount from ('+@sql+') t group by id, stat3'
exec(@sql)
drop table tb--测试结果:
/*
id stat3 icount
----------- ----------- -----------
1 1 1
2 1 2
1 2 2
2 2 3
3 2 3
1 3 3
2 3 1
3 3 3*/
-- =============================================
-- Author: T.O.P
-- Create date: 20091201
-- Version: SQL SERVER 2000
-- =============================================
if object_id('[tb]') is not null drop table [tb1]
go
create table [tb]([ID] int,[STAT1] int,[STAT2] int,[STAT3] int,[D1] int,[D2] int,[D3] int,[D4] int,[D5] int,[d6] int)
insert [tb]
select 1,null,null,null,1,3,2,3,2,3 union all
select 2,null,null,null,3,1,2,1,2,2 union all
select 3,null,null,null,2,3,2,3,2,3
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select ID, [STAT3] = ' + quotename(Name) + ' from tb'
from syscolumns
where name<>'ID' and name<>'STAT1' and name<>'STAT2' and name<>'STAT3' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
print (@sql + ' order by ID')set @sql='select id, sum(case when stat3=1 then icount else 0 end) as stat1,'
+'sum(case when stat3=2 then icount else 0 end) as stat2,'
+'sum(case when stat3=3 then icount else 0 end) as stat3'
+' from ( select id, stat3 , count(*) as icount from ('+@sql+') t group by id, stat3 ) k group by id'
exec(@sql)
drop table tb--测试结果:
/*
id stat1 stat2 stat3
----------- ----------- ----------- -----------
1 1 2 3
2 2 3 1
3 0 3 3*/
这样出来了, 应该够了吧