日期:2014-05-18  浏览次数:20664 次

求报表分析- -
SQL code

有TAB如下:
KJND    GSDM    YSDWDM    JE    SH_RQ       DM
2011    888     001       1000  20110101    0601
2011    888     001       1000  20110201    0601
2011    888     001       1000  20110201    0501
2011    888     001       1000  20110101    0401
2011    888     002       1000  20110101    0601
2011    888     002       1000  20110201    0401
2011    888     003       1000  20110101    0601
2011    888     003       1000  20110201    0501
2011    888     003       1000  20110201    0401
................................................
需求:
取其中001、002、003这3个单位,如查询SH_RQ为2011年2月的数据
KJND GSDM YSDWDM M1(DM为:0401/0501/0601) L1(DM为:0401/0501/0601) M2(DM为:0601) L2(DM为:0601) M3(DM为:0401/0501) L3(DM为:0401/0501)
2011  888    001    2000    4000    1000  2000   1000  2000
2011  888    002    1000    2000    0     1000   1000  1000
2011  888    003    2000    3000    0     1000   2000  2000



------解决方案--------------------
行转列.................
------解决方案--------------------
自己写sum(case when...)
------解决方案--------------------
SQL code
select
  kjnd,
  gsdm,
  ysdmdm,
  sum(case when dm in('0401','0501','0601') then je else 0 end) as m1,
  sum(case when dm='0601') then je else 0 end) as m1,
...
from
   tb
gorup by
     kjnd,
  gsdm,
  ysdmdm

------解决方案--------------------
參照例子
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
------解决方案--------------------
把你后面的l1,d1之类的数据解释一下怎么来的?
------解决方案--------------------
SQL code
累计合计这样计算

select *,(select sum(m1) from tb where SH_RQ<=t.SH_RQ) from tb t

------解决方案--------------------
不知道楼主要统计什么,供参考吧:
SQL code
select KJND, GSDM, YSDWDM,
M1=sum(case when dm in('0601','0501','0601') then JE else 0 end),
L1=sum(case when dm in('0601','0501','0601') then .... else 0 end),
M2=sum(...
from tab
group by KJND, GSDM, YSDWDM

------解决方案--------------------
SQL code
create table tb(KJND int, GSDM int, YSDWDM varchar(10), JE int, SH_RQ datetime, DM varchar(10))
insert into tb values(2011 , 888 , '001' , 1000 ,'20110101' , '0601')
insert into tb values(2011 , 888 , '001' , 1000 ,'20110201' , '0601')
insert into tb values(2011 , 888 , '001' , 1000 ,'20110201' , '0501')
insert into tb values(2011 , 888 , '001' , 1000 ,'20110101' , '0401')
insert into tb values(2011 , 888 , '002' , 1000 ,'20110101' , '0601')
insert into tb values(2011 , 888 , '002' , 1000 ,'20110201' , '0401')
insert into tb values(2011 , 888 , '003' , 1000 ,'20110101' , '0601')
insert into tb values(2011 , 888 , '003' , 1000 ,'20110201' , '0501')
insert into tb values(2011 , 888 , '003' , 1000 ,'20110201' , '0401')
go


select t.KJND , t.GSDM , t.YSDWDM,
       M1 = (select sum(je) from tb where KJND = t.KJND and YSDWDM = t.YSDWDM and datepart(mm,sh_rq) = 2),
       L1 = (select sum(je) from tb where KJND = t.KJND and YSDWDM = t.YSDWDM)
from tb t where  YSDWDM in ('001','002','003') and datepart(mm,sh_rq) = 2
group by t.KJND , t.GSDM , t.YSDWDM

drop table tb

/*
KJND        GSDM        YSDWDM     M1          L1          
----------- ----------- ---------- ----------- ----------- 
2011        888         001        2000        4000
2011        888         002        1000        2000
2011        888         003        2000        3000

(所影响的行数为 3 行)

*/