一個sql的查詢

[moqijun]

有一張表,格式如下:
rq lh rksl cksl
2003-09-04 AL0005 27.00 .00
2003-09-05 AL0005 .00 17.00
2003-09-09 AL0009 .00 8.00
2003-09-23 AL0009 .00 13.00
2003-09-17 AL0013 260.00 .00
2003-09-18 AL0013 .00 182.00
2003-09-19 AL0013 .00 60.00

它是一個月的出入庫情況,
現在我想查詢時把它變為:
一個月每天的出入情況, 每個料號為二行, 一行為入庫情況, 一行為出庫情況
lh 1,2,3,4, 5,..9,..17..18..19..23..30
AL0005 27
AL0005 17
AL0009
AL0009 8 13
AL0013 260
AL0013 182 60

[txlicenhe]

Select * from
(
Select lh,
(case when day(rq) = 1 then rksl else 0 end) as 1,
(case when day(rq) = 2 then rksl else 0 end) as 2,
...
(case when day(rq) = 30 then rksl else 0 end) as 30
from 出入库表
Union all
Select lh,
(case when day(rq) = 1 then cksl else 0 end) as 1,
(case when day(rq) = 2 then cksl else 0 end) as 2,
...
(case when day(rq) = 30 then cksl else 0 end) as 30
from 出入库表
) a
order by lh

[yujohny]

参考这个贴
http://expert.csdn.net/Expert/topic/2240/2240247.xml?temp=3.889102E-02

[waitwater]

Select lh,sum(a1) as 1,sum(a2) as 2,....sum(a30) as 30, from
(
Select lh,
(case when day(rq) = 1 then rksl else 0 end) as a1,
(case when day(rq) = 2 then rksl else 0 end) as a2,
...
(case when day(rq) = 30 then rksl else 0 end) as a30
from 出入库表
Union all
Select lh,
(case when day(rq) = 1 then cksl else 0 end) as a1,
(case when day(rq) = 2 then cksl else 0 end) as a2,
...
(case when day(rq) = 30 then cksl else 0 end) as a30
from 出入库表
) a
order by lh

[moqijun]

按上面的寫法, 一個料號會出現多行.一個料號只要有出入庫就一行.不符合要求.

[zjcxc]

select sort=0,lh
,[1]=sum(case day(rq) when 1 then rksl else 0 end)
,[2]=sum(case day(rq) when 2 then rksl else 0 end)
,[3]=sum(case day(rq) when 3 then rksl else 0 end)
,[4]=sum(case day(rq) when 4 then rksl else 0 end)
,[5]=sum(case day(rq) when 5 then rksl else 0 end)
..............................
,[30]=sum(case day(rq) when 30 then rksl else 0 end)
from 表 group by lh
union all
select sort=1,lh
,[1]=sum(case day(rq) when 1 then cksl else 0 end)
,[2]=sum(case day(rq) when 2 then cksl else 0 end)
,[3]=sum(case day(rq) when 3 then cksl else 0 end)
,[4]=sum(case day(rq) when 4 then cksl else 0 end)
,[5]=sum(case day(rq) when 5 then cksl else 0 end)
..............................
,[30]=sum(case day(rq) when 30 then cksl else 0 end)
from 表 group by lh
order by lh,sort

[zjcxc]

sort字段的加入是为了保证入库的记录在前,出库的记录在后,如果不想让它显示,就用:

select lh,[1],[2],[3],[4],[5],...[30]
from(
select sort=0,lh
,[1]=sum(case day(rq) when 1 then rksl else 0 end)
,[2]=sum(case day(rq) when 2 then rksl else 0 end)
,[3]=sum(case day(rq) when 3 then rksl else 0 end)
,[4]=sum(case day(rq) when 4 then rksl else 0 end)
,[5]=sum(case day(rq) when 5 then rksl else 0 end)
..............................
,[30]=sum(case day(rq) when 30 then rksl else 0 end)
from 表 group by lh
union all
select sort=1,lh
,[1]=sum(case day(rq) when 1 then cksl else 0 end)
,[2]=sum(case day(rq) when 2 then cksl else 0 end)
,[3]=sum(case day(rq) when 3 then cksl else 0 end)
,[4]=sum(case day(rq) when 4 then cksl else 0 end)
,[5]=sum(case day(rq) when 5 then cksl else 0 end)
..............................
,[30]=sum(case day(rq) when 30 then cksl else 0 end)
from 表 group by lh
) a order by lh,sort

[playyuer]

不好!

[realgz]

selct lh,
sum (case when datepart(day,rq)=1 then rksl+ksl else 0 end)
as [1],
……
sum (case when datepart(day,rq)=30 then rksl+ksl else 0 end)
as [30]

from [table]
group by lh,(case when ksl=0 then 1 else 2 end)
order by lh,(case when ksl=0 then 1 else 2 end)

[realgz]

又复制错了是：
selct lh,
sum (case when datepart(day,rq)=1 then rksl+cksl else 0 end)
as [1],
……
sum (case when datepart(day,rq)=30 then rksl+cksl else 0 end)
as [30]

from [table]
group by lh,(case when rksl=0 then 1 else 2 end)
order by lh,(case when rksl=0 then 1 else 2 end)

[realgz]

前提是你同一条记录不能既出库又入库。。。。。

[playyuer]

有8000字符限制

declare @a varchar(8000)
set @a = 'select lh'
declare @b varchar(8000)
set @b = 'select lh'
declare @i integer
set @i = 0
declare @d datetime


select @i = @i + 1
,@a = @a + ',(select cksl from T where lh = a.lh and datediff(day,rq,'''+ cast(dateadd(day, -day(a.rq)+n,a.rq) as varchar) + ''')=0) as ''' + cast(@i as varchar) + '''' + char(13)
+ case when datediff(month,@d,dateadd(day, -day(a.rq)+n,a.rq) ) = 0 or @d is null then '' else ' from T a group by a.lh union all select a.lh' + char(13) end
,@b = @b + ',(select rksl from T where lh = a.lh and datediff(day,rq,'''+ cast(dateadd(day, -day(a.rq)+n,a.rq) as varchar) + ''')=0) as ''' + cast(@i as varchar) + '''' + char(13)
+ case when datediff(month,@d,dateadd(day, -day(a.rq)+n,a.rq) ) = 0 or @d is null then '' else ' from T a group by a.lh union all select a.lh' + char(13) + '' end
,@d=dateadd(day, -day(a.rq)+n,a.rq)
from
(
select lh,min(rq) as rq
from T
group by lh,datediff(month,rq,getdate())
) a
,
(
select 1 as n
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) b
,T
where datediff(month,a.rq,dateadd(day, -day(a.rq) + n,a.rq) )=0
and a.lh *= T.lh
and datediff(day,T.rq, dateadd(day, -day(a.rq) + n,a.rq)) = 0

order by a.lh ,a.rq

--print @a + ' from T a group by a.lh union ' + @b + ' from T a group by a.lh'
exec (@a + ' from T a group by a.lh union ' + @b + ' from T a group by a.lh')

[playyuer]

用行表示,更合SQL理简单些与你的表设计更符合:

select a.lh,dateadd(day, -day(a.rq) + n,a.rq),t.cksl,t.rksl
from
(
select lh,min(rq) as rq
from T
group by lh,datediff(month,rq,getdate())
) a
,
(
select 1 as n
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) b
,T
where datediff(month,a.rq,dateadd(day, -day(a.rq) + n,a.rq) )=0
and a.lh *= T.lh
and datediff(day,T.rq, dateadd(day, -day(a.rq) + n,a.rq)) = 0

order by a.lh ,dateadd(day, -day(a.rq) + n,a.rq)

[changechange]

Select * from
(
Select lh,
(case when day(rq) = 1 then rksl else 0 end) as 1,
(case when day(rq) = 2 then rksl else 0 end) as 2,
...
(case when day(rq) = 30 then rksl else 0 end) as 30
from 出入库表
Union all
Select lh,
(case when day(rq) = 1 then cksl else 0 end) as 1,
(case when day(rq) = 2 then cksl else 0 end) as 2,
...
(case when day(rq) = 30 then cksl else 0 end) as 30
from 出入库表
) a
order by lh