First

測試資料

DB: MySQL

Auther: Frank Hsieh

SQL UNION

MySQL UNION運算符允許您將兩個或多個查詢結果集組合為一個結果集。

UNION [DISTINCT | ALL]

SELECT id FROM t1
UNION
SELECT id FROM t2;
id
1
2
3
4
SELECT 
    firstName, 
    lastName
FROM
    employees 
UNION 
SELECT 
    contactFirstName, 
    contactLastName
FROM
    customers;
firstName lastName
Diane Murphy
Mary Patterson
Jeff Firrelli
William Patterson
Gerard Bondur
Anthony Bow
Leslie Jennings
Leslie Thompson
Julie Firrelli
Steve Patterson

UNION vs JOIN

SQL UNION ALL

SELECT id FROM t1
UNION ALL
SELECT id FROM t2;
id
1
2
3
2
3
4

SQL INTERSECT

該INTERSECT運算符是一個集合運算符,它僅返回兩個查詢或更多查詢的不同行。

(SELECT column_list 
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);

不幸的是,MySQL不支持該INTERSECT運算符。但是,您可以模擬INTERSECT 運算符。

模擬INTERSECT使用DISTINCT和INNER JOIN子句

SELECT DISTINCT 
id 
FROM t1 
INNER JOIN t2 USING(id);

模擬INTERSECT使用IN和子查詢

SELECT /*DISTINCT*/ id
FROM t1
WHERE id IN (SELECT id FROM t2);

SQL MINUS

該MINUS比較將比較兩個查詢的結果,並從第一個查詢的結果集中返回不出現在第二個查詢的結果集中的不同行。

SELECT id FROM t1
MINUS
SELECT id FROM t2;

不幸的是,MySQL不支持MINUS運算符。但是,您可以使用join進行仿真。

模擬INTERSECT使用LEFT JOIN

SELECT 
    id
FROM
    t1
LEFT JOIN
    t2 USING (id)
WHERE
    t2.id IS NULL;

SQL 子查詢

一個MySQL子查詢是嵌套在另一個查詢內,諸如查詢 SELECT,INSERT,或 。另外,一個子查詢可以嵌套在另一個子查詢中。UPDATE DELETE

MySQL子查詢稱為內部查詢,而包含子查詢的查詢稱為外部查詢。子查詢可以在使用表達式的任何地方使用,並且必須在括號中關閉。

以下查詢返回在美國辦公室工作的員工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

以下查詢返回付款最高的客戶。

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);
customerNumber checkNumber amount
141 JE105477 120166.58

使用子查詢找到付款額大於平均付款額的客戶

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);
customerNumber checkNumber amount
112 HQ55022 32641.98
112 ND748579 33347.88
114 GG31455 45864.03
114 MA765515 82261.22
114 NR27552 44894.74
119 LN373447 47924.19
119 NG94694 49523.67
121 DB889831 50218.95

獨立子查詢

以下子查詢查找銷售訂單中項目的最大數量,最小數量和平均數量

SELECT 
    MAX(items), 
    MIN(items), 
    FLOOR(AVG(items))
FROM
    (SELECT 
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;
MAX(items) MIN(items) FLOOR(AVG(items))
18 1 9

關聯子查詢

我們選擇購買價格大於每個 產品系列中所有產品的平均購買價格的產品

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)
productname buyprice
1952 Alpine Renault 1300 98.58
1996 Moto Guzzi 1100i 68.99
2003 Harley-Davidson Eagle Drag Bike 91.02
1972 Alfa Romeo GTA 85.68
1962 LanciaA Delta 16V 103.42
1968 Ford Mustang 95.34
2001 Ferrari Enzo 95.59

SQL EXISTS

查找具有至少一個訂單的客戶

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS(
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber 
		= customers.customernumber);

查找沒有訂單的客戶

SELECT 
  customerNumber, 
  customerName
FROM
  customers
WHERE
  NOT EXISTS( 
    SELECT 
      1
    FROM
      orders
    WHERE 
      orders.customernumber = customers.customernumber
  );

SQL CASE

根據客戶訂購的訂單數返回客戶類型

WITH cte AS (
	SELECT 
		customerName, 
		COUNT(*) orderCount
	FROM
		orders
	INNER JOIN customers 
		USING (customerNumber)
	GROUP BY customerName
)
SELECT 
    customerName, 
    orderCount,
    CASE orderCount
		WHEN 1 THEN 'One-time Customer'
        WHEN 2 THEN 'Repeated Customer'
        WHEN 3 THEN 'Frequent Customer'
        ELSE 'Loyal Customer'
	end customerType
FROM
    cte
ORDER BY customerName;

使用CASE表達式在地區不為NULL的情況下按地區對客戶進行排序,或在地區為NULL的情況下對國家/地區進行排序

SELECT 
    customerName, 
    state, 
    country
FROM
    customers
ORDER BY (
    CASE
	WHEN state IS NULL 
            THEN country
	ELSE state
END);

將CASE表達式與SUM()一起使用,以按訂單狀態計算銷售訂單總數

SELECT 
    SUM(CASE
        WHEN status = 'Shipped' THEN 1
        ELSE 0
    END) AS 'Shipped',
    SUM(CASE
        WHEN status = 'On Hold' THEN 1
        ELSE 0
    END) AS 'On Hold',
    SUM(CASE
        WHEN status = 'In Process' THEN 1
        ELSE 0
    END) AS 'In Process',
    SUM(CASE
        WHEN status = 'Resolved' THEN 1
        ELSE 0
    END) AS 'Resolved',
    SUM(CASE
        WHEN status = 'Cancelled' THEN 1
        ELSE 0
    END) AS 'Cancelled',
    SUM(CASE
        WHEN status = 'Disputed' THEN 1
        ELSE 0
    END) AS 'Disputed',
    COUNT(*) AS Total
FROM
    orders;

SQL 算排名

根據客戶訂購的訂單總金額返回客戶排名

SET @row_number = 0;
SELECT *, (@row_number:=@row_number + 1) AS num
FROM
    (SELECT 
        `c`.`customerNumber`,
        `c`.`customerName`,
        SUM(`p`.`amount`) AS `total_amount`
    FROM `customers` AS `c`
        LEFT JOIN `payments` AS `p`
            ON `c`.`customerNumber` = `p`.`customerNumber`
    WHERE `p`.`amount` IS NOT NULL
    GROUP By `c`.`customerNumber`
    ORDER BY `total_amount` DESC) AS `RS`

SQL 算中位數

根據客戶訂購的訂單總金額返回客戶排名中位數

SET @row_number = 0;
WITH `RS` AS (
	SELECT `t`.*, (@row_number:=@row_number + 1) AS `num`
	FROM ( 
        SELECT `customerNumber`,SUM(`amount`) AS `total_amount`
        FROM `payments`
        WHERE `amount` IS NOT NULL
        GROUP By `customerNumber`
        ORDER BY `total_amount` DESC
	) AS `t`
)SELECT *
FROM `RS`
WHERE `num` = (SELECT (COUNT(`num`)+1) DIV 2 FROM `RS`)

SQL 算總合百分比

根據客戶訂購的訂單總金額返回客戶消費佔總金額百分比

SET @row_number = 0;
WITH `RS` AS (
    SELECT `T`.*, (@row_number:=@row_number + 1) AS `num`
    FROM
        (SELECT 
            `c`.`customerNumber`,
            `c`.`customerName`,
            SUM(`p`.`amount`) AS `total_amount`
        FROM `customers` AS `c`
            LEFT JOIN `payments` AS `p`
                ON `c`.`customerNumber` = `p`.`customerNumber`
        WHERE `p`.`amount` IS NOT NULL
        GROUP By `c`.`customerNumber`
        ORDER BY `total_amount` DESC) AS `T`
)SELECT *, `total_amount`/(SELECT SUM(`total_amount`) FROM `RS`) AS `Pct_To_Total`
FROM `RS`
WHERE 1

SQL - FUNCTION

DROP FUNCTION IF EXISTS GG;
CREATE FUNCTION GG (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT(s,' GG !');
SELECT GG('Jimpop') ;
DROP FUNCTION GG;