Instantly share code, notes, and snippets.
CreatedDecember 12, 2023 08:11
Save sqlparser/2aa0a44c97b4e9da6e4d8927524a8875 to your computer and use it in GitHub Desktop.
MySQL Sample SQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.Learn more about bidirectional Unicode characters
| ```sql | |
| -- mysql sample sql | |
| SELECT | |
| salesperson.name, | |
| -- find maximum sale size for this salesperson | |
| (SELECT MAX(amount) AS amount | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id) | |
| AS amount, | |
| -- find customer for this maximum size | |
| (SELECT customer_name | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id | |
| AND all_sales.amount = | |
| -- find maximum size, again | |
| (SELECT MAX(amount) AS amount | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id)) | |
| AS customer_name | |
| FROM | |
| salesperson; | |
| SELECT | |
| salesperson.name, | |
| max_sale.amount, | |
| max_sale_customer.customer_name | |
| FROM | |
| salesperson, | |
| -- calculate maximum size, cache it in transient derived table max_sale | |
| (SELECT MAX(amount) AS amount | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id) | |
| AS max_sale, | |
| -- find customer, reusing cached maximum size | |
| (SELECT customer_name | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id | |
| AND all_sales.amount = | |
| -- the cached maximum size | |
| max_sale.amount) | |
| AS max_sale_customer; | |
| SELECT | |
| salesperson.name, | |
| max_sale.amount, | |
| max_sale.customer_name | |
| FROM | |
| salesperson, | |
| -- find maximum size and customer at same time | |
| LATERAL | |
| (SELECT amount, customer_name | |
| FROM all_sales | |
| WHERE all_sales.salesperson_id = salesperson.id | |
| ORDER BY amount DESC LIMIT 1) | |
| AS max_sale; | |
| WITH RECURSIVE employee_paths (id, name, path) AS | |
| ( | |
| SELECT id, name, CAST(id AS CHAR(200)) | |
| FROM employees | |
| WHERE manager_id IS NULL | |
| UNION ALL | |
| SELECT e.id, e.name, CONCAT(ep.path, ',', e.id) | |
| FROM employee_paths AS ep JOIN employees AS e | |
| ON ep.id = e.manager_id | |
| ) | |
| SELECT * FROM employee_paths ORDER BY path; | |
| UPDATE table1 t1 | |
| JOIN table2 t2 ON t1.field1 = t2.field1 | |
| JOIN table3 t3 ON (t3.field1=t2.field2 AND t3.field3 IS NOT NULL) | |
| SET t1.field9=t3.field9 | |
| WHERE t1.field5=1 | |
| AND t1.field9 IS NULL | |
| ``` |
Sign up for freeto join this conversation on GitHub. Already have an account?Sign in to comment