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

问题描述

我试图 GROUP ， SORT 和 COUNT 在我的一个名为'commodities'的表中查询一个。

以下简化了我的 MySql 表：

 家庭sub_family name详细名称
农业谷物小麦小麦1 mt 
农业谷物玉米玉米1公吨
农业谷物糖每1公吨
农业水果苹果苹果红
农业水果苹果苹果绿
农业水果苹果苹果黄
农业水果柠檬柠檬经典
木材树柠檬柠檬原木
原木中的木树橡木橡木原木
原木中的木材树Epicea Epicea 
木材包装Kraftliner Krafliner 3mm 
  

我想：

1. GROUP by name


2. SORT by 家庭， sub_family 和最后名称


3. COUNT 每个家庭， sub_family 然后 name （同样地 sub_family ）

ol>



到目前为止，我在同一个 sub_family COUNT



确实如下查询：



  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.com/#！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 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

解决方案

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

这篇关于在一个查询中分组，排序和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！