我有三个表：Dolls，Dresses和Who wears what
娃娃桌：
Names ID
连衣裙表：
Descriptions ID
谁戴什么桌子：
Doll_ID Dresses_ID
我如何找出两个娃娃穿着相同的衣服。我只想要唯一的一对。我管理的最好的方法是这样做，但是它没有唯一的对：
SELECT
"Dolls"."Name", "Dolls2"."Name", "Dresses"."Description" AS "Dresses"
FROM
"Dolls", "Dolls" AS "Dolls2", "Who wears what" AS "WWW",
"Who wears what" AS "WWW2", "Dresses"
WHERE
"WWW"."Panenka" = "Dolls"."ID"
AND "WWW2"."Panenka" = "Dolls2"."ID"
AND "Dresses"."ID" = "WWW"."Dresses"
AND "Dresses"."ID" = "WWW2"."Dresses"
AND "Dolls"."ID" != "Dolls2"."ID"
样本数据：
玩偶：
1, Vera
2, Mary
3, Viki
连衣裙：
1, Short
2, Skimpy
3, See-through
WWW（娃娃，连衣裙）：
1, 1
2, 1
3, 3
所需的输出：
Vera, Mary, Short
一种可能性是从得到WWW由多于一个娃娃穿着的衣服，拿到相关的Doll_ID（MIN和MAX，因为只有2需要）和JOIN那些ID回娃娃表
SELECT d1.Names Doll_1, d2.Names Doll_2, Dress.Descriptions
FROM Dolls d1
INNER JOIN (SELECT max(Doll_ID) D1ID, min(Doll_ID) d2ID, D.Descriptions
FROM WWW W
INNER JOIN Dresses D ON W.Dresses_ID = D.ID
GROUP BY D.Descriptions
HAVING COUNT(DISTINCT Doll_ID) > 1) Dress
ON d1.ID = Dress.d1ID
INNER JOIN Dolls d2 ON d2.ID = Dress.d2ID
这两个玩偶势必会有所不同，因为如果存在多个不同ID MIN且MAX不能相同的值
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句