SQL进阶（上）

CASE表达式

新手用WHERE字句进行条件分支，高手用SELECT字句进行条件分支

--男性人口
SELECT pref_name,
        SUM(population)
FROM PopTbl2
WHERE sex='1'
GROUP BY pref_name
--女性人口
SELECT pref_name
        SUM(population)
FROM PopTbl2
WHERE sex='2'
GROUP BY pref_name

SELECT pref_name,
        --男性人口
        SUM(CASE WHEN sex='1' THEN population ELSE 0 END) AS cnt_m,
        --女性人口
        SUM(CASE WHEN sex='2' THEN population ELSE 0 END)AS cnt_f,
FROM PopTbl2
GROUP BY pref_name;

新手用HAVING子句进行条件分支，高手用SELECT子句进行条件分支

--选择只加入一个社团的学生

SELECT std_id,MAX(club_id)AS main_club
FROM    StudentClub
GROUP BY std_id
HAVING COUNT(*)=1;

--选择加入了多个社团的学生
SELECT std_id，club_id AS main_club
FROM    StudentClub
WHERE    main_club_flg='Y';

SELECT std_id,
        CASE WHERE COUNT(*)=1        --只加入一个社团的学生
            THEN MAX(club_id)
            ELSE MAX(CASE WHEN main_club_flag='Y'
                          THEN club_id
                          ELSE NULL END)
        END AS main_club
FROM    StudentClub
GROUP BY std_id

自连接的用法

删除重复行

DELETE FROM Products P1
WHERE　EXISTS(SELECT *
                FROM Products P2
                WHERE P1.name =P2.name
                AND P1.price=P2.price
                AND P1.rowid <P2.rowid);

查找局部不一致的列

--用于查询价格相等但商品名称不同的记录的SQL语句
SELECT DISTINCT P1。name，P1.price
FROM Products P1,Products P2
WHERE P1.price =p2.price
AND P1.name<>P2.name

排序

--排序，使用窗口函数
SELECT name,price
    RANK() OVER (ORDER BY price DESC) AS rank_1,
    DENSE_RANK() OVER(ORDER BY price DESC)AS rank_2
FROM Products

三值逻辑和NULL

true和false两个值，第三个值unknown

1. 排中律不成立
2. NOT IN和NOT EXISTS不是等价的

HAVING子句的力量

寻找缺失的编号

--如果有查询结果，说明存在缺失的编号
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*)<>MAX(seq)

求众数

SELECT income,COUNT(*)AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >=ALL(SELECT COUNT(*)
                        FROM Graduates
                        GROUP BY income)

求中位数

--求中位数SQL语句：在HAVING子句中使用非等值自连接
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
        FROM Graduates T1,Graduates T2
        GROUP BY T1.income)
        --S1的条件
HAVING SUM(CASE WHEN T2.income >=T1.income THEN 1 ELSE 0 END)>=COUNT(*)/2
AND SUM(CASE WHERE T2.income<=T1.income THEN 1 ELSE 0 END)>=COUNT(*)/2) TEP

查询不包含NULL的集合

--在对包含NULL的列使用时，COUNT(*)和COUNT(列名)的查询结果是不同的
SELECT COUNT(*),COUNT(col_1)
FROM NUllTbl

外连接的用法

用外连接进行行列转换(行→列)

--水平展开求交叉表
SELECT C0,name,
    CASE WHEN C1.name IS NOT NULL THEN 'O' ELSE NULL END AS "SQL入门"
    CASE WHEN C2.name IS NOT NULL THEN 'O' ELSE NULL END AS "UNIX基础"
    CASE WHEN C3.name IS NOT NULL THEN 'O' ELSE NULL END AS "Java中级"
FROM (SELECT DISTINCT name FROM Courses)CO --这里的Co是侧栏
LEFT OUTER JOIN
    (SELECT name FROM Courses WHERE course ='SQL入门')C1 ON CO.name =C1.name
LEFT OUTER JOIN
    (SELECT name FROM Courses WHERE course='UNIX基础')C2 ON C0.name=C2.name
LEFT OUTEER JOIN
    (SELECT name FROM Courses WHERE course='Java中级')C3 ON C0.name=C3.name

用外连接进行行列转换(列→行)

--列数据转换成行数据；使用UNION ALL
SELECT employee,child_1 AS child FROM Personnel UNION ALL
SELECT employee,child_2 AS child FROM Personnel UNION ALL
SELECT employee,child_3 AS child FROM Personnel

用外连接求差集：A-B

SELECT A.id AS id,A.name AS A_name
FROM Class_A A LEFT OUTER JOIN CLass_B B
ON A.id =B.id
WHERE B.name IS NULL

用全外连接求异或集

SELECT COALESCE(A.id,B.id)AS id,
    COALESCE(A.name,B.name)AS name
FROM Class_A A FULL OUTER OUTER JOIN Class_B B
ON A.id=B.id
WHERE A.name IS NULL
OR B.name IS NULL

用关联子查询比较行与行

和上一年比较结果

--求出的是增长了还是减少了，亦或者是维持现状，使用关联子查询
SELECT S1.year,S1.sale,
        CASE WHEN sale=
            (SELECT sale
            FROM Sales S2
            WHERE S2.year =S1.year-1)THEN '→'--持平
        WHEN sale>
            (SELECT sale
            FROM Sales S2
            WHERE S2.year =S1.year-1)THEN '↑'--增长
        WHEN sale<
            (SELECT sale
            FROM Sales S2
            WHERE S2.year =S1.year-1)THEN '↓'--减少
        ELSE '-' END AS var
FROM Sales S1
ORDER BY year；

和过去最临近的年份营业额相同的年份，同时使用自连接

SELECT S1.year AS year,
    S1.year AS year,
FROM Sale2 S1,Sales2 S2
WHERE S1.sale =S2.sale
AND S2.year =(SELECT MAX(year)
              FROM Sales2 S3
                WHERE S1.year>S3.year)
ORDER BY year;

移动累计值

--求累计值；使用肥罗已满型递归集合
SELECT prc_data, A1.prc_amt,
        (SELECT SUM(prc_amt)
        FROM Accounts A2
        WHERE A1.prc_date >=A2.prc_date)AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;

查询重叠的时间区间

--求重叠的住宿期间
SELECT reserver,start_date,end_date
FROM Reservations R1
WHERE EXISTS
    (SELECT *
    FROM Reservations R2
    WHERE R1.reserver<> R2.reserver    --与自己以外的客人进行比较
    AND (R1.start_date BETWEEN R2.start_date AND R2.end_date    
                    --自己的入住日期在他人的住宿期间
    OR R1.end_date BETWEEN R2.start_date AND R2.end_date));
                    --自己的离店日期在他人的住宿期间内