mysql - 选择 MySQL 中具有该值的所有 ID

Question

我有这两个表:

表 1:

ID           ITEM
--------------------
1             AB
1            22S
1             AB
2            F45
2             BB
3             1
3             1
3             AA
3            F45
3            F67
3             A
......

表 2:

ITEM   COUNTRY
---------------
0       usa
1      italy
2      mexico
3      mexico
A      greece
AA     malta
AB      usa
AC    pakistan
B       uk 
BB    france
BA    france
BB    russia
F45     uk

我使用以下查询来获取每个 ID 类似于“A%”的项目:

SELECT GROUP_CONCAT(ITEM) 
FROM (SELECT Table1.ID, Table1.ITEM, Table1.date 
      FROM Table2 Table2 INNER JOIN Table1 Table1
      ON (Table2.ITEM = Table1.ITEM) WHERE table2.ITEM like "A%" 
      ORDER BY Table1.id, Table1.date, Table2.ITEM) as temp
GROUP BY ID
ORDER BY ID

我想找到一种方法来编辑我的查询，这样我就可以只为其中恰好包含项目“F45”的 ID 获取上述内容(但我不想使用此项目，只需选择包含该项目的 ID)有它)..任何帮助将不胜感激

Answer 1

我猜

SELECT GROUP_CONCAT(ITEM) AS group_item
FROM (SELECT Table1.ID, Table1.ITEM, Table1.date 
      FROM Table2 Table2 INNER JOIN Table1 Table1
      ON (Table2.ITEM = Table1.ITEM) WHERE table2.ITEM like "A%" 
      ORDER BY Table1.id, Table1.date, Table2.ITEM) as temp
GROUP BY ID
HAVING group_item LIKE '%F45%'
ORDER BY ID

或者调整子查询以仅选择需要的行(这里我不确定你想要实现什么)