code date in out
AAA 2010-05-01 20 10
AAA 2010-05-02 30 5
BBB 2010-05-01 17 8
CCC 2010-05-03 57 1查询的结果: 2010-05-01 2010-05-02 2010-05-03 in_sum out_sun
code in out in out in out
AAA 20 10 30 5 0 0 50 15
BBB 17 8 17 8
CCC 57 1 57 1
============
就是把日期转成列, 入库出库数量按日期显示, 并在最后一列汇总下这个款号的所有日期出入库总数。一定要用PIVOT做, 因为case when 列一多速度很慢很慢 数据库SQL2005 谢谢~~ 在线等~~~。
AAA 2010-05-01 20 10
AAA 2010-05-02 30 5
BBB 2010-05-01 17 8
CCC 2010-05-03 57 1查询的结果: 2010-05-01 2010-05-02 2010-05-03 in_sum out_sun
code in out in out in out
AAA 20 10 30 5 0 0 50 15
BBB 17 8 17 8
CCC 57 1 57 1
============
就是把日期转成列, 入库出库数量按日期显示, 并在最后一列汇总下这个款号的所有日期出入库总数。一定要用PIVOT做, 因为case when 列一多速度很慢很慢 数据库SQL2005 谢谢~~ 在线等~~~。
code(款号) date(日期) in(入库数量) out(出库数量)
AAA 2010-5-1 20 10
AAA 2010-5-2 30 5
BBB 2010-5-1 17 8
CCC 2010-5-3 57 1
2010-5-1 2010-5-2 2010-5-3 in_sum(所有天数入库汇总) out_sum(所有天数出库汇总)
code(款号) in(入库数量) out(出库数量) in(入库数量) out(出库数量) in(入库数量) out(出库数量)
AAA 20 10 30 5 50 15
BBB 17 8 17 8
CCC 57 1 57 1
drop table #tb
create table #tb([code] varchar(5),[date] smalldatetime,[in] int,[out] int)
insert into #tb select 'AAA','2010-05-01',20,10
union all select 'AAA','2010-05-02',30,5
union all select 'BBB','2010-05-01',17,8
union all select 'CCC','2010-05-03',57,1
select
a.[code],[2010-05-01_in],[2010-05-01_out],[2010-05-02_in],[2010-05-02_out],[2010-05-03_in],[2010-05-03_out],[in_sum],[out_sum]
from
(
SELECT [code],[2010-05-01] as [2010-05-01_in],[2010-05-02] as [2010-05-02_in],[2010-05-03] as [2010-05-03_in]
FROM
(SELECT [code],[date],[in] FROM #tb) a
PIVOT
(sum([in]) FOR [date] IN ([2010-05-01],[2010-05-02],[2010-05-03])) b
) a
left join
(
SELECT [code],[2010-05-01] as [2010-05-01_out],[2010-05-02] as [2010-05-02_out],[2010-05-03] as [2010-05-03_out]
FROM
(SELECT [code],[date],[out] FROM #tb) a
PIVOT
(sum([out]) FOR [date] IN ([2010-05-01],[2010-05-02],[2010-05-03])) b
) b
on a.[code] = b.[code]
left join
(
select [code],sum([in]) as in_sum,sum([out]) as out_sum from #tb group by [code]
) c
on a.[code] = c.[code]/*
code 2010-05-01_in 2010-05-01_out 2010-05-02_in 2010-05-02_out 2010-05-03_in 2010-05-03_out in_sum out_sum
----- ------------- -------------- ------------- -------------- ------------- -------------- ----------- -----------
AAA 20 10 30 5 NULL NULL 50 15
BBB 17 8 NULL NULL NULL NULL 17 8
CCC NULL NULL NULL NULL 57 1 57 1(3 行受影响)
*/