时间戳

首页 > 解决方案 > 对多列进行组合和求和

mysql - 对多列进行组合和求和

问题描述

我有两个表，一个是具有 productid、productname、customerid1 和数量的产品，另一个是具有 customerid2、customername 和日期的客户表

我想要做的是在两个日期之间从客户表中收集所有 customerid2。然后通过连接将这些 id 与 products 表中的 customerid1 匹配。然后按名称对所有产品进行分组，求和它们的数量，然后按每个产品的名称对所有客户进行分组，并求和它们的总数量。

编辑：如有必要，我可以使用子查询或完全单独的查询。任何工作

**customers**
Column      ,   Column,    Column

customerid2:1,  name:jon,   date:2020
customerid2:2,  name:steve, date:2020
customerid2:3,  name:ted,   date:2020
customerid2:4,  name:ned,   date:2020


**products**
Column      , Column      ,     Column      ,  Column

productid:3 , productname:car,   customerid1:1,  qty,5
productid:3 , productname:car,   customerid1:2,  qty,5
productid:1 , productname:boat,  customerid1:3,  qty,1
productid:1 , productname:boat,  customerid1:4,  qty,2
productid:3 , productname:car,   customerid1:1,  qty,5

最终输出应该是：

 car total:15
 customer id:1   name:jon     qty:10
 customer id:2    name:steve   qty:5*

 boat total:3
 customer id:3   name:ted   qty:1
 customer id:4    name:ned    qty:2

我的代码：

$sql = SELECT customerid2, name, date, productname, SUM(qty)
       FROM customers
       JOIN ON customerid2 = customerid1
       WHERE date BETWEEN '2019' AND '2020'
       GROUP BY productid, customerid2
       ORDER BY qty

标签： mysqlsql

解决方案

Mysql 无法做出这样的输出，但在应用程序级别，您可以使用此查询来构建您需要的任何内容

这个查询：

SELECT 
  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
  ON p.productid = p1.productid 
WHERE `date` BETWEEN '2019' AND '2020'
GROUP by c.customerid2, c.name,p.productid,p.productname
ORDER by total DESC;

要获得你想要的 json 对象，你需要这个

SELECT
    json_object('product', CONCAT( total_sum,' ',productname), 'customer',json_result) json_result
FROM
    (SELECT  
        productname
        ,totaL_SUM
        ,JSON_ARRAYAGG(json_object('customerid', customerid2, 'name', name,'total', total)) json_result
    FROM
        (SELECT 
          c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
        FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
        inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
          ON p.productid = p1.productid 
        WHERE `date` BETWEEN '2019' AND '2020'
        GROUP by c.customerid2, c.name,p.productid,p.productname
        ORDER by total DESC) t2
    GROUP BY productname,total_sum) t3

CREATE TABLE products (
  `productid` INTEGER,
  `productname` VARCHAR(4),
  `customerid1` INTEGER,
  `qty` INTEGER
);

INSERT INTO products
  (`productid`, `productname`, `customerid1`, `qty`)
VALUES
  ('3', 'car', '1', '5'),
  ('3', 'car', '2', '5'),
  ('1', 'boat', '3', '1'),
  ('1', 'boat', '4', '2'),
  ('3', 'car', '1', '5');

✓

✓

CREATE TABLE customer (
  `customerid2` INTEGER,
  `name` VARCHAR(5),
  `date` INTEGER
);

INSERT INTO customer
  (`customerid2`, `name`, `date`)
VALUES
  ('1', 'jon', '2020'),
  ('2', 'steve', '2020'),
  ('3', 'ted', '2020'),
  ('4', 'ned', '2020');

✓

✓

SELECT 
  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
  ON p.productid = p1.productid 
WHERE `date` BETWEEN '2019' AND '2020'
GROUP by c.customerid2, c.name,p.productid,p.productname
ORDER by total DESC;

客户ID2 | 姓名 | 产品名称 | 总计| 总和
----------: | :---- | :------------ | ----: | --------:
          1 | 乔恩 | 汽车 | 10 | 15
          2 | 史蒂夫 | 汽车 | 5 | 15
          4 | 内德 | 船| 高分辨率照片| CLIPARTO 2 | 3
          3 | 泰德 | 船| 高分辨率照片| CLIPARTO 1 | 3

SELECT json_object('product', CONCAT( total_sum,' ',productname), 'customer', 
json_object('customerid', customerid2, 'name', name,'total', total)) json_result
FROM
(SELECT 
  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
  ON p.productid = p1.productid 
WHERE `date` BETWEEN '2019' AND '2020'
GROUP by c.customerid2, c.name,p.productid,p.productname
ORDER by total DESC) t2

| json_result |
| :------------------------------------------------ ------------------------------------------- |
| {"product": "15 car", "customer": {"name": "jon", "total": 10, "customerid": 1}} |
| {"product": "15 car", "customer": {"name": "steve", "total": 5, "customerid": 2}} |
| {“产品”：“3船”，“客户”：{“名称”：“ned”，“总计”：2，“客户ID”：4}} |
| {“产品”：“3船”，“客户”：{“名称”：“ted”，“总计”：1，“客户ID”：3}} |

SELECT
    json_object('product', CONCAT( total_sum,' ',productname), 'customer',json_result) json_result
FROM
  (SELECT  
      productname
      ,totaL_SUM
      ,JSON_ARRAYAGG(json_object('customerid', customerid2, 'name', name,'total', total)) json_result
  FROM
      (SELECT 
        c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
      FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
      inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
        ON p.productid = p1.productid 
      WHERE `date` BETWEEN '2019' AND '2020'
      GROUP by c.customerid2, c.name,p.productid,p.productname
      ORDER by total DESC) t2
  GROUP BY productname,total_sum) t3

| json_result |
| :------------------------------------------------ -------------------------------------------------- ------------------------------ |
| {“产品”：“3船”，“客户”：[{“名称”：“ned”，“总”：2，“客户ID”：4}，{“名称”：“ted”，“总”： 1，“客户ID”：3}]} |
| {"product": "15 car", "customer": [{"name": "jon", "total": 10, "customerid": 1}, {"name": "steve", "total": 5、“客户ID”：2}]} |

db<>在这里摆弄

推荐阅读