如何在SQL中实现set类行为？

Question

假设我有dogs(dogid)表和fleas(fleaid)表，还有一个dogs_favorite_fleas(dogid,fleaid)，狗可以在dog_favorite_fleas中列出他最喜欢的跳蚤。如何创建查询/临时表，列出所有唯一的跳蚤集并对它们进行计数？例如，假设Rex喜欢flea a和flea b，Spot喜欢‘and跳蚤’, and, andLassielikesandflea a‘。查询的结果应该是：

fleas          |count
-------------------------
flea a, flea b |2
flea a, flea c |1

备注：大约有5000只不同的跳蚤和大约450万只狗。

做这个查询的最好方法是什么？

Answer 1

将GROUP_CONCAT与GROUP BY结合使用

SELECT GROUP_CONCAT(f.flea SEPARATOR ', ') AS 'fleas', COUNT(df.dogid) 'Count'
FROM dogs_favorite_fleas df 
INNER JOIN fleas f ON df.fleaid = f.id
INNER JOIN dogs d ON df.dogid = d.id
GROUP BY df.dogid

SQL Fiddle演示

Update:如果您想要计算有多少个分组跳蚤列表，请将前面的查询包含在子查询中，并将GROUP BY与COUNT一起使用，如下所示：

SELECT fleas, COUNT(fleas) 'COUNT'
FROM
(
   SELECT GROUP_CONCAT(f.flea SEPARATOR ', ') AS 'fleas'
   FROM dogs_favorite_fleas df 
   INNER JOIN fleas f ON df.fleaid = f.id
   INNER JOIN dogs d ON df.dogid = d.id
   GROUP BY df.dogid
   ORDER BY f.flea
) t
GROUP BY fleas

更新的SQL演示