本文介绍了在一个查询中分组，排序和计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
GROUP by name SORT by 家庭， sub_family 和最后名称 COUNT 每个家庭， sub_family 然后 name （同样地 sub_family ） ol> GROUP by name
SORT by family, sub_family and lastly name
COUNTthe numbers of rows for each family, sub_family and then name (IN THE SAME sub_family)
我试图 GROUP ， SORT 和 COUNT 在我的一个名为'commodities'的表中查询一个。
以下简化了我的 MySql 表：
家庭sub_family name详细名称农业谷物小麦小麦1 mt 农业谷物玉米玉米1公吨农业谷物糖每1公吨农业水果苹果苹果红农业水果苹果苹果绿农业水果苹果苹果黄农业水果柠檬柠檬经典木材树柠檬柠檬原木原木中的木树橡木橡木原木原木中的木材树Epicea Epicea 木材包装Kraftliner Krafliner 3mm我想：
到目前为止，我在同一个 sub_family 确实如下查询：
SELECT TableC.family， TableC.NbrFamily， TableB.sub_family， TableB.NbrSubFamily， TableA.name， TableA.NbrName FROM （ SELECT b $ b family， sub_family， name， COUNT（DISTINCT commodities.id）AS NbrName FROM commodities GROUP BY名称）TableA INNER JOIN （ SELECT sub_family， COUNT（DISTINCT commodities.id）AS NbrSubFamily FROM commodities GROUP BY sub_family ）Tab leB ON（TableA.sub_family = TableB.sub_family） INNER JOIN （ SELECT family， COUNT（DISTINCT commodities.id）AS NbrFamily FROM商品GROUP BY系列）TableC ON（TableA.family = TableC.family） GROUP BY TableA.name ORDER BY TableA.family，TableA。 sub_family，TableA.name其结果如下：
family NbrFamily sub_family NbrSubFamily name NbrName Agro 7谷物3小麦1 农业7谷物3玉米1 农业7谷物3糖1 Agro 7水果4苹果3 农业7水果4柠檬2 木材4树3柠檬2 木材4树3橡木1 木材4 Tree 3 Epicea 1 Wood 4 Packaging 1 Kraftliner 1您可以看到 NbrName counts柠檬 2次，但我希望它只计算一次，因为一个柠檬位于 Fruits sub_family ，另一个在树中 sub_family 。
[UPDATE]：这里是我想要的结果：
family NbrFamily sub_family NbrSubFamily name NbrName 农业7谷物3小麦1 农业7谷物3玉米1 农业7谷物3糖1 农业7水果4苹果3 农业7水果4柠檬1 木材4树3柠檬1 木材4树3橡树1 树木4树3 Epicea 1 木材4包装1 Kraftliner 1解决方案
我只是猜测您要求 http ：//sqlfiddle/#！9 / e9206 / 16
因为它带来了理想的效果：
SELECT A.family，C.NbrFamily，A.sub_family，B.NbrSubFamily，A.name，COUNT（A.Name） FROM商品为A LEFT JOIN（ SELECT family，sub_family，COUNT（Name）AS NbrSubFamily FROM commodities GROUP BY系列，sub_family ）B ON A.sub_family = B.sub_family AND A.family = B.family LEFT JOIN（ SELECT family，COUNT（Name）AS NbrFamily FROM commodities GROUP BY系列）C ON A.family = C.family GROUP BY A.family，A.sub_family，A.name O RDER BY A.id
I'm trying to GROUP, SORT and COUNT in a single query one of my table named 'commodities'.
Here is a simplification of my MySql table :
family sub_family name detailed_name Agro Grains Wheat Wheat per 1 mt Agro Grains Corn Corn per 1 mt Agro Grains Sugar Sugar per 1 mt Agro Fruits Apple Apple red Agro Fruits Apple Apple green Agro Fruits Apple Apple yellow Agro Fruits Lemon Lemon classic Wood Tree Lemon Lemon in logs Wood Tree Oak Oak in logs Wood Tree Epicea Epicea in logs Wood Packaging Kraftliner Krafliner 3mmI would like to :
So far I managed to do everything but COUNT in the same sub_family.
Indeed, the following query :
SELECT TableC.family, TableC.NbrFamily, TableB.sub_family, TableB.NbrSubFamily, TableA.name, TableA.NbrName FROM ( SELECT family, sub_family, name, COUNT(DISTINCT commodities.id) AS NbrName FROM commodities GROUP BY name ) TableA INNER JOIN ( SELECT sub_family, COUNT(DISTINCT commodities.id) AS NbrSubFamily FROM commodities GROUP BY sub_family ) TableB ON (TableA.sub_family = TableB.sub_family) INNER JOIN ( SELECT family, COUNT(DISTINCT commodities.id) AS NbrFamily FROM commodities GROUP BY family ) TableC ON (TableA.family = TableC.family) GROUP BY TableA.name ORDER BY TableA.family,TableA.sub_family,TableA.namewhich results in the following :
family NbrFamily sub_family NbrSubFamily name NbrName Agro 7 Grains 3 Wheat 1 Agro 7 Grains 3 Corn 1 Agro 7 Grains 3 Sugar 1 Agro 7 Fruits 4 Apple 3 Agro 7 Fruits 4 Lemon 2 Wood 4 Tree 3 Lemon 2 Wood 4 Tree 3 Oak 1 Wood 4 Tree 3 Epicea 1 Wood 4 Packaging 1 Kraftliner 1You can see that NbrName counts Lemon 2 times but I would like it to count it only 1 time because one lemon is in Fruits sub_family and the other in Tree sub_family.
[UPDATE] : Here are my desired results :
family NbrFamily sub_family NbrSubFamily name NbrName Agro 7 Grains 3 Wheat 1 Agro 7 Grains 3 Corn 1 Agro 7 Grains 3 Sugar 1 Agro 7 Fruits 4 Apple 3 Agro 7 Fruits 4 Lemon 1 Wood 4 Tree 3 Lemon 1 Wood 4 Tree 3 Oak 1 Wood 4 Tree 3 Epicea 1 Wood 4 Packaging 1 Kraftliner 1解决方案
Just my guess of what you are asking for sqlfiddle/#!9/e9206/16
because it brings desired result:
SELECT A.family, C.NbrFamily,A.sub_family,B.NbrSubFamily,A.name,COUNT(A.Name) FROM commodities as A LEFT JOIN ( SELECT family,sub_family,COUNT(Name) AS NbrSubFamily FROM commodities GROUP BY family,sub_family ) B ON A.sub_family = B.sub_family AND A.family = B.family LEFT JOIN ( SELECT family,COUNT(Name) AS NbrFamily FROM commodities GROUP BY family ) C ON A.family = C.family GROUP BY A.family,A.sub_family,A.name ORDER BY A.id
更多推荐
在一个查询中分组,排序和计数
发布评论