22,301
社区成员




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
*/