Example analysis of MySQL derived table join table query-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Example analysis of MySQL derived table join table query

PHPz

Apr 17, 2023 pm 06:40 PM

mysql

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 &#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;
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!

Statement

This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles