php - MySQL请求按公共(public)值排序

Question

我正面临一个小的 SQL 查询问题。

让我解释一下我会做什么。

我有一个成员资格表，我会根据两个成员之间共有的话题标签的数量进行排序。

这是为约会网站创建一个建议列表。

在我的第一个表“member”中，我使用“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)

因为 Laura 和 Jhon(我)有 2 个相同的标签，而 isabelle 和我有 1 个相同的标签......我想见 Melanie，即使她没有任何标签!

Answer 1

我想你一定需要一个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