Shulou(Shulou.com)06/01 Report--

This article shares with you the content of a sample analysis of MySQL derived table concatenated table queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Previously on:

A mall system operated by the company suddenly found that there was something wrong with the order withdrawal function, and a large number of merchants reflected that the amount was inconsistent with the order amount. As a result, there is a demand, and we need to take the withdrawal table and the supplier table as a result set, connect the order amount in the order table, and compare the amount of the order table with the amount that the merchant withdraws. Check whether the merchant withdraws more or less.

Record my query process below.

Query process:

At the beginning, in the first step, I took the withdrawal table as the main table to query the relevant results. The MySQL statement is as follows

SELECT count (ysw.supply_id) AS 'withdrawal times', ysw.user_id AS 'supplier corresponding user ID', ysw.supply_id AS' supplier 'supplier ID', SUM (ysw.money) AS' supplier cash withdrawal amount', case ysw.pay_type when 10 then 'Wechat' when 20 then 'Alipay' else 'bank card' end as' payment method', ys.supply_name AS 'supplier name', ys.money AS 'supplier balance' Ys.freez_money AS 'supplier frozen amount (withdrew amount)' FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_idWHERE ysw.create_time

< 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_idORDER BY SUM(ysw.money) DESC ; 查询结果如图是正常的：

Next, I added a left join to the data of the order table on the left link. The amount-related data has changed seriously inconsistent, and the query time has been significantly prolonged. The MySQL statement is as follows

SELECT count (ysw.supply_id) AS 'withdrawal times', ysw.user_id AS 'supplier corresponding user ID', ysw.supply_id AS' supplier 'supplier ID', SUM (ysw.money) AS' supplier cash withdrawal amount', case ysw.pay_type when 10 then 'Wechat' when 20 then 'Alipay' else 'bank card' end as' payment method', ys.supply_name AS 'supplier name', ys.money AS 'supplier balance' Ys.freez_money AS 'supplier frozen amount (withdrew amount)', SUM (yo.pay_price) FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_idLEFT JOIN yoshop_order AS yo ON yo.supply_ids = ysw.supply_id WHERE ysw.create_time

< 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_idORDER BY SUM(ysw.money) DESC ; 查询结果对比图如下： 经过实践，我想直接通过左连接查询到提现表金额和订单表金额是行不通的。通过网上查资料，以及在技术群里请教， 优化了思路： 把提现的统计好,把订单的统计好， 最后两个结果集再根据供应商id做个链接 接下来就是，三步走了， 第一步：把提现的统计好，上面第一次尝试的第一步就是了， 第二步：把订单表的数据统计好。由于使用系统的原因，我直接使用的订单商品表计算的订单总金额，这一步也是分三步走的，我直接上代码： 1.查询yoshop_order所有进行中，已完成的 订单id(order_id); SELECT order_id FROM yoshop_order WHERE order_status IN (10,30); 2.查询没有退款的订单ID SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund); 3.查询订单商品表中 所有的订单金额SELECT supply_id AS '供应商ID' , SUM(total_pay_price) AS '供应商订单总金额' FROM yoshop_order_goods WHERE create_time < 1647446400 AND order_pay_status = 0 AND order_id IN(SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund) ) GROUP BY supply_id ORDER BY SUM(total_pay_price) DESC ; 接下来就是进行把第一步和第二步的查询结果当作派生表，进行左连接查询。我在这一步耗费的时间和精力最多。如果你能认真看完，相信一定会有收货。我在这里把我错误的过程也进行了记录 第一次错误拼接： SELECT * FROM ( SELECT count(ysw.supply_id) AS '提现次数',ysw.user_id AS '供应商对应的用户ID', ysw.supply_id AS 'supply_id' ,SUM(ysw.money) AS '供应商提现总金额', case ysw.pay_type when 10 then '微信' when 20 then '支付宝' else '银行卡' end as '支付方式' , ys.supply_name AS '供应商名称',ys.money AS '供应商余额',ys.freez_money AS '供应商冻结金额（已提现金额）' FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id ORDER BY SUM(ysw.money) DESC ) AS t1 union all // left join ,这里是注释记得删除SELECT * FROM -- 这里是错误的不应该在查询 (SELECT supply_id AS 'supply_id' , SUM(total_pay_price) AS total_pay_price FROM yoshop_order_goods WHERE create_time < 1647446400 AND order_pay_status = 0 AND order_id IN( SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund) ) GROUP BY supply_id ORDER BY SUM(total_pay_price) DESC ) AS t2 ON t1.suppply_id = t2.suppply_id 通过这一次试错，明显看出我把left join 和 union all 的含义记错了,并且在拼接的时候重复使用了select * from 。虽然是试错了，但也是有收货的，接下来进行了第二次错误的拼接： SELECT t1.提现次数 ,t1.供应商对应的用户ID ,t1.supply_id, t1.支付方式 ,t1.供应商名称,t1.供应商余额, t1.供应商冻结金额（已提现金额）, t2.total_pay_price FROM (SELECT count(ysw.supply_id) AS '提现次数',ysw.user_id AS '供应商对应的用户ID', ysw.supply_id AS supply_id ,SUM(ysw.money) AS '供应商提现总金额',case ysw.pay_type when 10 then '微信' when 20 then '支付宝' else '银行卡' end as '支付方式' , ys.supply_name AS '供应商名称',ys.money AS '供应商余额',ys.freez_money AS '供应商冻结金额（已提现金额）' FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id ORDER BY SUM(ysw.money) DESC ) AS t1 LEFT JOIN (SELECT supply_id AS supply_id , SUM(total_pay_price) AS total_pay_price FROM yoshop_order_goods WHERE create_time < 1647446400 AND order_pay_status = 0 AND order_id IN(SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN (SELECT order_id FROM yoshop_order_refund) ) GROUP BY supply_id ORDER BY SUM(total_pay_price) DESC ) AS t2 ON t1.suppply_id = t2.suppply_id 通过这两次错误的尝试，以及根据尝试过程中MySQL给出的错误提示，知道自己是在左连接上使用错误了，应该在开始查询出来所有的字段，left join 后不能在使用select * 最后，回想了一遍自己所学的left join的语法，写出了最后的正确的查询结果 SELECT t1.supply_id '供应商ID',t1.supply_name '供应商名称',t1.user_id '供应商绑定的用户ID',t1.withdrawtime '供应商提现次数' ,t1.supplyallmoney '供应商提现金额',t1.payway '供应商提现方式',t1.supply_money '供应商账户余额',t1.supply_free_money '供应商冻结余额（已提现金额）',t2.total_pay_price '供应商订单总金额',t2.order_id '供应商订单数量'FROM ( SELECT count(ysw.supply_id) AS withdrawtime, ysw.user_id AS user_id, ysw.supply_id AS supply_id , SUM(ysw.money) AS supplyallmoney, ysw.alipay_name AS alipay_name ,ysw.alipay_account AS alipay_account, ysw.audit_time as audit_time , ysw.bank_account AS bank_account, ysw.bank_card AS bank_card, ysw.bank_name AS bank_name,case ysw.pay_type when 10 then '微信' when 20 then '支付宝' else '银行卡' end as payway ,ys.supply_name AS supply_name, ys.money AS supply_money, ys.freez_money AS supply_free_moneyFROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_idWHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_idORDER BY SUM(ysw.money) DESC ) AS t1 LEFT JOIN (SELECT supply_id AS 'supply_id' , COUNT(order_id) AS order_id, SUM(total_pay_price) AS total_pay_price FROM yoshop_order_goods WHERE create_time < 1647446400 AND order_pay_status = 0 AND order_id IN( SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund) ) GROUP BY supply_id ORDER BY SUM(total_pay_price) DESC ) AS t2 ON t1.supply_id = t2.supply_id 正确的结果截图：

Thank you for reading! This is the end of this article on "sample analysis of MySQL derived table query". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.