日期:2014-05-17 浏览次数:20523 次
--表名qktj:欠款统计
--字段 dwdm:单位代码,qk:欠款金额,qkts:欠款天数,czydm:操作员代码,djsj:登记时间
create table qktj(dwdm varchar(10),qk numeric(19,2),qkts int,czydm varchar(5),djsj datetime)
insert into qktj(dwdm,qk,qkts,czydm,djsj)
select '33-A',2341.35,123,'1523','2009-01-11 13:34:23' union all--时间格式我忘记具体怎么写了,这里写到秒
select '33-A',341.35,13,'5123','2009-11-12 13:34:23' union all
select '33-B',-241.59,23,'1234','2012-04-23 13:34:23' union all
select '33-A',9341.75,13,'1234','2012-05-30 13:34:23' union all
select '33-A',241.66,13,'1523','2011-05-23 13:34:23' union all
select '33-B',-23.68,143,'1234','2010-08-11 13:34:23' union all
select '33-B',21.34,223,'1523','2010-09-21 13:34:23' union all
select '33-C',2341.45,223,'1234','2009-11-11 13:34:23' union all
select '33-C',241.69,153,'1523','2012-07-11 13:34:23' union all
select '33-A',-341.88,44,'1523','2012-12-11 13:34:23' union all
select '33-C',21.80,34,'1234','2011-05-11 13:34:23' union all
select '33-B',2341.35,123,'1234','2012-01-11 13:34:23' union all
select '33-B',2341.35,123,'1523','2009-01-11 13:34:23' union all
select '33-A',2341.35,123,'1234','2012-01-11 13:34:23' union all
select '33-A',2341.35,123,'1523','2009-01-11 13:34:23' union all
select '33-C',2341.35,123,'1523','2011-01-11 13:34:23' union all
select '33-A',2341.35,123,'1234','2010-01-11 13:34:23' union all
select '33-A',2341.35,123,'1523','2009-01-11 13:34:23'
--这些数据是我现在随便举例写的
select top(200) * from qktj with(nolock)
select top(2000) * from qktj with(nolock)
--速度很慢,请赐教
select dwdm,czydm,
sum(case when qkts/30 = 0 then qk else 0 end)[0-30天],
sum(case when qkts/30 = 1 then qk else 0 end)[30-60天],
sum(case when qkts/30 = 2 then qk else 0 end)[60-120天],
sum(case when qkts/30 > 2 then qk else 0 end)[120天以上],
sum( qk )[合计]
from qktj group by dwdm,czydm
/*
dwdm czydm 0-30天 30-60天 60-120天 120天以上 合计
---------- ----- ---------- ---------- ---------- ---------- ----------
33-A&nb