MySQL - 在单个查询中分组，排序和计数 [英] MySQL - Group, Sort and Count in a single query

问题描述

我想尝试 GROUP ， SORT 和 COUNT



这里是我的 MySql 的简化。表：

  family sub_family name detailed_name 
农业谷物小麦每1 mt 
 Agro谷物玉米每1 mt 
农业谷物糖糖每1 mt 
农业水果苹果苹果红
农业水果苹果苹果绿
农业水果苹果苹果黄
农业水果柠檬柠檬经典
木树柠檬柠檬在原木
木树橡木在原木
木树Epicea在木材中的Epicea 
木包装Kraftliner Krafliner 3mm 
  

我想：

1. GROUP by'name'


2. SORT by'family'，sub_family'and lastly'name' / li>
   
3. 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 
  

谢谢。

解决方案

只是我猜想你要的是什么 http://sqlfiddle.com/#!9/e9206/16

因为它带来了所需的结果：

  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 :

1. GROUP by 'name'
2. SORT by 'family',sub_family' and lastly 'name'
3. COUNTthe 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 - 在单个查询中分组，排序和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！