时间戳

首页 > 解决方案 > 如何显示每位客户所有订单的总成本？（子查询）

mysql - 如何显示每位客户所有订单的总成本？（子查询）

问题描述

我想显示他们订单的总成本，并显示他们购买的每件商品。当我按订单号排序时，总和（价格）就搞砸了。当我按客户名称排序时，它只显示一种产品。我如何显示他们购买的所有产品以及这些产品的总成本。*每个订单都附有一件产品。具有唯一的订单号

这些是我创建的虚构表格，与我自己的非常相似。

Customer Table
+------+-------------+
| name | customer_id |
+------+-------------+
| Adam |           4 |
| Drew |           6 |
| Tom  |           8 |
+------+-------------+

Order Table
+---------------+---------------+----------+--------------+
| purchase_date | delivery_date | order_id | customer_id  |
+---------------+---------------+----------+--------------+
| 01/22/2020    | 02/23/2020    |        3 |             4|
| 01/12/2020    | 02/12/2020    |        5 |             4|
| 01/31/2020    | 02/22/2020    |        6 |             4|
| 01/05/2020    | 02/14/2020    |       11 |             8|
| 01/22/2020    | 02/23/2020    |        7 |             8|
| 01/12/2020    | 02/12/2020    |        8 |             6|
| 01/31/2020    | 02/22/2020    |        9 |             6|
| 01/05/2020    | 02/14/2020    |       10 |             6|
| 01/05/2020    | 02/14/2020    |       10 |             6|
+---------------+---------------+----------+--------------+

Product_order table
+-------+------------+----------+
| po_id | product_id | order_id |
+-------+------------+----------+
|     1 |          3 |         3|
|     2 |         13 |         5|
|     3 |          7 |         6|
|     4 |          8 |        11|
|     5 |         45 |         7|
|     6 |          9 |         8|
|     7 |          3 |         9|
|     8 |         45 |        10|
|     9 |          3 |        10|
+-------+------------+----------+

product table
+------------+------------+-------+
| product_id |  product   | Price |
+------------+------------+-------+
|          3 | Soda       | 7.00  |
|         13 | Chips      | 9.00  |
|          7 | GummyBears | 11.00 |
|          8 | IceCream   | 3.00  |
|          9 | Pen        | 2.00  |
|         45 | Gum        | 1.00  |
+------------+------------+-------+

这是我尝试过的查询，但产生了以下结果。

select c.name, product, sum(p.price) as total_sale
from (Select distinct order_id, customer_id
    from `order`) as o
inner join (select o.customer_id
    from `order` o
    group by o.customer_id) as a on o.customer_id = a.customer_id
inner join product_order po on o.order_id = po.order_id
inner join customer c on o.customer_id = c.customer_id
inner join products p on po.product_id = p.product_id
group by c.name

这是结果：

+----------+------------+-----------------+
|   Name   |  product   | total_purchased |
+----------+------------+-----------------+
| Adam     | Soda       |            27.00|
| Tom      | IceCream   |             4.00|
| Drew     | Pen        |            17.00|
+----------+------------+-----------------+

我想展示他们购买的所有产品。他们购买所有物品的总金额，就像这样。

+------+------------+-----------------+
| Name |  product   | total_purchased |
+------+------------+-----------------+
| Adam | Soda       |            27.00|
| Adam | Chips      |            27.00|
| Adam | GummyBears |            27.00|
| Tom  | IceCream   |             4.00|
| Tom  | Gum        |             4.00|
| Drew | Pen        |            17.00|
| Drew | Soda       |            17.00|
| Drew | Gum        |            17.00|
| Drew | Soda       |            17.00|
+------+------------+-----------------+

标签： mysql

解决方案

数据不符合想要的结果，但这只是一个小问题

order是mysql中的保留字，请尽量不要使用

您示例中的日期也不是 mysql 标准，所以我不得不重写它们

正如我所说，您必须先计算总和，然后再将其加入 colplte 查询

CREATE TABLE Customer  (
  `name` VARCHAR(4),
  `customer_id` INTEGER
);

INSERT INTO Customer 
  (`name`, `customer_id`)
VALUES
  ('Adam', '4'),
  ('Drew', '6'),
  ('Tom', '8');

CREATE TABLE `Order`  (
  `purchase_date` DATETIME,
  `delivery_date` DATETIME,
  `order_id` INTEGER,
  `customer_id` INTEGER
);

INSERT INTO `Order`
  (`purchase_date`, `delivery_date`, `order_id`, `customer_id`)
VALUES
  ('2020-01-22', '2020-02-23', '3', '4'),
  ('2020-01-12', '2020-02-12', '5', '6'),
  ('2020-01-31', '2020-02-22', '6', '6'),
  ('2020-01-05', '2020-02-14', '11', '8');

CREATE TABLE Product_order (
  `po_id` INTEGER,
  `product_id` INTEGER,
  `order_id` INTEGER
);

INSERT INTO Product_order
  (`po_id`, `product_id`, `order_id`)
VALUES
  ('1', '3', '3'),
  ('2', '13', '5'),
  ('3', '45', '6'),
  ('4', '7', '11');

CREATE TABLE products (
  `product_id` INTEGER,
  `product` VARCHAR(10),
  `price` DECIMAL(5,2)
);

INSERT INTO products
  (`product_id`, `product`, `price`)
VALUES
  ('3', 'Soda', '3.99'),
  ('13', 'Chips', '7.99'),
  ('45', 'Gum', '8'),
  ('7', 'GummyBears', '12');

select o.customer_id, sum(p.price) as total_sale
from (Select distinct order_id, customer_id
    from `Order`) as o
inner join Product_order po on o.order_id = po.order_id
inner join products p on po.product_id = p.product_id
group by o.customer_id

客户 ID | 总销售额
----------: | ---------:
          4 | 3.99
          6 | 15.99
          8 | 12.00

SELECT 
    c.name, p.product, pr.total_sale
FROM
    Customer c
        INNER JOIN
    `Order` o ON c.customer_id = o.customer_id
        INNER JOIN
    Product_order po ON o.order_id = po.order_id
        INNER JOIN
    products p ON po.product_id = p.product_id
        INNER JOIN
    (SELECT 
        o.customer_id, SUM(p.price) AS total_sale
    FROM
        (SELECT DISTINCT
        order_id, customer_id
    FROM
        `Order`) AS o
    INNER JOIN Product_order po ON o.order_id = po.order_id
    INNER JOIN products p ON po.product_id = p.product_id
    GROUP BY o.customer_id) pr ON c.customer_id = pr.customer_id

姓名 | 产品 | 总销售额
:--- | :--------- | ---------:
亚当 | 苏打水 | 3.99
德鲁 | 芯片 | 15.99
德鲁 | 口香糖 | 15.99
汤姆 | 小熊软糖 | 12.00

db<>在这里摆弄

推荐阅读