mysql - 按条件子查询的结果分组

Question

我正在标准化一些表，但遇到了无法解决的问题。

旧的非规范化查询如下所示:

SELECT
      SUM(allorderlines.COUNT),
      products.NAME,
      products.VOLUME,
      people.NAME,
      people.ID,
      layout.POSTYPE,
      IF( allorders.TOTAL = allorders.DISCOUNT, 1, 0 ) AS ISFREE
   FROM
      allorderlines
         INNER JOIN allorders
            ON allorderlines.ORDERID = allorders.ORDERID
            INNER JOIN people
               ON allorders.EMPLOYEE = people.ID
            INNER JOIN layout
               ON allorders.POSID = layout.ID
         INNER JOIN products
            ON allorderlines.PRODUCT = products.ID 
   WHERE
      allorderlines.PRODUCT = ?
   GROUP BY
      people.ID,
      layout.POSTYPE,
      ISFREE
   ORDER BY
      layout.POSTYPE ASC,
      people.NAME,
      ISFREE ASC,
      products.NAME

这意味着我可以将结果分组为订单是否免费(即免费)。

在我的规范化版本中，我没有 allorders 表中可用的订单总和(allorders.TOTAL 和 allorders.DISCOUNT)。要确定订单是否免费，我需要 SUM(allorderlines.LINEPRICE) 并查看它是否等于 0。产品的行不会是 0，而是另一行折扣值为如果订单总共是免费的，则订单将存在并且等于产品的总和:例如:

#Order 23123
 Line 1: 2000
 Line 2: 1000
 Line 3 (discount): -3000

Free: true

#Order 23124
 Line 1: 2000
 Line 2: 1000
 Line 3 (discount): -2000

Free: false

我不知道如何做到这一点并保留上述逻辑。我最接近的尝试是将 ISFREE 行替换为以下内容:

IF(SUM(allorderlines.LINEPRICE) = 0,1,0) AS ISFREE

但这给了我一个“cannot group by IFREE”错误，大概是因为我试图对在这种情况下不可用的东西进行分组。

一个明显的解决方案是将 allorders.LINEPRICE 的总和保存在 allorders 表中，但这将是多余的，我正在尝试放弃由此而来。

请注意，DISCOUNT 值以前为正值，而作为订单行，它现在为负值。因此 (allorders.TOTAL = allorders.DISCOUNT) = FREE。

编辑:为了澄清，结果集应该类似于:

people.ID     people.NAME    SUM(COUNT)      POSTYPE    ISFREE
3             A name         4               1          1
3             A name         24              1          0
3             A name         2               2          1
3             A name         7               2          0
6             Another name   2               1          1
6             Another name   93              1          0
6             Another name   6               2          1
6             Another name   193             2          0

每个人可以有 0 到 4 行，0 表示给定产品的任何 POSTYPE 都没有以任何价格出售，4 表示两种 POSTYPE 至少有 1 件免费且 1 件已售出(截至撰写本文时，只有2种)。

Answer 1

首先，让我们这样做。您似乎正在寻找包含特定产品的任何订单。执行预查询以获取该产品的所有不同订单。然后加入按订单分组的订单行，以检测空闲的总净订单。

最后，重新加入订单行和其余表格以获取您要查找的额外内容，但按人员分组、布局是自由标志，但按产品名称排序意味着显示所有产品订单的名称，但您的 where 子句仅查找一种产品。

首先，首先是预审查询，并使用别名与长表名。

SELECT
      QualByProd.OrderID,
      SUM( AOL2.LinePrice ) as TotalOrder,
      COUNT(*) as LineItemCount
   FROM
      ( SELECT distinct AOL.OrderID
           from AllOrderLines AOL
           where AOL.Product = ? ) QualByProd
         JOIN AllOrderLines AOL2
            ON QualByProd.OrderID = AOL2.OrderID2
   GROUP BY
      QualByProd.OrderID

这将为您提供每个订单:1) 具有相关产品 ID，2) 整个订单中所有订单项的摘要，无论免费状态、谁甚至产品名称如何。但此时，每个合格订单只有一条记录和总计(包括与合格产品关联的订单中的总体行项目的计数)。现在，我们可以将整个查询作为您原始查询的基础，例如...

SELECT
      PR.NAME ProductName,
      PR.VOLUME ProductVolume,
      P.NAME PersonName,
      P.ID PersonID,
      L.POSTYPE,
      IF( OkRecs.TotalOrder, 1, 0 ) AS ISFREE,
      OkRecs.LineItemCount
   FROM
      ( entire first query above by product ) as OkRecs
         JOIN AllOrderLines AOL3
            ON OkRecs.OrderID = AOL3.OrderID
         JOIN AllOrders AO
            ON OkRecs.OrderID = AO.OrderID
            INNER JOIN people P
               ON AO.EMPLOYEE = P.ID
            INNER JOIN layout L
               ON AO.POSID = L.ID
         INNER JOIN products PR
            ON AOL3.Product = PR.ID 
   WHERE
      AOL3.PRODUCT = ?
   ORDER BY
      L.POSTYPE,
      P.NAME,
      IF( OkRecs.TotalOrder, 1, 0 )
      PR.NAME

现在，您的分组表示聚合，但我认为您不希望这样。你可以让一个人有两个不同的订单... 1 个免费，5 个付费，总共 6 个订单。由于没有聚合，您可以直接订购。最后是免费项目。如果您想首先查看所有免费订单，那么在所有 POSType、人员和产品中，只需将 IF() 移至第一个位置即可。