表A
name age score
peter 26 100
jack 30 96
daniel 25 48
bark 21 69
jack 27 66
表B
name age score
peter 26 100
bark 24 69
jack 27 66
daniel 25 48
luke 22 75
（1）表B每行資料和表A對比，如果age沒有在表A出現，屬于新資料，則select出來
比如上邊的
bark 24 69
luke 22 75
（2）同時還要過濾掉上述結果中表B欄位name、score同時和表A欄位name、score相同的資料
過濾掉
bark 24 69
最后結果
luke 22 75
SQL陳述句應該怎么寫？謝謝
uj5u.com熱心網友回復：
SELECT temp1.*FROM (
SELECT A.name,A.age,A.score
FROM A LEFT
JOIN B ON A.name=B.name AND A.age=B.age
WHERE B.name IS NULL ) AS temp1
LEFT JOIN B AS temp2 ON temp1.name=temp2.name
WHERE temp2.score IS NOT NULL
uj5u.com熱心網友回復：
SELECT temp1.*FROM (
SELECT A.name,A.age,A.score
FROM A RIGHT
JOIN B ON A.name=B.name AND A.age=B.age
WHERE B.name IS NULL ) AS temp1
LEFT JOIN B AS temp2 ON temp1.name=temp2.name
WHERE temp2.score IS NOT NULL
上面寫錯了 這個對的 該的地方標紅了
uj5u.com熱心網友回復：
SELECT temp1.*FROM (
SELECT B.name,B.age,B.score
FROM A RIGHT
JOIN B ON A.name=B.name AND A.age=B.age
WHERE B.name IS NULL ) AS temp1
LEFT JOIN B AS temp2 ON temp1.name=temp2.name
WHERE temp2.score IS NOT NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115319.html
標籤：MySQL
上一篇：有大神嗎