mysql - 在连接查询中选择 DISTINCT 行的 SUM

Question

我有两个表，订单和订单项。我正在按日期制作销售报告，其中需要包括每个订单商品在每个日期的总成本价格和销售价格以及每个订单在每个日期的总交付成本:

表:顺序

orderid    date          deliverycost
1          2000-01-01    5
2          2000-01-01    3
3          2000-01-02    0
4          2000-01-02    4

表:订单项

orderitemdid    orderid    costprice    sellingprice
1               1          10           12
2               1          2            4
3               2          10           12
4               3          5            6
5               3          4            10
6               4          1            2

我正在寻找将提供以下输出的查询:

date          costprice   sellingprice   deliverycost
2000-01-01    22          28             8    
2000-01-02    10          18             4

我尝试了以下 SQL:

        SELECT
            SUM(`orderitem`.`costprice`) AS `costprice`,
            SUM(`orderitem`.`sellingprice`) AS `sellingprice`,
            SUM(`order`.`delivery`) AS `delivery`,
            DATE(`order`.`date`) AS `date`,
        FROM
            `order`
        INNER JOIN
            `orderitem`
        ON
            (`order`.`orderid` = `orderitem`.`orderid`)
        GROUP BY
            `date`
        ORDER BY
            `date`

问题是我最终得到的是交货价格乘以订单商品的数量，所以我希望对订单执行 SUM()。交货 在连接发生之前。

Answer 1

可能与引号使用不当有关不要对列名使用单引号，但在需要时使用反引号

   SELECT
        SUM(`orderitem`.`costprice`) as costprice,
        SUM(`orderitem`.`sellingprice`) as sellingprice,
        SUM(`order`.`delivery`) as delivery,
        DATE(`order`.`confirmdate`) as `date`,
    FROM  `order`
    INNER JOIN  `orderitem`    ON `order`.`orderid` = `orderitem`.`orderid`
    GROUP BY  `date`
    ORDER BY `date`

在你的情况下也应该避免倒退，因为你没有单独的列名和保留字

   SELECT
        SUM(orderitem.costprice) as costprice,
        SUM(orderitem.sellingprice) as sellingprice,
        SUM(order.delivery) as delivery,
        DATE(order.confirmdate) as date,
    FROM  order
    INNER JOIN  orderitem    ON order.orderid = orderitem.orderid
    GROUP BY  date
    ORDER BY date

并且为了避免笛卡尔积，您可以加入聚合表

   SELECT
        SUM(t.costprice) as costprice,
        SUM(t.sellingprice) as sellingprice,
        SUM(order.delivery) as delivery,
        DATE(order.confirmdate) as date,
    FROM  order
    INNER JOIN  (
    SELECT 
        orderitem.orderid
        SUM(orderitem.costprice) as costprice,
        SUM(orderitem.sellingprice) as sellingprice
        from orderitem
        group by  orderitem.orderid
      ) t ON order.orderid = t.orderid
    GROUP BY  date
    ORDER BY date