我面临一个小的SQL查询问题。
让我解释一下我会怎么做。
我有一个成员表，我将按两个成员之间的共同标签数进行排序。
这是为约会网站创建一个建议列表。
在我的第一个表“成员”中，我使用“idmem”
在第二个“hashtags”中，我有以下列：“idhash”、“idmem”、“hashtags”
目前我正在提出这个要求。
SELECT m.*,
nbhash.nb
FROM member AS m
INNER JOIN
(SELECT count(*) AS nb,
mh.idmem
FROM hashtags AS h
INNER JOIN hashtags AS mh
ON h.hashtags = mh.hashtags
WHERE h.idmem = '3016') nbhash
WHERE m.idmem != '3016'
AND m.sexe = 'female'
AND m.orientsexe = 'male'
AND nbhash.idmem = m.idmem
member table
idmem | pseudo | sexe | orientsexe |
3016 | jhon | male | female |
3017 | laura | female | male |
3018 | david | male | male |
3019 | jessica | female | male |
3020 | clara | female | female |
3021 | isabelle | female | male |
3022 | melanie | female | male |
3023 | fred | male | female |
hashtags table
idhash | idmem | hashtags
1 | 3016 | basketball
2 | 3016 | cinema
3 | 3017 | basketball
4 | 3017 | cinema
5 | 3019 | basketball
6 | 3020 | cinema
7 | 3021 | basketball
8 | 3021 | football我想按这个顺序看：
劳拉（3017），伊莎贝尔（3021），杰西卡（3019），梅兰妮（3022）
因为劳拉和约翰（我）有两个相同的标签，伊莎贝尔和我有一个相同的标签。。。。。。
即使梅兰妮没有标签我也想见她！
最佳答案
我想你需要一个order by和left join：
SELECT m.*, coalesce(nbhash.nb, 0)
FROM member m LEFT JOIN
(SELECT count(*) AS nb, mh.idmem
FROM hashtags h INNER JOIN
hashtags mh
ON h.hashtags = mh.hashtags
WHERE h.idmem = '3016'
) nbhash
ON nbhash.idmem = m.idmem
WHERE m.idmem <> '3016' AND m.sexe = 'female' AND m.orientsexe = 'male'
ORDER BY coalesce(nbhash.nb, 0) DESC