SQL问题？

yuzhifu1 2009-10-05 03:35:56

[code=select a.fyear,b.fname,sum(a.fendbal) as fendbal,
'1月'=case when fperiod=1 then sum(a.fendbal) else 0 end ,
'2月'=case when fperiod=2 then sum(a.fendbal) else 0 end ,
'3月'=case when fperiod=3 then sum(a.fendbal) else 0 end ,
'4月'=case when fperiod=4 then sum(a.fendbal) else 0 end ,
'5月'=case when fperiod=5 then sum(a.fendbal) else 0 end ,
'6月'=case when fperiod=6 then sum(a.fendbal) else 0 end ,
'7月'=case when fperiod=7 then sum(a.fendbal) else 0 end ,
'8月'=case when fperiod=8 then sum(a.fendbal) else 0 end ,
'9月'=case when fperiod=9 then sum(a.fendbal) else 0 end ,
'10月'=case when fperiod=10 then sum(a.fendbal) else 0 end ,
'11月'=case when fperiod=11 then sum(a.fendbal) else 0 end ,
'12月'=case when fperiod=12 then sum(a.fendbal) else 0 end
from openrowset('sqloledb','192.168.12.101';'sa';'',ais20080707094757.dbo.icinvbal) as a
inner join openrowset('sqloledb','192.168.12.101';'sa';'',ais20080707094757.dbo.t_stock) as b
on a.fstockid=b.fitemid
group by a.fyear,a.fperiod,b.fname
with rollup
][/2008 钢材库 3295826.03 .00 .00 .00 3295826.03 .00 .00 .00 .00 .00 .00 .00 .00
2008 NULL 3295826.03 .00 .00 .00 3295826.03 .00 .00 .00 .00 .00 .00 .00 .00
2008 半成品库 179351.75 .00 .00 .00 .00 179351.75 .00 .00 .00 .00 .00 .00 .00
2008 材料库 453749.27 .00 .00 .00 .00 453749.27 .00 .00 .00 .00 .00 .00 .00
2008 成品库 181652.73 .00 .00 .00 .00 181652.73 .00 .00 .00 .00 .00 .00 .00
2008 冲压库 19932.07 .00 .00 .00 .00 19932.07 .00 .00 .00 .00 .00 .00 .00
2008 钢材库 4062298.24 .00 .00 .00 .00 4062298.24 .00 .00 .00 .00 .00 .00 .00
2008 公用库 9351.16 .00 .00 .00 .00 9351.16 .00 .00 .00 .00 .00 .00 .00
2008 加工库 199146.36 .00 .00 .00 .00 199146.36 .00 .00 .00 .00 .00 .00 .00
2008 样机库 23440.78 .00 .00 .00 .00 23440.78 .00 .00 .00 .00 .00 .00 .00
2008 装配库 123858.91 .00 .00 .00 .00 123858.91 .00 .00 .00 .00 .00 .00 .00
2008 NULL 5252781.27 .00 .00 .00 .00 5252781.27 .00 .00 .00 .00 .00 .00 .00
2008 半成品库 313916.43 .00 .00 .00 .00 .00 313916.43 .00 .00 .00 .00 .00 .00
2008 半成品库-不良品 12906.63 .00 .00 .00 .00 .00 12906.63 .00 .00 .00 .00 .00 .00
2008 材料库 502988.61 .00 .00 .00 .00 .00 502988.61 .00 .00 .00 .00 .00 .00
2008 材料库-不良品 28365.22 .00 .00 .00 .00 .00 28365.22 .00 .00 .00 .00 .00 .00
2008 成品库 264196.22 .00 .00 .00 .00 .00 264196.22 .00 .00 .00 .00 .00 .00
2008 冲压库 29905.00 .00 .00 .00 .00 .00 29905.00 .00 .00 .00 .00 .00 .00
2008 冲压库-不良品 741.83 .00 .00 .00 .00 .00 741.83 .00 .00 .00 .00 .00 .00
2008 钢材库 4060658.65 .00 .00 .00 .00 .00 4060658.65 .00 .00 .00 .00 .00 .00
2008 公用库 48695.06 .00 .00 .00 .00 .00 48695.06 .00 .00 .00 .00 .00 .00
2008 加工库 134161.93 .00 .00 .00 .00 .00 134161.93 .00 .00 .00 .00 .00 .00
2008 加工库-不良品 541.95 .00 .00 .00 .00 .00 541.95 .00 .00 .00 .00 .00 .00
2008 临时库 7465.66 .00 .00 .00 .00 .00 7465.66 .00 .00 .00 .00 .00 .00
2008 威海市东升电镀厂 3151.31 .00 .00 .00 .00 .00 3151.31 .00 .00 .00 .00 .00 .00
2008 温玉保 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00
2008 样机库 23440.78 .00 .00 .00 .00 .00 23440.78 .00 .00 .00 .00 .00 .00
2008 周珂明 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00
2008 装配库 269668.59 .00 .00 .00 .00 .00 269668.59 .00 .00 .00 .00 .00 .00
2008 NULL 5700803.87 .00 .00 .00 .00 .00 5700803.87 .00 .00 .00 .00 .00 .00
]

...全文