mysql common table expression (cte) example
CTE is a temporary result set in MySQL to simplify complex queries. It can be referenced multiple times in the current query, improving code readability and maintenance. For example, when looking for the latest orders for each user in the orders table, you can first obtain the latest order date for each user through the CTE, and then associate it with the original table to obtain the complete record. Compared with subqueries, the CTE structure is clearer and the logic is easier to debug. Usage tips include explicit aliasing, concatenating multiple CTEs, and processing tree data with recursive CTEs. Mastering CTE can make SQL more elegant and efficient.
CTE (Common Table Expression) is a very practical query structure in MySQL, especially when handling complex queries, it can make the logic clearer and the code easier to read. The following is a simple but typical example to illustrate the usage of CTE.
What is CTE?
A CTE is a temporary result set that can be referenced multiple times in a query, and its life cycle only exists in the current query. Compared with subqueries, CTE is clearer and easier to debug.
For example, you have an order table orders that record the order amount and time of each user. If you want to find out the most recent order information of each user, you can write it with CTE.
Example: Use CTE to find the latest order for each user
Suppose we have an orders table with the fields as follows:
-
order_id -
user_id -
amount -
order_date
Now we need to find out the latest order record for each user.
WITH latest_orders AS (
SELECT
user_id,
MAX(order_date) AS latest_date
FROM orders
GROUP BY user_id
)
SELECT o.*
FROM orders o
JOIN latest_orders lo
ON o.user_id = lo.user_id AND o.order_date = lo.latest_date;This SQL does two things:
- First use CTE to find out the latest order date for each user.
- Then associate the original table with this CTE to find the complete order information.
The advantage of writing this is that it is clear logic, and if you need to reuse latest_orders later, just JOIN.
The difference between CTE and subquery
Although the above requirements can also be completed with subqueries, CTE is more readable. For example, if the above example is replaced with a nested subquery, it may look a bit confusing:
SELECT o.*
FROM orders o
WHERE (o.user_id, o.order_date) IN (
SELECT user_id, MAX(order_date)
FROM orders
GROUP BY user_id
);This writing method can also achieve the same effect, but when the logic is more complex, the structural advantages of CTE are reflected.
A few tips for using CTE
- The alias must be clear : the result column followed by the CTE can not specify an alias, but it is recommended to write it clearly, especially when doing complex calculations.
- Multiple CTEs can be concatenated : you can write multiple CTEs, separated by commas.
- Recursive CTE is also useful : for example, if you look up tree structure data (such as organizational structure, comment nesting, etc.), you can use
WITH RECURSIVE.
Take an example of multiple CTEs:
WITH cte1 AS (...),
cte2 AS (...)
SELECT ...
FROM cte1
JOIN cte2 ON ...;Basically that's it. CTE is not difficult, but it is particularly useful in actual development, especially in reports-based SQL or scenarios where multi-layer aggregation is required. Mastering it will make your SQL more elegant and easier to maintain.
The above is the detailed content of mysql common table expression (cte) example. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
How to add a primary key to an existing table in MySQL?
Aug 12, 2025 am 04:11 AM
To add a primary key to an existing table, use the ALTERTABLE statement with the ADDPRIMARYKEY clause. 1. Ensure that the target column has no NULL value, no duplication and is defined as NOTNULL; 2. The single-column primary key syntax is ALTERTABLE table name ADDPRIMARYKEY (column name); 3. The multi-column combination primary key syntax is ALTERTABLE table name ADDPRIMARYKEY (column 1, column 2); 4. If the column allows NULL, you must first execute MODIFY to set NOTNULL; 5. Each table can only have one primary key, and the old primary key must be deleted before adding; 6. If you need to increase it yourself, you can use MODIFY to set AUTO_INCREMENT. Ensure data before operation
Explain database indexing strategies (e.g., B-Tree, Full-text) for a MySQL-backed PHP application.
Aug 13, 2025 pm 02:57 PM
B-TreeindexesarebestformostPHPapplications,astheysupportequalityandrangequeries,sorting,andareidealforcolumnsusedinWHERE,JOIN,orORDERBYclauses;2.Full-Textindexesshouldbeusedfornaturallanguageorbooleansearchesontextfieldslikearticlesorproductdescripti
How to back up a database in MySQL
Aug 11, 2025 am 10:40 AM
Using mysqldump is the most common and effective way to back up MySQL databases. It can generate SQL scripts containing table structure and data. 1. The basic syntax is: mysqldump-u[user name]-p[database name]>backup_file.sql. After execution, enter the password to generate a backup file. 2. Back up multiple databases with --databases option: mysqldump-uroot-p--databasesdb1db2>multiple_dbs_backup.sql. 3. Back up all databases with --all-databases: mysqldump-uroot-p
How to change the GROUP_CONCAT separator in MySQL
Aug 22, 2025 am 10:58 AM
You can customize the separator by using the SEPARATOR keyword in the GROUP_CONCAT() function; 1. Use SEPARATOR to specify a custom separator, such as SEPARATOR'; 'The separator can be changed to a semicolon and plus space; 2. Common examples include using the pipe character '|', space'', line break character '\n' or custom string '->' as the separator; 3. Note that the separator must be a string literal or expression, and the result length is limited by the group_concat_max_len variable, which can be adjusted by SETSESSIONgroup_concat_max_len=10000; 4. SEPARATOR is optional
What is the difference between UNION and UNION ALL in MySQL?
Aug 14, 2025 pm 05:25 PM
UNIONremovesduplicateswhileUNIONALLkeepsallrowsincludingduplicates;1.UNIONperformsdeduplicationbysortingandcomparingrows,returningonlyuniqueresults,whichmakesitsloweronlargedatasets;2.UNIONALLincludeseveryrowfromeachquerywithoutcheckingforduplicates,
How to lock tables in MySQL
Aug 15, 2025 am 04:04 AM
The table can be locked manually using LOCKTABLES. The READ lock allows multiple sessions to read but cannot be written. The WRITE lock provides exclusive read and write permissions for the current session and other sessions cannot read and write. 2. The lock is only for the current connection. Execution of STARTTRANSACTION and other commands will implicitly release the lock. After locking, it can only access the locked table; 3. Only use it in specific scenarios such as MyISAM table maintenance and data backup. InnoDB should give priority to using transaction and row-level locks such as SELECT...FORUPDATE to avoid performance problems; 4. After the operation is completed, UNLOCKTABLES must be explicitly released, otherwise resource blockage may occur.
How to select data from a table in MySQL?
Aug 19, 2025 pm 01:47 PM
To select data from MySQL table, you should use SELECT statement, 1. Use SELECTcolumn1, column2FROMtable_name to obtain the specified column, or use SELECT* to obtain all columns; 2. Use WHERE clause to filter rows, such as SELECTname, ageFROMusersWHEREage>25; 3. Use ORDERBY to sort the results, such as ORDERBYageDESC, representing descending order of age; 4. Use LIMIT to limit the number of rows, such as LIMIT5 to return the first 5 rows, or use LIMIT10OFFSET20 to implement paging; 5. Use AND, OR and parentheses to combine
How to use the IN operator in MySQL?
Aug 12, 2025 pm 03:46 PM
TheINoperatorinMySQLchecksifavaluematchesanyinaspecifiedlist,simplifyingmultipleORconditions;itworkswithliterals,strings,dates,andsubqueries,improvesqueryreadability,performswellonindexedcolumns,supportsNOTIN(withcautionforNULLs),andcanbecombinedwith
