有如下表
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程序来实现!
stat1=case when d1=1 then 1 else 0 end+case when d2=1 then 1 else 0 end+case when d3=1 then 1 else 0 end+case when d100=1 then 1 else 0 end,
stat2=case when d1=2 then 1 else 0 end+case when d2=2 then 1 else 0 end+case when d3=2 then 1 else 0 end+case when d100=2 then 1 else 0 end,
stat3=case when d1=3 then 1 else 0 end+case when d2=3 then 1 else 0 end+case when d3=3 then 1 else 0 end+case when d100=3 then 1 else 0 end
from tb
order by id
update tb set
stat1=case when d1=1 then 1 else 0 end+case when d2=1 then 1 else 0 end+case when d3=1 then 1 else 0 end+case when d100=1 then 1 else 0 end,
stat2=case when d1=2 then 1 else 0 end+case when d2=2 then 1 else 0 end+case when d3=2 then 1 else 0 end+case when d100=2 then 1 else 0 end,
stat3=case when d1=3 then 1 else 0 end+case when d2=3 then 1 else 0 end+case when d3=3 then 1 else 0 end+case when d100=3 then 1 else 0 end
select
stat1=
case d1 when 1 then 1 else 0 end+ case d2 when 1 then 1 else 0 end+
case d3 when 1 then 1 else 0 end+ case d4 when 1 then 1 else 0 end+
case d5 when 1 then 1 else 0 end+ case d6 when 1 then 1 else 0 end+
case d7 when 1 then 1 else 0 end+ case d8 when 1 then 1 else 0 end+
case d9 when 1 then 1 else 0 end+ case d10 when 1 then 1 else 0 end+
case d11 when 1 then 1 else 0 end+ case d12 when 1 then 1 else 0 end+
case d13 when 1 then 1 else 0 end+ case d14 when 1 then 1 else 0 end+
case d15 when 1 then 1 else 0 end+ case d16 when 1 then 1 else 0 end+
case d17 when 1 then 1 else 0 end+ case d18 when 1 then 1 else 0 end+
case d19 when 1 then 1 else 0 end+ case d20 when 1 then 1 else 0 end+
case d21 when 1 then 1 else 0 end+ case d22 when 1 then 1 else 0 end+
case d23 when 1 then 1 else 0 end+ case d24 when 1 then 1 else 0 end+
case d25 when 1 then 1 else 0 end+ case d26 when 1 then 1 else 0 end+
case d27 when 1 then 1 else 0 end+ case d28 when 1 then 1 else 0 end+
case d29 when 1 then 1 else 0 end+ case d30 when 1 then 1 else 0 end+
case d31 when 1 then 1 else 0 end+ case d32 when 1 then 1 else 0 end+
case d33 when 1 then 1 else 0 end+ case d34 when 1 then 1 else 0 end+
case d35 when 1 then 1 else 0 end+ case d36 when 1 then 1 else 0 end+
case d37 when 1 then 1 else 0 end+ case d38 when 1 then 1 else 0 end+
case d39 when 1 then 1 else 0 end+ case d40 when 1 then 1 else 0 end+
case d41 when 1 then 1 else 0 end+ case d42 when 1 then 1 else 0 end+
case d43 when 1 then 1 else 0 end+ case d44 when 1 then 1 else 0 end+
case d45 when 1 then 1 else 0 end+ case d46 when 1 then 1 else 0 end+
case d47 when 1 then 1 else 0 end+ case d48 when 1 then 1 else 0 end+
case d49 when 1 then 1 else 0 end+ case d50 when 1 then 1 else 0 end+
case d51 when 1 then 1 else 0 end+ case d52 when 1 then 1 else 0 end+
case d53 when 1 then 1 else 0 end+ case d54 when 1 then 1 else 0 end+
case d55 when 1 then 1 else 0 end+ case d56 when 1 then 1 else 0 end+
case d57 when 1 then 1 else 0 end+ case d58 when 1 then 1 else 0 end+
case d59 when 1 then 1 else 0 end+ case d60 when 1 then 1 else 0 end+
case d61 when 1 then 1 else 0 end+ case d62 when 1 then 1 else 0 end+
case d63 when 1 then 1 else 0 end+ case d64 when 1 then 1 else 0 end+
case d65 when 1 then 1 else 0 end+ case d66 when 1 then 1 else 0 end+
case d67 when 1 then 1 else 0 end+ case d68 when 1 then 1 else 0 end+
case d69 when 1 then 1 else 0 end+ case d70 when 1 then 1 else 0 end+
case d71 when 1 then 1 else 0 end+ case d72 when 1 then 1 else 0 end+
case d73 when 1 then 1 else 0 end+ case d74 when 1 then 1 else 0 end+
case d75 when 1 then 1 else 0 end+ case d76 when 1 then 1 else 0 end+
case d77 when 1 then 1 else 0 end+ case d78 when 1 then 1 else 0 end+
case d79 when 1 then 1 else 0 end+ case d80 when 1 then 1 else 0 end+
case d81 when 1 then 1 else 0 end+ case d82 when 1 then 1 else 0 end+
case d83 when 1 then 1 else 0 end+ case d84 when 1 then 1 else 0 end+
case d85 when 1 then 1 else 0 end+ case d86 when 1 then 1 else 0 end+
case d87 when 1 then 1 else 0 end+ case d88 when 1 then 1 else 0 end+
case d89 when 1 then 1 else 0 end+ case d90 when 1 then 1 else 0 end+
case d91 when 1 then 1 else 0 end+ case d92 when 1 then 1 else 0 end+
case d93 when 1 then 1 else 0 end+ case d94 when 1 then 1 else 0 end+
case d95 when 1 then 1 else 0 end+ case d96 when 1 then 1 else 0 end+
case d97 when 1 then 1 else 0 end+ case d98 when 1 then 1 else 0 end+
case d99 when 1 then 1 else 0 end+ case d100 when 1 then 1 else 0 end
,
stat2=
case d1 when 2 then 1 else 0 end+ case d2 when 2 then 1 else 0 end+
case d3 when 2 then 1 else 0 end+ case d4 when 2 then 1 else 0 end+
case d5 when 2 then 1 else 0 end+ case d6 when 2 then 1 else 0 end+
case d7 when 2 then 1 else 0 end+ case d8 when 2 then 1 else 0 end+
case d9 when 2 then 1 else 0 end+ case d10 when 2 then 1 else 0 end+
case d11 when 2 then 1 else 0 end+ case d12 when 2 then 1 else 0 end+
......
case d97 when 2 then 1 else 0 end+ case d98 when 2 then 1 else 0 end+
case d99 when 2 then 1 else 0 end+ case d100 when 2 then 1 else 0 end
,
stat3=
case d1 when 3 then 1 else 0 end+ case d2 when 3 then 1 else 0 end+
case d3 when 3 then 1 else 0 end+ case d4 when 3 then 1 else 0 end+
case d5 when 3 then 1 else 0 end+ case d6 when 3 then 1 else 0 end+
case d7 when 3 then 1 else 0 end+ case d8 when 3 then 1 else 0 end+
case d9 when 3 then 1 else 0 end+ case d10 when 3 then 1 else 0 end+
case d11 when 3 then 1 else 0 end+ case d12 when 3 then 1 else 0 end+
......
case d97 when 3 then 1 else 0 end+ case d98 when 3 then 1 else 0 end+
case d99 when 3 then 1 else 0 end+ case d100 when 3 then 1 else 0 end
from Table1
这个字符串其实是通过SQL程序生成的,代码如下declare @S int
Declare @ResultStr1 nvarchar(4000)
Declare @ResultStr2 nvarchar(4000)
Declare @ResultStr3 nvarchar(4000)
Declare @ResultStr nvarchar(4000)set @ResultStr1=''
set @ResultStr2=''
set @ResultStr3=''
set @resultStr=''
--如果你需要计算d1~D7列,那么
set @S=1 --从d1列开始(如果需要从Dn列开始,则设置@S的值为n)
while @S<100 begin --到d7列结束(如果需要在dx列结束,则设置条件@S<x)
set @ResultStr1=@ResultStr1+' case d'+cast(@S as varchar(10))+' when 1 then 1 else 0 end+'+char(13)+char(10)
set @ResultStr2=@ResultStr2+' case d'+cast(@S as varchar(10))+' when 2 then 1 else 0 end+'+char(13)+char(10)
set @ResultStr3=@ResultStr3+' case d'+cast(@S as varchar(10))+' when 3 then 1 else 0 end+'+char(13)+char(10)
set @S=@S+1
end
set @ResultStr1=@ResultStr1+' case d'+cast(@S as varchar(10))+' when 1 then 1 else 0 end'+char(13)+char(10)
set @ResultStr2=@ResultStr2+' case d'+cast(@S as varchar(10))+' when 2 then 1 else 0 end'+char(13)+char(10)
set @ResultStr3=@ResultStr3+' case d'+cast(@S as varchar(10))+' when 3 then 1 else 0 end'+char(13)+char(10)set @ResultStr='Select stat1='+@ResultStr1+', stat2='+@ResultStr2+', Stat3='+@ResultStr3+' from table1'
print @ResultStr1
print @ResultStr2
print @ResultStr3--execute sp_executesql @ResultStr如果你的列只有二三十列,那么可以通过execute sp_executesql @ResultStr直接生成结果,但是不幸的是,列有100列,那么每次生成的字符串超过了SQL的限制,就不能直接生成结果了。
因为每个Stat列需要100*36=3600,而共有3列,共10800字符,而sp_executesql 的参数只能接收不超过4000个的字符,所以不能作为参数传递,只能生成Select语句来查询了
应该将列D1~D100换成行的表达形式,即
这个表结构是这样的:(以你给的数据为例)
ID Idx Value
1 1 1 //id=1,d1=1
1 2 3 //id=1,d2=3
1 3 2 //id=1,d3=2
1 4 3
1 5 2
...
1 100 3 //id=1,d100=3
2 1 3 //id=2,d1=3
...
其中Id为原数据行的ID值,Idx列为原表中列的编号,如Idx=1表示D1列,Value是原表值相应列的值原表如下
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