select a.fyear,b.fname,
'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
order by b.fname--查询结果
年度 仓库 1月 二月 三月 四月 五月 六月 七月 八月 ...
2009 半成品库 296771.41 .00 .00 .00 .00 .00 .00 .00
2009 半成品库 .00 246305.64 .00 .00 .00 .00 .00 .00
2009 半成品库 .00 .00 212076.73 .00 .00 .00 .00 .00
2009 半成品库 .00 .00 .00 107207.75 .00 .00 .00 .00
2009 半成品库 .00 .00 .00 .00 129850.51 .00 .00 .00
2009 半成品库 .00 .00 .00 .00 .00 173769.09 .00 .00
2009 半成品库 .00 .00 .00 .00 .00 .00 106501.56 .00
2009 半成品库 .00 .00 .00 .00 .00 .00 .00 145109.19
2009 半成品库 .00 .00 .00 .00 .00 .00 .00 .00问题:怎样把查询结果集中在第一行??
'1月'=max(case when fperiod=1 then sum(a.fendbal) else 0 end) ,
'2月'=max(case when fperiod=2 then sum(a.fendbal) else 0 end) ,
'3月'=max(case when fperiod=3 then sum(a.fendbal) else 0 end) ,
'4月'=max(case when fperiod=4 then sum(a.fendbal) else 0 end) ,
'5月'=max(case when fperiod=5 then sum(a.fendbal) else 0 end) ,
'6月'=max(case when fperiod=6 then sum(a.fendbal) else 0 end) ,
'7月'=max(case when fperiod=7 then sum(a.fendbal) else 0 end) ,
'8月'=max(case when fperiod=8 then sum(a.fendbal) else 0 end) ,
'9月'=max(case when fperiod=9 then sum(a.fendbal) else 0 end) ,
'10月'=max(case when fperiod=10 then sum(a.fendbal) else 0 end ,
'11月'=max(case when fperiod=11 then sum(a.fendbal) else 0 end ,
'12月'=max(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
order by b.fname 也可以用子查询的,直接在外部加一个查询
select a.fyear,b.fname,
'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 into #t
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
order by b.fname
select 年度,仓库,max(1月),max(2月),max(3月),max(4月),max(5月),
max(6月),max(7月),max(8月),max(9月),max(10月),max(11月),max(12月)
from #t group by 年度,仓库drop table #t
用临时表把数据转接一下就行了。
'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 into #t
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
order by b.fname
select 年度,仓库,max([1月]),max([2月]),max(3月),max(4月),max(5月),
max(6月),max(7月),max(8月),max(9月),max(10月),max(11月),max(12月)
from #t group by 年度,仓库drop table #t
你就稍微改下呀,
都加上[]或''
select 年度,仓库,max([1月]),max([2月]),max([3月]),max([4月]),max([5月]),
max([6月]),max([7月]),max([8月]),max([9月]),max([10月]),max([11月]),max([12月])
from #t group by 年度,仓库
select a.fyear,b.fname,
'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,b.fname
order by b.fname