选择联接查询中不同行的总和

Question

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

表：订单

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

表：orderitem

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`

问题是，我最终得到的交货价格乘以订单项目的数量，因此我希望对订单进行求和（）<代码>交付在连接发生之前。

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