MySQL 和 GROUP BY

Question

我在 MySQL 数据库中有 3 个表:

项目

id | item_name
---+----------
 1 | item1
 2 | item2
 3 | item3
 4 | item4

标签

id | tag_name
---+---------
 1 | A
 2 | B
 3 | C

items_tags

id | items_id | tags_id
---+----------+-------
 1 |    1     |   1
 2 |    1     |   2
 3 |    1     |   3
 4 |    2     |   1
 5 |    2     |   3
 6 |    4     |   3

我可以使用此查询获取所有项目和标签:

SELECT items.item_name,
    GROUP_CONCAT(tags.tag_name) AS item_tags
FROM items 
    LEFT JOIN items_tags ON items.id=items_tags.items_id
    LEFT JOIN tags ON items_tags.tags_id=tags.id
GROUP BY items.id;

output:

item_name | item_tags
----------+----------
  item1   | A, B, C
  item2   | A, C
  item3   | NULL
  item4   | C

Q1:如何获取带有特定标签的所有项目

... where is A tag

output: 

item_name | item_tags
----------+----------
  item1   | A, B, C
  item2   | A, C

Q2:如何获取没有特定标签的所有项目

... where is not B

output:

item_name | item_tags
----------+----------
  item2   | A, C
  item3   | NULL
  item4   | C

第三季度:组合

... where is C and is not B 

output:

item_name | item_tags
----------+----------
  item2   | A, C
  item4   | C

Answer 1

您必须添加一个HAVING子句。

对于特定标签，例如与“C”:

SELECT items.item_name,
    GROUP_CONCAT(tags.tag_name) AS item_tags
FROM items 
    LEFT JOIN items_tags ON items.id=items_tags.items_id
    LEFT JOIN tags ON items_tags.tags_id=tags.id
GROUP BY items.id
HAVING COUNT(CASE WHEN tags.tag_name = 'C' THEN 1 END) > 0

输出:

item_name  item_tags
=====================
item1      A,B,C
item2      A,C
item4      C

对于没有，例如不“B”:

SELECT items.item_name,
    GROUP_CONCAT(tags.tag_name) AS item_tags
FROM items 
    LEFT JOIN items_tags ON items.id=items_tags.items_id
    LEFT JOIN tags ON items_tags.tags_id=tags.id
GROUP BY items.id
HAVING COUNT(CASE WHEN  tags.tag_name = 'B' THEN 1 END) = 0

输出:

item_name  item_tags
=====================
item2      A,C
item3      NULL
item4      C

和组合，例如不是 B 和“A”:

SELECT items.item_name,
    GROUP_CONCAT(tags.tag_name) AS item_tags
FROM items 
    LEFT JOIN items_tags ON items.id=items_tags.items_id
    LEFT JOIN tags ON items_tags.tags_id=tags.id
GROUP BY items.id
HAVING COUNT(CASE WHEN tags.tag_name = 'B' THEN 1 END) = 0 AND 
       COUNT(CASE WHEN tags.tag_name = 'A' THEN 1 END) > 0

输出:

item_name item_tags
===================
item2     C,A