我有一張桌子電影
select title,length from film where length = 117;
title | length
------------------------ --------
Chamber Italian | 117
Affair Prejudice | 117
Graffiti Love | 117
Magic Mallrats | 117
Resurrection Silverado | 117
我想做兩次自我加入表
SELECT
distinct(
f1.title,
f2.title,
f3.title),
f1.length
FROM
film f1
INNER JOIN film f2
ON f1.film_id <> f2.film_id AND
f1.length = f2.length
INNER JOIN film f3
ON f1.film_id <> f3.film_id AND f2.film_id <> f3.film_id AND
f2.length = f3.length
WHERE f1.title = 'Chamber Italian';
row | length
----------------------------------------------------------------- --------
("Chamber Italian","Affair Prejudice","Graffiti Love") | 117
("Chamber Italian","Affair Prejudice","Magic Mallrats") | 117
("Chamber Italian","Affair Prejudice","Resurrection Silverado") | 117
("Chamber Italian","Graffiti Love","Affair Prejudice") | 117
("Chamber Italian","Graffiti Love","Magic Mallrats") | 117
("Chamber Italian","Graffiti Love","Resurrection Silverado") | 117
("Chamber Italian","Magic Mallrats","Affair Prejudice") | 117
("Chamber Italian","Magic Mallrats","Graffiti Love") | 117
("Chamber Italian","Magic Mallrats","Resurrection Silverado") | 117
("Chamber Italian","Resurrection Silverado","Affair Prejudice") | 117
("Chamber Italian","Resurrection Silverado","Graffiti Love") | 117
("Chamber Italian","Resurrection Silverado","Magic Mallrats") | 117
但正如你所見
("Chamber Italian","Affair Prejudice","Graffiti Love")
("Chamber Italian","Graffiti Love","Affair Prejudice")
是一樣的只是安排是不同的如何消除這個
只是在我學習sql時問。
所以基本上我想要的輸出是
*
row | length
----------------------------------------------------------------- --------
("Chamber Italian","Affair Prejudice","Graffiti Love") | 117
("Chamber Italian","Affair Prejudice","Magic Mallrats") | 117
("Chamber Italian","Affair Prejudice","Resurrection Silverado") | 117
("Chamber Italian","Graffiti Love","Magic Mallrats") | 117
("Chamber Italian","Graffiti Love","Resurrection Silverado") | 117
("Chamber Italian","Magic Mallrats","Resurrection Silverado") | 117
我希望 (a,b,c) (a,c,b) 被視為相同并選擇其中任何一個
uj5u.com熱心網友回復：
放入任意平局斷路器
ON 1.film_id < f2.film AND ...
而不是你的對稱not<> 運算子，
為什么？
因為一切更大（或更小）
也不相等，但反過來會被跳過。
也簡化了第二次加入
...
FROM
film f1
INNER JOIN film f2
ON f1.film_id < f2.film_id AND
f1.length = f2.length
INNER JOIN film f3
ON f2.film_id < f3.film_id AND
f2.length = f3.length
因為 '<' 是可傳遞的，所以它隱含地涵蓋了這種
f1.film_id < f3.film_id
情況。
uj5u.com熱心網友回復：
你可以嘗試這樣的事情：
SELECT
distinct f1.title as title1,
(select title from film where title <> f1.title order by title limit 1) as title2,
(select title from film where title <> f1.title order by title desc limit 1) as title3,
f1.length
from film f1
where f1.title = 'Chamber Italian'
limit 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/406870.html
標籤：
上一篇：根據序列和模式對列進行分類