求查询表里对应数量汇总合计相减的明细表

[BarryW]

MFPOS 表有字段为：
OS_ID, CLS_ID, BIL_TYPE

SO F 01
SO F 01
SO F 02
SO F 01
SO F 02
.....

TFPOS表有字段为：
OS_ID, PRD_NO, QTY

SO CP0245 5000.00
SO CP0245 2000.00
SO CP0245 9000.00
SO CP0241 1000.00
SO CP0241 900.00
.....


我想求出两个表MFPOS.OS_ID=TFPOS.OS_ID='SO' 并 MFPOS.CLS_ID='F'
且MFPOS.BIL_TYPE都等于'02'对应TFPOS.PRD_NO的TFPOS.QTY总合计减去
MFPOS.BIL_TYPE都等于'01'对应TFPOS.PRD_NO的TFPOS.QTY总合计大于0的数值;

另一条件：按TFPOS.PRD_NO可查询；

显示为：

PRD_NO, QTY1, QTY2, QTY3
CP0245 9000 7000 2000
......

注：QTY1为MFPOS.BIL_TYPE都等于'02'的PRD_NO总合计
QTY2为MFPOS.BIL_TYPE都等于'01'的PRD_NO总合计
QTY3=QTY1-QTY2

上面的CP0241这个货号因为对应'02'的合计减去'01'的合计少于零(900-1000=-100)所以没有显示出来

谢谢哪大虾帮忙！

[BarryW]

问题是：
BIL_TYPE=‘01’与BIL_TYPE=‘02’ 对应的 TFPOS表中的数据，怎么区分？


根据OS_ID来关联两个表，区分根据PRD_NO来区分
不同的PRD_NO汇总来合计

[BarryW]

MFPOS 表有字段为：
OS_ID, OS_NO CLS_ID, BIL_TYPE
SO SO71080001 F 01
SO SO71080002 F 01
SO SO71080003 F 02
SO SO71090001 F 01
SO SO71090001 F 02
.....
TFPOS表有字段为：
OS_ID, OS_NO PRD_NO, QTY EST_DD
SO SO71080001 CP0245 5000.00 2007-01-21 00:00:00.000
SO SO71080002 CP0245 2000.00 2007-01-25 00:00:00.000
SO SO71080003 CP0245 9000.00 2007-01-24 00:00:00.000
SO SO71090001 CP0241 1000.00 2007-01-29 00:00:00.000
SO SO71090001 CP0241 900.00 2007-02-22 00:00:00.000
.....
我想求出两个表MFPOS.OS_ID=TFPOS.OS_ID='SO',MFPOS.OS_NO=TFPOS.OS_NO 并 MFPOS.CLS_ID='F'
且MFPOS.BIL_TYPE都等于'02'对应TFPOS.PRD_NO的TFPOS.QTY总合计减去
MFPOS.BIL_TYPE都等于'01'对应TFPOS.PRD_NO的TFPOS.QTY总合计大于0的数值;
另一条件：按TFPOS.PRD_NO可查询,并可按EST_DD范围查询；
显示为：
PRD_NO, QTY1, QTY2, QTY3
CP0245 9000 7000 2000
......
注：QTY1为MFPOS.BIL_TYPE都等于'02'的PRD_NO总合计
QTY2为MFPOS.BIL_TYPE都等于'01'的PRD_NO总合计
QTY3=QTY1-QTY2
上面的CP0241这个货号因为对应'02'的合计减去'01'的合计少于零(900-1000=-100)所以没有显示出来

谢谢哪大虾帮忙！

[BarryW]

上面的我做了一个修正，加了一个OS_NO关联两表，EST_DD为一个日期查询段;这样是有关联性了吧

[caixia615]

没有唯一主键貌似挺困难,还是等星星们来帮你 看看吧

[BarryW]

有哪位来帮帮俺哟

[子陌红尘]

create table MFPOS(OS_ID varchar(10),OS_NO varchar(10),CLS_ID varchar(10),BIL_TYPE varchar(10))
insert into MFPOS select 'SO','SO71080001','F','01'
insert into MFPOS select 'SO','SO71080002','F','01'
insert into MFPOS select 'SO','SO71080003','F','02'
insert into MFPOS select 'SO','SO71090001','F','01'
insert into MFPOS select 'SO','SO71090002','F','02'
create table TFPOS(OS_ID varchar(10),OS_NO varchar(10),PRD_NO varchar(10),QTY numeric(8,2))
insert into TFPOS select 'SO','SO71080001','CP0245',5000.00
insert into TFPOS select 'SO','SO71080002','CP0245',2000.00
insert into TFPOS select 'SO','SO71080003','CP0245',9000.00
insert into TFPOS select 'SO','SO71090001','CP0241',1000.00
insert into TFPOS select 'SO','SO71090002','CP0241',900.00
go


select
b.PRD_NO,
QTY1=sum(case a.BIL_TYPE when '02' then b.QTY else 0 end),
QTY2=sum(case a.BIL_TYPE when '01' then b.QTY else 0 end),
QTY3=sum(case a.BIL_TYPE when '02' then b.QTY else -b.QTY end)
from
MFPOS a,TFPOS b
where
a.OS_ID=b.OS_ID and a.OS_NO=b.OS_NO and a.CLS_ID='F'
group by
b.PRD_NO
having
sum(case a.BIL_TYPE when '02' then b.QTY else -b.QTY end)>=0
go


/*
PRD_NO QTY1 QTY2 QTY3
---------- ----------- ----------- -----------
CP0245 9000.00 7000.00 2000.00
*/

drop table MFPOS,TFPOS
go

[xluzhong]

CREATE TABLE MFPOS (OS_ID VARCHAR(10), OS_NO VARCHAR(20), CLS_ID VARCHAR(2),BIL_TYPE VARCHAR(10))
INSERT INTO MFPOS
SELECT 'SO','SO71080001','F','01' UNION
SELECT 'SO','SO71080002','F','01' UNION
SELECT 'SO','SO71080003','F','02' UNION
SELECT 'SO','SO71090001','F','01' UNION
SELECT 'SO','SO71090001','F','02'

CREATE TABLE TFPOS(OS_ID VARCHAR(10),OS_NO VARCHAR(20),PRD_NO VARCHAR(10),QTY MONEY,EST_DD DATETIME)
INSERT INTO TFPOS
SELECT 'SO','SO71080001','CP0245',5000.00,'2007-01-21 00:00:00.000' UNION
SELECT 'SO','SO71080002','CP0245',2000.00,'2007-01-25 00:00:00.000' UNION
SELECT 'SO','SO71080003','CP0245',9000.00,'2007-01-24 00:00:00.000' UNION
SELECT 'SO','SO71090001','CP0241',1000.00,'2007-01-29 00:00:00.000' UNION
SELECT 'SO','SO71090001','CP0241',900.00,'2007-02-22 00:00:00.000'

--我想求出两个表MFPOS.OS_ID=TFPOS.OS_ID='SO',MFPOS.OS_NO=TFPOS.OS_NO 并 MFPOS.CLS_ID='F'
--且MFPOS.BIL_TYPE都等于'02'对应TFPOS.PRD_NO的TFPOS.QTY总合计减去
--MFPOS.BIL_TYPE都等于'01'对应TFPOS.PRD_NO的TFPOS.QTY总合计大于0的数值;
--另一条件：按TFPOS.PRD_NO可查询,并可按EST_DD范围查询；-
--显示为：
--PRD_NO, QTY1, QTY2, QTY3
--CP0245 9000 7000 2000
--......
--注：QTY1为MFPOS.BIL_TYPE都等于'02'的PRD_NO总合计
-- QTY2为MFPOS.BIL_TYPE都等于'01'的PRD_NO总合计
-- QTY3=QTY1-QTY2
--上面的CP0241这个货号因为对应'02'的合计减去'01'的合计少于零(900-1000=-100)所以没有显示出来--

--SELECT B.PRD_NO,QTY,BIL_TYPE
SELECT B.PRD_NO,
QTY1 = SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE 0 END),
QTY2 = SUM(CASE WHEN A.BIL_TYPE = '01' THEN B.QTY ELSE 0 END),
QTY3 = SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE -B.QTY END)
FROM MFPOS A
INNER JOIN TFPOS B
ON A.OS_ID = B.OS_ID AND A.OS_NO=B.OS_NO
WHERE A.CLS_ID = 'F'
GROUP BY B.PRD_NO
HAVING SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE -B.QTY END) > 0


DROP TABLE MFPOS
DROP TABLE TFPOS

[子陌红尘]

select
b.PRD_NO,
QTY1=sum(case a.BIL_TYPE when '02' then b.QTY else 0 end),
QTY2=sum(case a.BIL_TYPE when '01' then b.QTY else 0 end),
QTY3=sum(case a.BIL_TYPE when '02' then b.QTY else -b.QTY end)
from
MFPOS a,TFPOS b
where
a.OS_ID=b.OS_ID and a.OS_NO=b.OS_NO and a.CLS_ID='F'
group by
b.PRD_NO
having
sum(case a.BIL_TYPE when '02' then b.QTY else -b.QTY end)>0 --多了一个"="，删除掉
go

[冷箫轻笛]

看看

子陌老大都来了,应该没有问题了吧

[lovcal]

LZ，应该可以了吧？

[nat_2006]

SELECT T.* INTO #TMEP FROM
(SELECT 'SOSO71080001' AS OS_ID,' ' AS OS_NO,'F' AS CLS_ID, '01' AS BIL_TYPE
UNION
SELECT 'SOSO71080002',' ', 'F', '01'
UNION
SELECT 'SOSO71080003',' ', 'F', '02'
UNION
SELECT 'SOSO71090001',' ', 'F', '01'
UNION
SELECT 'SOSO71090001',' ', 'F', '02') T



SELECT Y.* INTO #TMEP1 FROM
(SELECT 'SOSO71080001' AS OS_ID,' ' AS OS_NO,'CP0245' AS PRD_NO,5000.00 AS QTY, '2007-01-21 00:00:00.000' AS EST_DD
UNION
SELECT 'SOSO71080002',' ','CP0245',2000.00, '2007-01-25 00:00:00.000'
UNION
SELECT 'SOSO71080003',' ','CP0245',9000.00, '2007-01-24 00:00:00.000'
UNION
SELECT 'SOSO71090001',' ','CP0241',1000.00, '2007-01-29 00:00:00.000'
UNION
SELECT 'SOSO71090001',' ','CP0241',900.00, '2007-02-22 00:00:00.000') Y

SELECT SUM(CASE WHEN #TMEP.BIL_TYPE='01' THEN -#TMEP1.QTY ELSE #TMEP1.QTY END) AS QTY3,
SUM(CASE WHEN #TMEP.BIL_TYPE='01' THEN #TMEP1.QTY ELSE 0 END) AS QTY2,
SUM(CASE WHEN #TMEP.BIL_TYPE='02' THEN #TMEP1.QTY ELSE 0 END) AS QTY1,#TMEP1.PRD_NO
FROM #TMEP
LEFT JOIN #TMEP1
ON #TMEP.OS_ID=#TMEP1.OS_ID
WHERE #TMEP.CLS_ID='F'
GROUP BY #TMEP1.PRD_NO

[caixia615]

貌似有点问题~

[BarryW]

怎么有点问题呢

[xiequanqin]

MFPOS.BIL_TYPE都等于'02'对应TFPOS.PRD_NO
MFPOS.BIL_TYPE都等于'01'对应TFPOS.PRD_NO

这2个表少了个关联对照关系吧？

[BarryW]

关联对应是：
MFPOS.OS_ID=TFPOS.OS_ID='SO'

[BarryW]

关联对应是：
MFPOS.OS_ID=TFPOS.OS_ID='SO'
并 BIL_TYPE='02'为PRD_NO所有SUM(QTY)-BIL_TYPE='01'
为PRD_NO所有SUM(QTY)合于零值;
还有条件就是：MFPOS.CLS_ID='F'
按TFPOS.PRD_NO可查询

[BarryW]

谢谢大家的帮忙了了！结果已出来啦，太高兴了；
现在我做了几次按“另一条件：按TFPOS.PRD_NO范围可查询,并可按EST_DD范围查询；”还没有成功！

就是要按在范围（between）：从(PRD_NO 至prd_no) and (EST_DD至EST_DD)范围来查询

[BarryW]

再说一下目前采用的是子陌和xluzhong(Ralph)的两个方案

[xluzhong]

CREATE TABLE MFPOS (OS_ID VARCHAR(10), OS_NO VARCHAR(20), CLS_ID VARCHAR(2),BIL_TYPE VARCHAR(10))
INSERT INTO MFPOS
SELECT 'SO','SO71080001','F','01' UNION
SELECT 'SO','SO71080002','F','01' UNION
SELECT 'SO','SO71080003','F','02' UNION
SELECT 'SO','SO71090001','F','01' UNION
SELECT 'SO','SO71090001','F','02'

CREATE TABLE TFPOS(OS_ID VARCHAR(10),OS_NO VARCHAR(20),PRD_NO VARCHAR(10),QTY MONEY,EST_DD DATETIME)
INSERT INTO TFPOS
SELECT 'SO','SO71080001','CP0245',5000.00,'2007-01-21 00:00:00.000' UNION
SELECT 'SO','SO71080002','CP0245',2000.00,'2007-01-25 00:00:00.000' UNION
SELECT 'SO','SO71080003','CP0245',9000.00,'2007-01-24 00:00:00.000' UNION
SELECT 'SO','SO71090001','CP0241',1000.00,'2007-01-29 00:00:00.000' UNION
SELECT 'SO','SO71090001','CP0241',900.00,'2007-02-22 00:00:00.000'

--我想求出两个表MFPOS.OS_ID=TFPOS.OS_ID='SO',MFPOS.OS_NO=TFPOS.OS_NO 并 MFPOS.CLS_ID='F'
--且MFPOS.BIL_TYPE都等于'02'对应TFPOS.PRD_NO的TFPOS.QTY总合计减去
--MFPOS.BIL_TYPE都等于'01'对应TFPOS.PRD_NO的TFPOS.QTY总合计大于0的数值;
--另一条件：按TFPOS.PRD_NO可查询,并可按EST_DD范围查询；-
--显示为：
--PRD_NO, QTY1, QTY2, QTY3
--CP0245 9000 7000 2000
--......
--注：QTY1为MFPOS.BIL_TYPE都等于'02'的PRD_NO总合计
-- QTY2为MFPOS.BIL_TYPE都等于'01'的PRD_NO总合计
-- QTY3=QTY1-QTY2
--上面的CP0241这个货号因为对应'02'的合计减去'01'的合计少于零(900-1000=-100)所以没有显示出来--

--SELECT B.PRD_NO,QTY,BIL_TYPE

DECLARE @B_ED DATETIME
DECLARE @E_ED DATETIME

SET @B_ED = '2007-01-21'
SET @E_ED = '2007-01-25'

SELECT B.PRD_NO,
QTY1 = SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE 0 END),
QTY2 = SUM(CASE WHEN A.BIL_TYPE = '01' THEN B.QTY ELSE 0 END),
QTY3 = SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE -B.QTY END)
FROM MFPOS A
INNER JOIN TFPOS B
ON A.OS_ID = B.OS_ID AND A.OS_NO=B.OS_NO
WHERE A.CLS_ID = 'F'
AND B.EST_DD BETWEEN @B_ED AND @E_ED
GROUP BY B.PRD_NO
HAVING SUM(CASE WHEN A.BIL_TYPE = '02' THEN B.QTY ELSE -B.QTY END) > 0


DROP TABLE MFPOS
DROP TABLE TFPOS

[BarryW]

谢谢，结贴了