SELECT A.A1,A.A2,B.B2,B.B3 FROM T1 A INNER JOIN T2 B ON A.A1=B.B1 AND A.A2 >= B. B2 AND NOT EXISTS ( SELECT 1 FROM T2 WHERE B1 = B.B1 AND B2 > B.B2 )
语句有点区别,原因是鸟书中的最大有歧义你的语句等同于SELECT A.A1,A.A2,B.B2,B.B3 FROM T1 A INNER JOIN T2 B ON A.A1=B.B1 AND A.A2 >= B.B2 AND NOT EXISTS ( SELECT 1 FROM T2 WHERE B1 = B.B1 AND A.A2 >= B2 AND B2 > B.B2 )
CREATE TABLE t1 ( a1 INT, a2 INT, a3 INT ) CREATE TABLE t2 ( b1 INT, b2 INT, b3 INT ) INSERT INTO t1 SELECT 1,3,1 UNION SELECT 2,3,1 UNION SELECT 3,1,1INSERT INTO t2 SELECT 1,2,3 UNION SELECT 1,1,4 UNION SELECT 3,1,1SELECT * FROM t1,t2 WHERE a1 = b1 AND a2 >= b2 AND b2 >= ALL(SELECT b2 FROM t2 WHERE a1 = b1 AND a2 >= b2)
FROM T1 A
INNER JOIN T2 B
ON A.A1=B.B1
AND A.A2 >= B. B2
AND NOT EXISTS (
SELECT 1 FROM T2
WHERE B1 = B.B1
AND B2 > B.B2
)
FROM T1 A
INNER JOIN T2 B
ON A.A1=B.B1
AND A.A2 >= B.B2
AND NOT EXISTS (
SELECT 1 FROM T2
WHERE B1 = B.B1
AND A.A2 >= B2
AND B2 > B.B2
)
CREATE TABLE t1
( a1 INT,
a2 INT,
a3 INT
)
CREATE TABLE t2
( b1 INT,
b2 INT,
b3 INT
)
INSERT INTO t1
SELECT 1,3,1 UNION
SELECT 2,3,1 UNION
SELECT 3,1,1INSERT INTO t2
SELECT 1,2,3 UNION
SELECT 1,1,4 UNION
SELECT 3,1,1SELECT *
FROM t1,t2
WHERE a1 = b1 AND a2 >= b2 AND b2 >= ALL(SELECT b2 FROM t2 WHERE a1 = b1 AND a2 >= b2)