Home >Database >Mysql Tutorial >Example analysis of MySQL derived table join table query
A mall system operated by the company suddenly discovered that there was a problem with the order withdrawal function, and a large number of merchants reported that the amount was inconsistent with the order amount. So a demand arose. It was necessary to use the withdrawal table and the supplier table as a result set, connect the order amount in the order table, and compare the amount in the order table with the amount withdrawn by the merchant in the reflection table to check whether the merchant has withdrawn more money. Still less withdrawals.
The following records my query process.
At the beginning, in the first step, I used the withdrawal table as the main table to query the relevant results. The MySQL statement is as follows
SELECT count(ysw.supply_id) AS '提现次数',ysw.user_id AS '供应商对应的用户ID', ysw.supply_id AS '供应商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 ;
The query result is normal as shown:
Next, I added another order table data on the left link left join, the amount-related data has changed seriously and inconsistently, and the query time has been significantly extended. The MySQL statement is as follows
SELECT count(ysw.supply_id) AS '提现次数',ysw.user_id AS '供应商对应的用户ID', ysw.supply_id AS '供应商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 '供应商冻结金额（已提现金额）',SUM(yo.pay_price) FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id LEFT 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_id ORDER BY SUM(ysw.money) DESC ;
The query result comparison chart is as follows:
The next step is to go through three steps. The first step: Collect the statistics of withdrawals. The first step in the first attempt above is the first step. The second step: Collect the data of the order table. Due to the use of the system, I directly use the order product table to calculate the total order amount. This step is also divided into three steps. I directly enter the code:
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 ;The next step is to combine the first step and the second step. The query result of the second step is used as a derived table for left join query. This is the step where I spend the most time and energy. If you can read it carefully, I believe you will receive it. I also recorded my erroneous process here. The first wrong splicing:
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_idThrough this trial and error, it is obvious that I misremembered the meaning of left join and union all, and when splicing select * from is used repeatedly. Although it was a trial and error, the goods were received, and then the second wrong splicing was performed:
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_idThrough these two wrong attempts, and based on the error prompts given by MySQL during the attempt , knowing that I made a mistake in using the left join, I should query all the fields at the beginning, and cannot use select * after left join. Finally, recalled the syntax of left join that I had learned, and wrote the final The correct query result
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_money
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' , 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_idThe correct result screenshot:
The above is the detailed content of Example analysis of MySQL derived table join table query. For more information, please follow other related articles on the PHP Chinese website!