输出MySQL的记录列表，按类别分组？ [英] Output MySQL list of records, grouped by category?

问题描述

我有类似这样的数据： 产品

$ b

 
  b芥末热芥末
芥末辣根芥末
芥末蜂蜜芥末
酱汁番茄酱
酱汁烧烤酱
 

我想得到这个输出：

 
  Mustards  
热芥末
辣根芥末
蜂蜜芥末
 
 调料番茄酱
烧烤酱
 

我想我可以用GROUP BY和/或DISTINCT来完成它，就像这样：

 
 SELECT DISTINCT类别，prodName 
 FROM recipes 
 GROUP BY类别
 ORDER BY类别，prodName ASC 
 

但我没有太多的运气。任何人都可以提供帮助吗？

谢谢 - Joe 解决方案

试试这个：

  SELECT类别，GROUP_CONCAT（ProdName）作为产品
来自食谱
 GROUP BY类别
 ORDER BY类别，ProdName ASC 
  

输出：

 
类别产品
 ---------------------------- ------------------------------ 
 Mustards热芥末，辣根芥末，蜂蜜芥末
酱汁番茄酱，烧烤酱
 

查看这个SQLFiddle

I have data like this:

Category     Product
Mustards     Hot Mustard
Mustards     Horseradish Mustard
Mustards     Honey Mustard
Sauces       Tomato Sauce
Sauces       Barbeque Sauce

And I'd like to get this output:

Mustards
Hot Mustard
Horseradish Mustard
Honey Mustard

Sauces
Tomato Sauce
Barbeque Sauce

I thought I could do it with GROUP BY and/or DISTINCT, like this:

    SELECT DISTINCT category, prodName
    FROM recipes
    GROUP BY category
    ORDER BY category, prodName ASC

but I'm not having much luck. Can anyone help?

Thanks - Joe

解决方案

Try this:

SELECT category, GROUP_CONCAT(ProdName) as Product
FROM recipes
GROUP BY category
ORDER BY category, ProdName ASC

Output:

CATEGORY     PRODUCT
----------------------------------------------------------
Mustards     Hot Mustard,Horseradish Mustard,Honey Mustard
Sauces       Tomato Sauce,Barbeque Sauce

See this SQLFiddle

这篇关于输出MySQL的记录列表，按类别分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！