22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE T1 --表1
(
SNO INT ,
SLineNO INT,
Qty INT,
ASNO INT,
ASLineNO INT
);
INSERT INTO T1 VALUES
(1001, 1, 10, 1009, 1),
(1001, 2, 10, 1009, 2),
(1002, 1, 10, 1009, 1),
(1003, 1, 10, 2009, 1),
(1003, 2, 10, 2009, 2),
(1004, 1, 10, 2009, 2),
(1005, 1, 10, 3009, 1),
(1005, 2, 10, 4009, 1);
CREATE TABLE T2--表2
( ASNO INT,
ASLineNO INT,
AQty INT,
);
INSERT INTO T2 VALUES
(1009, 1, 10),
(1009, 2, 10),
(2009, 1, 10000),
(2009, 2, 20),
(3009, 1, 10),
(5009, 1, 30),
(6009, 1, 10);
SNO SLineNO Qty ASNO ASLineNO
1001 1 10 1009 1
1001 2 10 1009 2
1002 1 10 6009 1
--这样也可以哦
select T1.* from T1,T2,(select * from T1 WHERE ASNO NOT IN (SELECT ASNO FROM T2))T3
where T1.ASNO = T2.ASNO AND
T1.ASLineNO = T2.ASLineNO AND
T1.Qty = T2.AQty AND T1.SNO <> T3.SNO
--CREATE TABLE T1 --表1
-- (
-- SNO INT ,
-- SLineNO INT,
-- Qty INT,
-- ASNO INT,
-- ASLineNO INT
--);
--INSERT INTO T1 VALUES
--(1001, 1, 10, 1009, 1),
--(1001, 2, 10, 1009, 2),
--(1002, 1, 10, 1009, 1),
--(1003, 1, 10, 2009, 1),
--(1003, 2, 10, 2009, 2),
--(1004, 1, 10, 2009, 2),
--(1005, 1, 10, 3009, 1),
--(1005, 2, 10, 4009, 1);
--CREATE TABLE T2--表2
-- ( ASNO INT,
-- ASLineNO INT,
-- AQty INT,
--);
--INSERT INTO T2 VALUES
--(1009, 1, 10),
--(1009, 2, 10),
--(2009, 1, 10000),
--(2009, 2, 20),
--(3009, 1, 10),
--(5009, 1, 30),
--(6009, 1, 10);
SELECT *
FROM
(SELECT SNO,SLINENO,SUM(QTY)QTY,ASNO,ASLINENO FROM t1
WHERE sno NOT IN (SELECT sno FROM t1 WHERE asno NOT IN(SELECT asno FROM t2))
GROUP BY SNO,SLINENO,ASNO,ASLINENO)A
INNER JOIN T2 B ON A.ASNO=B.ASNO AND A.SLINENO=B.ASLINENO
WHERE A.QTY=B.AQTY
/*
SNO SLINENO QTY ASNO ASLINENO ASNO ASLineNO AQty
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1001 1 10 1009 1 1009 1 10
1002 1 10 1009 1 1009 1 10
1001 2 10 1009 2 1009 2 10
*/
SELECT *
FROM
(SELECT SNO,SLINENO,SUM(QTY)QTY,ASNO,ASLINENO FROM t1
GROUP BY SNO,SLINENO,ASNO,ASLINENO)A
INNER JOIN T2 B ON A.ASNO=B.ASNO AND A.SLINENO=B.ASLINENO
WHERE A.QTY=B.AQTY
/*
SNO SLINENO QTY ASNO ASLINENO ASNO ASLineNO AQty
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1001 1 10 1009 1 1009 1 10
1001 2 10 1009 2 1009 2 10
1002 1 10 1009 1 1009 1 10
1005 1 10 3009 1 3009 1 10
*/