我有一个类似的表格
============
item | tag
-----+------
111 | amaretto
-----+------
111 | blueberry
-----+------
222 | amaretto
-----+------
333 | blueberry
-----+------
333 | chocolate
-----+------
444 | chocolate
-----+------
444 | amaretto
-----+------
555 | chocolate
-----+------
666 | blueberry
-----+------
666 | amaretto
-----+------
666 | chocolate
============
列CCD_ 1和CCD_。让我们想象一下,将商品作为冰淇淋,将标签作为可用的口味。例如,如果我搜索苦杏仁(在冰淇淋的情况下),我想显示苦杏仁冰淇淋可用的所有可能组合,如苦杏仁、苦杏仁+巧克力、蓝莓+苦杏仁等。如果我搜索标签amaretto
,我想展示所有可能的组合,如;
amaretto (222)
amaretto,blueberry (111)
amaretto,chocloate (444)
amaretto,blueberry,chocolate (666)
并且CCD_ 4和CCD_。
我该怎么做?我需要额外的桌子吗?我使用PHP+MySQL。
SELECT tt.item,
GROUP_CONCAT(DISTINCT tt.tag) FROM mytable tt
JOIN mytable ttt
ON tt.item=ttt.item
GROUP BY tt.item
HAVING SUM(tt.tag='yourtag')>0
一种解决方案是,如果GROUP_CONCT的结果是第一个排序的,则对其进行GROUP BY
SELECT MIN(item),tags FROM
(SELECT tt.item,
GROUP_CONCAT(DISTINCT tt.tag ORDER BY tt.tag) tags FROM mytable tt
JOIN mytable ttt
ON tt.item=ttt.item
GROUP BY tt.item
HAVING SUM(tt.tag='amaretto')>0)x
GROUP BY tags
SELECT GROUP_CONCAT(DISTINCT a.tag) tags
FROM my_table a
JOIN my_table b
ON b.item = a.item
WHERE b.tag = 'amaretto'
GROUP
BY a.item;
+------------------------------+
| tags |
+------------------------------+
| amaretto,blueberry |
| amaretto |
| amaretto,chocolate |
| amaretto,blueberry,chocolate |
+------------------------------+
4 rows in set (0.00 sec)
为了解决OP的修正问题,DISTINCT和GROUP BY在同一查询中的一个罕见实例…:
SELECT DISTINCT GROUP_CONCAT(a.tag ORDER BY a.tag) tags
FROM mytable a
JOIN mytable b
ON b.item = a.item
AND b.tag <= a.tag
WHERE b.tag = 'amaretto'
GROUP
BY a.item;
+-------------------------------------+
| tags |
+-------------------------------------+
| amaretto,blueberry |
| amaretto |
| amaretto,chocolate |
| amaretto,blueberry,chocolate |
| amaretto,blueberry,chocolate,pastry |
+-------------------------------------+
5 rows in set (0.01 sec)
请注意,多个相同的组合可能是缺乏正常化的症状。
您可能想要GROUP_CONCT,然后使用LIKE语句。
SELECT * FROM (
SELECT item, GROUP_CONCAT(DISTINCT tag) as TagCombinations
FROM tbl
GROUP BY item ) A
WHERE TagCombinations LIKE 'amaretto%'