SELECT
A.Proid,
A.[Date],
[In] = ISNULL(B.Qty,0),
[0ut] = ISNULL(C.Qty,0),
[Bal] = ISNULL(D.Qty,0)
FROM
(SELECT Proid,[Date] FROM In
UNION
SELECT Proid,[Date] FROM OuT
UNION
SELECT Proid,[Date] FROM Back) A
LEFT JOIN
In B
ON
A.Proid = B.Proid AND A.[Date] = B.[Date]
LEFT JOIN
Out C
ON
A.Proid = C.Proid AND A.[Date] = C.[Date]
LEFT JOIN
Back D
ON
A.Proid = D.Proid AND A.[Date] = D.[Date]
ORDER BY
A.Proid,A.[Date]
A.Proid,
A.[Date],
[In] = ISNULL(B.Qty,0),
[0ut] = ISNULL(C.Qty,0),
[Bal] = ISNULL(D.Qty,0)
FROM
(SELECT Proid,[Date] FROM In
UNION
SELECT Proid,[Date] FROM OuT
UNION
SELECT Proid,[Date] FROM Back) A
LEFT JOIN
In B
ON
A.Proid = B.Proid AND A.[Date] = B.[Date]
LEFT JOIN
Out C
ON
A.Proid = C.Proid AND A.[Date] = C.[Date]
LEFT JOIN
Back D
ON
A.Proid = D.Proid AND A.[Date] = D.[Date]
ORDER BY
A.Proid,A.[Date]
create table [In]
(
proid varchar(10),Date datetime,Qty int
)
create table [Out]
(
proid varchar(10),Date datetime,Qty int
)
create table [Back]
(
proid varchar(10),Date datetime,Qty int
)
insert [In]
select 'B001','2005-01-03',20 union
select 'B002','2005-01-03',50
insert [Out]
select 'B001','2005-01-08',10 union
select 'B001','2005-01-06',6 union
select 'B002','2005-01-03',8
insert [Back]
select 'B002','2005-01-09',1--测试
select [type]=1,* into # from [In]
union all
select 2,* from [Out]
union all
select 3,* from [Back]select
proid,
date,
[In]=(case when type<>2 then Qty else 0 end) ,
[Out]=(case when type=2 then Qty else 0 end) ,
[Bal]=(select sum(case when type<>2 then Qty else -Qty end) from # B where B.proid=A.proid and B.date<=A.date ) from # A order by proid--删除测试环境
drop table #
drop table [In],[Out],Back--结果
/*proid date In Out Bal
---------- ------------------------------------------------------ ---
B001 2005-01-03 00:00:00.000 20 0 20
B001 2005-01-06 00:00:00.000 0 6 14
B001 2005-01-08 00:00:00.000 0 10 4
B002 2005-01-03 00:00:00.000 50 0 42
B002 2005-01-03 00:00:00.000 0 8 42
B002 2005-01-09 00:00:00.000 1 0 43(所影响的行数为 6 行)
*/
create table [In]
(
proid varchar(10),Date datetime,Qty int
)
create table [Out]
(
proid varchar(10),Date datetime,Qty int
)
create table [Back]
(
proid varchar(10),Date datetime,Qty int
)
insert [In]
select 'B001','2005-01-03',20 union
select 'B002','2005-01-03',50
insert [Out]
select 'B001','2005-01-08',10 union
select 'B001','2005-01-06',6 union
select 'B002','2005-01-03',8
insert [Back]
select 'B002','2005-01-09',1--测试
declare @tb table (ID int identity,type int,proid varchar(10),date datetime,Qty int)
insert @tb(type,proid,[date],[Qty])
select [type]=1,* from [In]
union all
select 2,* from [Out]
union all
select 3,* from [Back]select
proid,
date,
[In]=(case when type<>2 then Qty else 0 end) ,
[Out]=(case when type=2 then Qty else 0 end) ,
[Bal]=(select sum(case when type<>2 then Qty else -Qty end) from @tb B where B.proid=A.proid and B.ID<=A.ID ) from @tb A order by proid--删除测试环境
drop table [In],[Out],Back--结果
/*
proid date In Out Bal
---------- ------------------------------------------------------ -
B001 2005-01-03 00:00:00.000 20 0 20
B001 2005-01-06 00:00:00.000 0 6 14
B001 2005-01-08 00:00:00.000 0 10 4
B002 2005-01-03 00:00:00.000 50 0 50
B002 2005-01-03 00:00:00.000 0 8 42
B002 2005-01-09 00:00:00.000 1 0 43(所影响的行数为 6 行)
*/
有以下问题:
1. 余额(Bal)不知如何计算.
2. 当In和Out是同一天时,In和Out会显示在同一行.记录数少了一条.当同一天有两项In,一项Out时, 产生两项记录,Out的数量在两项中重复出现.总数也不对了.
SBO数据库设定的时间只精确到日期,所以A.[Date] = C.[Date]这样的约束是否可行啊?
请您帮忙再看看.vivianfdlpw()的方法中有的语句没用过,正在学习.看懂了再测试一下.
真是高手啊!