物料移轉的SQL

[勉励前行]

物料移轉表：
物料 倉庫From　倉庫To　數量 日期
M A B 10 2010/09/01
M A C 20 2010/09/02
M B C 10 2010/09/01
M B D 5 2010/09/03
M C B 10 2010/09/01
M C D 25 2010/09/04

1、物料從　倉庫From 轉移至 倉庫To , 求各倉庫現有庫存SQL。

結果：
物料 倉庫　數量
M A -30
M B 5
M C -5
M D 30

2、各倉庫每日進出數量統計SQL
結果：
物料 日期 倉庫　進 出

[feegle_develop]

--问题一
if OBJECT_ID('a') is not null
  drop table a
go

create table a
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 int,
col5 datetime
)
go

insert into a
select 'M','A','B',10,'2010/09/01'
union all
select 'M','A','C',20,'2010/09/02'
union all
select 'M','B','C',10,'2010/09/01'
union all
select 'M','B','D',5,'2010/09/03'
union all
select 'M','C','B',10,'2010/09/01'
union all
select 'M','C','D',25,'2010/09/04'
go

select col1,col2,SUM(col24)
from
(
select col1,col2,col24=-col4
from a
union all
select col1,col3,col34=col4
from a
) b
group by col1,col2
go

[feegle_develop]

--完整的
if OBJECT_ID('a') is not null
  drop table a
go

create table a
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 int,
col5 datetime
)
go

insert into a
select 'M','A','B',10,'2010/09/01'
union all
select 'M','A','C',20,'2010/09/02'
union all
select 'M','B','C',10,'2010/09/01'
union all
select 'M','B','D',5,'2010/09/03'
union all
select 'M','C','B',10,'2010/09/01'
union all
select 'M','C','D',25,'2010/09/04'
go

select 物料=col1,倉庫=col2,數量=SUM(col24)
from
(
select col1,col2,col24=-col4
from a
union all
select col1,col3,col34=col4
from a
) b
group by col1,col2
go

select 时间=case when b.col5 is null then c.col5 else b.col5 end,
物料=case when b.col1 IS null then c.col1 else b.col1 end,
倉庫=case when b.col2 is null then c.col3 else b.col2 end,c.进,b.出
from
(
select col5,col1,col2,出=SUM(col4)
from a
group by col1,col2,col5
) b
full join
(
select col5,col1,col3,进=SUM(col4)
from a
group by col1,col3,col5
) c
on b.col1=c.col1 and b.col2=c.col3 and b.col5=c.col5
go

[勉励前行]

貼一下建表的語句：
CREATE TABLE DataMove([Meterial] [nchar](10),[MoveFrom] [nchar](10) ,
[MoveTo] [nchar](10) ,[Count] [int] ,[MoveTime] [datetime])
INSERT INTO DataMove
SELECT 'M', 'A', 'B', '10', '2010/09/01' union ALL
SELECT 'M', 'A', 'C', '20', '2010/09/02' union ALL
SELECT 'M', 'B', 'C', '10', '2010/09/01' union ALL
SELECT 'M', 'B', 'D', '5', '2010/09/03' union ALL
SELECT 'M', 'C', 'B', '10', '2010/09/01' union ALL
SELECT 'M', 'C', 'D', '25', '2010/09/04'

不知道如何寫統計的

[Rockets_NBA]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into #tb
select 'M','A','B',10,'2010/09/01' union all
select 'M','A','C',20,'2010/09/02' union all
select 'M','B','C',10,'2010/09/01' union all
select 'M','B','D',5,'2010/09/03' union all
select 'M','C','B',10,'2010/09/01' union all
select 'M','C','D',25,'2010/09/04'

select [倉庫],
	物料,
	sum(數量)
from 
(
	select 物料,[倉庫From] as [倉庫],-數量 as 數量 from #tb
	union all 
	select 物料,[倉庫To] as [倉庫],數量 from #tb
)b
group by 物料 ,[倉庫]


倉庫   物料   
---- ---- -----------
A    M    -30
B    M    5
C    M    -5
D    M    30

(4 行受影响)

[ws_hgo]

if object_id('tb') is not null drop table tb
go 
create table tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into tb
select 'M','A','B',10,'2010/09/01' union all
select 'M','A','C',20,'2010/09/02' union all
select 'M','B','C',10,'2010/09/01' union all
select 'M','B','D',5,'2010/09/03' union all
select 'M','C','B',10,'2010/09/01' union all
select 'M','C','D',25,'2010/09/04'

select 物料,倉庫,sum(數量) 數量
from
(
select 物料,[倉庫From] as 倉庫,-數量 as  數量 from tb
union all
select 物料,[倉庫To],數量 from tb
) tt
group by 物料,倉庫

物料   倉庫   數量
---- ---- -----------
M    A    -30
M    B    5
M    C    -5
M    D    30

(4 行受影响)

[Rockets_NBA]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into #tb
select 'M','A','B',10,'2010/09/01' union all
select 'M','A','C',20,'2010/09/02' union all
select 'M','B','C',10,'2010/09/01' union all
select 'M','B','D',5,'2010/09/03' union all
select 'M','C','B',10,'2010/09/01' union all
select 'M','C','D',25,'2010/09/04'

select [倉庫],
	物料,
	日期 =convert(varchar(10),日期,120),
	[进]=sum(case when 數量>0 then 數量 else 0 end),
	[出]=sum(case when 數量<0 then 數量 else 0 end)
from 
(
	select 物料,[倉庫From] as [倉庫],-數量 as 數量,日期 from #tb
	union all 
	select 物料,[倉庫To] as [倉庫],數量,日期 from #tb
)b
group by 物料 ,[倉庫],日期


倉庫   物料   日期         进           出
---- ---- ---------- ----------- -----------
A    M    2010-09-01 0           -10
A    M    2010-09-02 0           -20
B    M    2010-09-01 20          -10
B    M    2010-09-03 0           -5
C    M    2010-09-01 10          -10
C    M    2010-09-02 20          0
C    M    2010-09-04 0           -25
D    M    2010-09-03 5           0
D    M    2010-09-04 25          0

(9 行受影响)

[ws_hgo]

if object_id('tb') is not null drop table tb
go 
create table tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into tb
select 'M','A','B',10,'2010/09/01' union all
select 'M','A','C',20,'2010/09/02' union all
select 'M','B','C',10,'2010/09/01' union all
select 'M','B','D',5,'2010/09/03' union all
select 'M','C','B',10,'2010/09/01' union all
select 'M','C','D',25,'2010/09/04'

--物料從　倉庫From 轉移至 倉庫To , 求各倉庫現有庫存SQL。
select 物料,倉庫,sum(數量) 數量
from
(
select 物料,[倉庫From] as 倉庫,-數量 as  數量 from tb
union all
select 物料,[倉庫To],數量 from tb
) tt
group by 物料,倉庫

物料   倉庫   數量
---- ---- -----------
M    A    -30
M    B    5
M    C    -5
M    D    30

(4 行受影响)
--2各倉庫每日進出數量統計SQL

select 物料,日期,倉庫,
      sum(case when 數量>0 then 數量 else 0 end) '进',
      sum(case when 數量<0 then 數量 else 0 end) '出'
from
(
select 物料,[倉庫From] as 倉庫,-數量 as  數量,日期 from tb
union all
select 物料,[倉庫To],數量,日期 from tb
) tt
group by 物料,倉庫,日期

物料   日期                      倉庫   进           出
---- ----------------------- ---- ----------- -----------
M    2010-09-01 00:00:00.000 A    0           -10
M    2010-09-02 00:00:00.000 A    0           -20
M    2010-09-01 00:00:00.000 B    20          -10
M    2010-09-03 00:00:00.000 B    0           -5
M    2010-09-01 00:00:00.000 C    10          -10
M    2010-09-02 00:00:00.000 C    20          0
M    2010-09-04 00:00:00.000 C    0           -25
M    2010-09-03 00:00:00.000 D    5           0
M    2010-09-04 00:00:00.000 D    25          0

(9 行受影响)

[Rockets_NBA]

select [倉庫],
	物料,
	日期 =convert(varchar(10),日期,120),
	[进]=sum(case when 數量>0 then 數量 else 0 end),
	[出]=sum(case when 數量<0 then -數量 else 0 end)
from 
(
	select 物料,[倉庫From] as [倉庫],-數量 as 數量,日期 from #tb
	union all 
	select 物料,[倉庫To] as [倉庫],數量,日期 from #tb
)b
group by 物料 ,[倉庫],日期


 
倉庫   物料   日期         进           出
---- ---- ---------- ----------- -----------
A    M    2010-09-01 0           10
A    M    2010-09-02 0           20
B    M    2010-09-01 20          10
B    M    2010-09-03 0           5
C    M    2010-09-01 10          10
C    M    2010-09-02 20          0
C    M    2010-09-04 0           25
D    M    2010-09-03 5           0
D    M    2010-09-04 25          0

(9 行受影响)

[ws_hgo]

修改下

if object_id('tb') is not null drop table tb
go 
create table tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into tb
select 'M','A','B',10,'2010/09/01' union all
select 'M','A','C',20,'2010/09/02' union all
select 'M','B','C',10,'2010/09/01' union all
select 'M','B','D',5,'2010/09/03' union all
select 'M','C','B',10,'2010/09/01' union all
select 'M','C','D',25,'2010/09/04'

--物料從　倉庫From 轉移至 倉庫To , 求各倉庫現有庫存SQL。
select 物料,倉庫,sum(數量) 數量
from
(
select 物料,[倉庫From] as 倉庫,-數量 as  數量 from tb
union all
select 物料,[倉庫To],數量 from tb
) tt
group by 物料,倉庫

物料   倉庫   數量
---- ---- -----------
M    A    -30
M    B    5
M    C    -5
M    D    30

(4 行受影响)
--2各倉庫每日進出數量統計SQL

select 物料,日期,倉庫,
      sum(case when 數量>0 then 數量 else 0 end) '进',
      abs(sum(case when 數量<0 then 數量 else 0 end)) '出'
from
(
select 物料,[倉庫From] as 倉庫,-數量 as  數量,日期 from tb
union all
select 物料,[倉庫To],數量,日期 from tb
) tt
group by 物料,倉庫,日期

物料   日期                      倉庫   进           出
---- ----------------------- ---- ----------- -----------
M    2010-09-01 00:00:00.000 A    0           10
M    2010-09-02 00:00:00.000 A    0           20
M    2010-09-01 00:00:00.000 B    20          10
M    2010-09-03 00:00:00.000 B    0           5
M    2010-09-01 00:00:00.000 C    10          10
M    2010-09-02 00:00:00.000 C    20          0
M    2010-09-04 00:00:00.000 C    0           25
M    2010-09-03 00:00:00.000 D    5           0
M    2010-09-04 00:00:00.000 D    25          0

(9 行受影响)

[qq574444572]

[Quote=引用 2 楼 rockets_nba 的回复:]
SQL code
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (物料 varchar(1),[倉庫From] varchar(1),[倉庫To] varchar(1),數量 int,日期 datetime)
insert into #tb
sel……
[/Quote]
可行