php - 从多个表连接中选择具有计数的标签，每个 id 包含多个标签

Question

我的表格有 4 个标签链接到每个 ID。我想选择该值(tag_name 或 tag_id)的位置(等级)以及该值(tag_name 或 tag_id)在同一位置(等级)中显示的次数。

这是在 mysql 中的样子:

> +--------+------------+--------+------+> | id     | tag_name   | tag_id | rank |> +--------+------------+--------+------+> | 2345   | cookie     |    2   |   1  | > | 2345   | bar        |    1   |   2  |> | 2345   | cereal     |    3   |   3  |> | 2345   | milk       |    4   |   4  |> | 2346   | cereal     |    3   |   1  |> | 2346   | milk       |    4   |   2  |> | 2346   | cookie     |    2   |   3  |> | 2346   | hot dogs   |    5   |   4  |> | 2347   | chocolate  |    6   |   1  | > | 2347   | bar        |    1   |   2  |> +--------+------------+--------+------+

Here's what my current code looks like:

SELECT m.*, tr.tag_id, t.tag_name, @rownum:=@rownum + 1 AS rank 
FROM meals AS m
RIGHT JOIN tags_rel AS tr ON tr.meal_id = m.id 
JOIN tags AS t ON tr.tag_id = t.id
JOIN (SELECT @rownum:=0) AS r
ORDER BY m.id DESC

请记住，我实际上使用的是分页，所以我的 ORDER BY 实际上看起来像这样:

    ORDER BY id DESC LIMIT $start_from, 12

如果我选择 tag_name=bar 或 tag_id=1 和 rank=2 我应该得到下面的结果。另外，我想要为该值返回的行数。

> +--------+------------+--------+------+> | id     | tag_name   | tag_id | rank |> +--------+------------+--------+------+> | 2345   | bar        |    1   |   2  |> | 2347   | bar        |    1   |   2  |  > +--------+------------+--------+------+

谢谢!

Answer 1

  SELECT tag_name, tag_id, rank, COUNT(tag_id) AS rankcount 
    FROM table 
GROUP BY tag_id, rank;

结果:

+-----------+--------+------+-----------+
| tag_name  | tag_id | rank | rankcount |
+-----------+--------+------+-----------+
| bar       |      1 |    2 |         2 |
| cookie    |      2 |    1 |         1 |
| cookie    |      2 |    3 |         1 |
| cereal    |      3 |    1 |         1 |
| cereal    |      3 |    3 |         1 |
| milk      |      4 |    2 |         1 |
| milk      |      4 |    4 |         1 |
| hot dogs  |      5 |    4 |         1 |
| chocolate |      6 |    1 |         1 |
+-----------+--------+------+-----------+
9 rows in set (0.00 sec)

如果您希望通过行数来增强原始表(尽管这似乎毫无意义，因为每个 sql 结果都会返回行数)，您必须执行子查询:

SELECT id, tag_name, tag_id as t_id, rank, 
       (SELECT count(tag_id) FROM table WHERE tag_id = t_id) as subqueryCount
  FROM table;

+------+-----------+------+------+---------------+
| id   | tag_name  | t_id | rank | subqueryCount |
+------+-----------+------+------+---------------+
| 2345 | cookie    |    2 |    1 |             2 |
| 2345 | bar       |    1 |    2 |             2 |
| 2345 | cereal    |    3 |    3 |             2 |
| 2345 | milk      |    4 |    4 |             2 |
| 2346 | cereal    |    3 |    1 |             2 |
| 2346 | milk      |    4 |    2 |             2 |
| 2346 | cookie    |    2 |    3 |             2 |
| 2346 | hot dogs  |    5 |    4 |             1 |
| 2347 | chocolate |    6 |    1 |             1 |
| 2347 | bar       |    1 |    2 |             2 |
+------+-----------+------+------+---------------+
10 rows in set (0.00 sec)

扩展该查询会得到您想要的结果，并通过插入行数的列进行扩展(IMO 仍然相当无意义)。

SELECT id, tag_name, tag_id as t_id, rank, 
       (SELECT count(tag_id) FROM table WHERE tag_id = t_id) as subqueryCount
  FROM table
 WHERE tag_id = 1;

结果

+------+----------+------+------+---------------+
| id   | tag_name | t_id | rank | subqueryCount |
+------+----------+------+------+---------------+
| 2345 | bar      |    1 |    2 |             2 |
| 2347 | bar      |    1 |    2 |             2 |
+------+----------+------+------+---------------+
2 rows in set (0.00 sec)