在单个查询中进行分组，排序和计数

Question

我正在尝试GROUP，SORT并且COUNT在一个查询中，我的一个表名为“商品”。

这是我的MySql桌子的简化：

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

我想要 ：

1. GROUP 经过 name
2. SORT通过family，sub_family并最后name
3. COUNT每个的行数family，sub_family然后name（相同sub_family）

到目前为止，我设法做到了所有COUNT相同的事情sub_family。

确实，以下查询：

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

结果如下：

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

你可以看到，NbrName数 柠檬 2次，但我想它指望它只有1次，因为一个 柠檬 是 水果 sub_family和其他的 树
sub_family。

[更新]：这是我想要的结果：

   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

Answer 1

仅是我对您要求的http://sqlfiddle.com/#!9/e9206/16的猜测

因为它带来了预期的结果：

 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