我正在尝试在一个名为“商品”的表中的单个查询中使用 GROUPSORTCOUNT

这是我的 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

我想 :
  • GROUPname
  • SORTfamily , sub_family 和最后 name
  • COUNT 每个 familysub_familyname 的行数(相同的 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 将 Lemon 计算了 2 次,但我希望它只计算 1 次,因为一个柠檬在 Fruits sub_family 中,另一个在 Tree 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
    

    最佳答案

    只是我猜你要什么 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
    

    关于mysql - 在单个查询中分组、排序和计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29680707/

    10-16 13:43
    查看更多