对多列进行组合和求和 [英] Gouping and suming multiple columns
本文介绍了对多列进行组合和求和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表，一个是 products ，其中包含 productid、productname、customerid1 和数量和另一个 customers 表，其中包含 customerid2、customername 和日期
我想要做的是在两个日期之间从客户表中收集所有 customerid2.然后通过连接将这些 id 与产品表中的 customerid1 匹配.然后按名称将所有产品分组，总和它们的数量，然后按每个产品的名称对所有客户进行分组，并汇总他们的总数量.
如有必要，我可以使用子查询或完全单独查询.什么都行
**客户**列，列，列customerid2:1，姓名:jon，日期:2020customerid2:2，姓名:史蒂夫，日期:2020customerid2:3，姓名:ted，日期:2020customerid2:4，姓名:ned，日期:2020**产品**列，列，列，列productid:3 , productname:car, customerid1:1, qty,5productid:3 , productname:car, customerid1:2, qty,5productid:1 , productname:boat, customerid1:3, qty,1productid:1 , productname:boat, customerid1:4, qty,2productid:3 , productname:car, customerid1:1, qty,5最终输出应该是:
汽车总数:15客户 ID:1 姓名:乔恩 数量:10客户 ID:2 姓名:史蒂夫 数量:5*船总数:3客户 id:3 姓名:ted 数量:1客户 ID:4 姓名:已编 数量:2我的代码:
$sql = SELECT customerid2, name, date, productname, SUM(qty)来自客户加入 customerid2 = customerid1WHERE 日期介于 '2019' 和 '2020' 之间GROUP BY productid, customerid2按数量订购 解决方案
Mysql 不能做出这样的输出，但是在应用程序级别你可以使用这个查询，来构建你所需要的
这个查询:
SELECTc.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sumFROM 客户 c 内部 JOIN 产品 p ON c.customerid2 = p.customerid1内部连接(SELECT productid，SUM(qty)total_sum FROM products GROUP BY productid)p1ON p.productid = p1.productid'2019' 和 '2020' 之间的日期"在哪里按 c.customerid2、c.name、p.productid、p.productname 分组按总 DESC 排序；要获得你想要的 json 对象，你需要这个
SELECTjson_object('product', CONCAT(total_sum,',productname), 'customer',json_result) json_result从(选择产品名称，总和,JSON_ARRAYAGG(json_object('customerid', customerid2, 'name', name,'total', total)) json_result从(选择c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sumFROM 客户 c 内部 JOIN 产品 p ON c.customerid2 = p.customerid1内部连接(SELECT productid，SUM(qty)total_sum FROM products GROUP BY productid)p1ON p.productid = p1.productid'2019' 和 '2020' 之间的日期"在哪里按 c.customerid2、c.name、p.productid、p.productname 分组按总 DESC 排序) t2GROUP BY productname,total_sum) t3<块引用>
创建表格产品(`productid` 整数，`产品名称` VARCHAR(4),`customerid1` 整数，`数量` 整数);插入产品(`productid`、`productname`、`customerid1`、`qty`)价值观('3', '汽车', '1', '5'),('3', '汽车', '2', '5'),('1', '船', '3', '1'),('1', '船', '4', '2'),('3', '汽车', '1', '5');<前>✓✓
<块引用>
创建表客户(`customerid2` 整数，`name` VARCHAR(5),`日期` 整数);插入客户(`customerid2`, `name`, `date`)价值观('1', '乔恩', '2020'),('2', '史蒂夫', '2020'),('3', 'ted', '2020'),('4', 'ned', '2020');<前>✓✓
<块引用>
SELECTc.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sumFROM 客户 c 内部 JOIN 产品 p ON c.customerid2 = p.customerid1内部连接(SELECT productid，SUM(qty)total_sum FROM products GROUP BY productid)p1ON p.productid = p1.productid'2019' 和 '2020' 之间的日期"在哪里按 c.customerid2、c.name、p.productid、p.productname 分组按总 DESC 排序；<前>客户 ID2 |姓名 |产品名称 |总计 |总和----------: |:---- |:---------- |----: |--------:1 |乔恩 |汽车 |10 |152 |史蒂夫|汽车 |5 |154 |奈|船|2 |33 |泰德 |船|1 |3
<块引用>
SELECT json_object('product', CONCAT( total_sum,' ',productname), 'customer',json_object('customerid', customerid2, 'name', name,'total', total)) json_result从(选择c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sumFROM 客户 c 内部 JOIN 产品 p ON c.customerid2 = p.customerid1内部连接(SELECT productid，SUM(qty)total_sum FROM products GROUP BY productid)p1ON p.productid = p1.productid'2019' 和 '2020' 之间的日期"在哪里按 c.customerid2、c.name、p.productid、p.productname 分组按总 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 艘船"，客户":{名称":内德"，总计":2，客户 ID":4}} ||{产品":3 艘船"，客户":{名称":ted"，总计":1，客户 ID":3}} |
<块引用>
SELECTjson_object('product', CONCAT(total_sum,',productname), 'customer',json_result) json_result从(选择产品名称，总和,JSON_ARRAYAGG(json_object('customerid', customerid2, 'name', name,'total', total)) json_result从(选择c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sumFROM 客户 c 内部 JOIN 产品 p ON c.customerid2 = p.customerid1内部连接(SELECT productid，SUM(qty)total_sum FROM products GROUP BY productid)p1ON p.productid = p1.productid'2019' 和 '2020' 之间的日期"在哪里按 c.customerid2、c.name、p.productid、p.productname 分组按总 DESC 排序) t2GROUP BY productname,total_sum) t3<前>|json_result ||:--------------------------------------------------------------------------------------------------------------------------------- ||{产品":3 艘船"，客户":[{名称":ned"，总计":2，客户 ID":4}，{名称":"ted", "total": 1, "customerid": 3}]} ||{"product": "15 car", "customer": [{"name": "jon", "total": 10, "customerid": 1}, {"name":史蒂夫"，总计":5，客户 ID":2}]} |
db<>fiddle 这里
I have a two tables one is products that has productid, productname, customerid1 and quantity and another customers table that has customerid2,customername and dates
what i want to do is collect all the customerid2 from customers table between two dates. then match those ids to customerid1 in the products table with a join. then group all the products by name sum their quantity, then group all the customers by name for each product and sum their total quantity.
edit: I could use a subquery if necessary or seperate queries entirely. whatever works
**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
the final output should be:
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
my code:
$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
解决方案
Mysql can't make such an output, but on application level you can use this query, to buld what ever you need
This query:
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;
To get a json object as you wanted you need this
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;
customerid2 | name | productname | total | total_sum ----------: | :---- | :---------- | ----: | --------: 1 | jon | car | 10 | 15 2 | steve | car | 5 | 15 4 | ned | boat | 2 | 3 3 | ted | boat | 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}} | | {"product": "3 boat", "customer": {"name": "ned", "total": 2, "customerid": 4}} | | {"product": "3 boat", "customer": {"name": "ted", "total": 1, "customerid": 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 | | :--------------------------------------------------------------------------------------------------------------------------------- | | {"product": "3 boat", "customer": [{"name": "ned", "total": 2, "customerid": 4}, {"name": "ted", "total": 1, "customerid": 3}]} | | {"product": "15 car", "customer": [{"name": "jon", "total": 10, "customerid": 1}, {"name": "steve", "total": 5, "customerid": 2}]} |
db<>fiddle here
这篇关于对多列进行组合和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
