问题描述
我想尝试 GROUP
, SORT
和 COUNT $ c $
这里是我的 MySql
的简化。表:
family sub_family name detailed_name
农业谷物小麦每1 mt
Agro谷物玉米每1 mt
农业谷物糖糖每1 mt
农业水果苹果苹果红
农业水果苹果苹果绿
农业水果苹果苹果黄
农业水果柠檬柠檬经典
木树柠檬柠檬在原木
木树橡木在原木
木树Epicea在木材中的Epicea
木包装Kraftliner Krafliner 3mm
我想:
-
GROUP
by'name' -
SORT
by'family',sub_family'and lastly'name' / li>
-
COUNT
每个「family」,「sub_family」和「name」的行数(IN THE SAME sub_family) li>
到目前为止,我在同一个sub_family中设法做了 COUNT
p>
确实,以下查询:
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 .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 .name
这将产生以下结果:
家庭NbrFamily sub_family NbrSubFamily名称NbrName
农业7谷物3小麦1
农业7谷物3玉米1
农业7谷物3糖1
农业7水果4苹果3
农业7水果4柠檬2
木4树3柠檬2
木4树3橡木1
木4树3 Epicea 1
Wood 4包装1 Kraftliner 1
你可以看到'NbrName'计数'柠檬'2次,我希望它只有1次,因为一个柠檬是在'水果'子系列和其他在'树'sub_family。
[更新]:这里是我的所需结果:
家庭NbrFamily sub_family NbrSubFamily名称NbrName
Agro 7谷物3小麦1
Agro 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
谢谢。
只是我猜想你要的是什么
因为它带来了所需的结果:
SELECT A.family,C.NbrFamily,A.sub_family,B.NbrSubFamily,A.name,COUNT名称)
从商品作为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
从商品
GROUP BY family
)C
ON A.family = C.family
GROUP BY A.family,A.sub_family,A.name
ORDER 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 3mm
I would like to :
GROUP
by 'name'SORT
by 'family',sub_family' and lastly 'name'COUNT
the numbers of rows for each 'family','sub_family' and then 'name' (IN THE SAME sub_family)
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.name
which 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 1
You 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
Thank you.
Just my guess of what you are asking for http://sqlfiddle.com/#!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
这篇关于MySQL - 在单个查询中分组,排序和计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!