search

Home >Database >Mysql Tutorial >Example analysis of MySQL derived table join table query

Example analysis of MySQL derived table join table query

PHPz
PHPzforward
2023-04-17 18:40:061253browse

Preliminary summary:

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.

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:

Example analysis of MySQL derived table join table query

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 &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS &#39;供应商ID&#39; ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;,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:

Example analysis of MySQL derived table join table query

## After In practice, I want to directly query the withdrawal table amount and order table amount through left connection, which is not feasible. By checking information online and asking for advice in the technical group,

optimized the idea: make good statistics on cash withdrawals, good statistics on orders, and make a link based on the supplier ID for the last two result sets

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 &#39;供应商ID&#39; , SUM(total_pay_price)  AS &#39;供应商订单总金额&#39; 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 &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS &#39;supply_id&#39; ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
	case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
	ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;
	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 &#39;supply_id&#39; , 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

Through 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 &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS supply_id ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
	ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;
	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

Through 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 &#39;供应商ID&#39;,t1.supply_name &#39;供应商名称&#39;,t1.user_id &#39;供应商绑定的用户ID&#39;,t1.withdrawtime &#39;供应商提现次数&#39; ,t1.supplyallmoney &#39;供应商提现金额&#39;,t1.payway &#39;供应商提现方式&#39;,t1.supply_money &#39;供应商账户余额&#39;,t1.supply_free_money &#39;供应商冻结余额（已提现金额）&#39;,
t2.total_pay_price &#39;供应商订单总金额&#39;,t2.order_id &#39;供应商订单数量&#39;
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 &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; 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 &#39;supply_id&#39; , 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

The correct result screenshot:

Example analysis of MySQL derived table join table query

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!

mysql select union
Statement：
This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete
Previous article：How to create high-performance indexes for MySQLNext article：How to create high-performance indexes for MySQL

Related articles

See more