求查询表里对应数量汇总合计相减的明细表
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
SOCP0245 5000.00
SOCP0245 2000.00
SOCP0245 9000.00
SOCP0241 1000.00
SOCP0241 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)所以没有显示出来
谢谢哪大虾帮忙！
[解决办法]
貌似有点问题~
[解决办法]
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
[解决办法]
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
[解决办法]
看看
子陌老大都来了,应该没有问题了吧
[解决办法]
LZ，应该可以了吧？
[解决办法]
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
