/* 将已有编号方式转换为新的方式并统计 */
CREATE TABLE PopTbl
(
    pref_name  VARCHAR(32) PRIMARY KEY,
    population INTEGER NOT NULL
);

INSERT INTO PopTbl VALUES('德岛', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('爱媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福冈', 300);
INSERT INTO PopTbl VALUES('佐贺', 100);
INSERT INTO PopTbl VALUES('长崎', 200);
INSERT INTO PopTbl VALUES('东京', 400);
INSERT INTO PopTbl VALUES('群马', 50);


/* 把县编号转换成地区编号（1） */
SELECT CASE pref_name
           WHEN '德岛' THEN '四国'
           WHEN '香川' THEN '四国'
           WHEN '爱媛' THEN '四国'
           WHEN '高知' THEN '四国'
           WHEN '福冈' THEN '九州'
           WHEN '佐贺' THEN '九州'
           WHEN '长崎' THEN '九州'
           ELSE '其他' END AS district,
       SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END;

/* 按人口数量等级划分都道府县 */
SELECT CASE
           WHEN population < 100 THEN '01'
           WHEN population >= 100 AND population < 200 THEN '02'
           WHEN population >= 200 AND population < 300 THEN '03'
           WHEN population >= 300 THEN '04'
           ELSE NULL END AS pop_class,
       COUNT(*)          AS cnt
FROM PopTbl
GROUP BY CASE
             WHEN population < 100 THEN '01'
             WHEN population >= 100 AND population < 200 THEN '02'
             WHEN population >= 200 AND population < 300 THEN '03'
             WHEN population >= 300 THEN '04'
             ELSE NULL END;

/* 把县编号转换成地区编号(2)：将CASE表达式归纳到一处 */
SELECT CASE pref_name
           WHEN '德岛' THEN '四国'
           WHEN '香川' THEN '四国'
           WHEN '爱媛' THEN '四国'
           WHEN '高知' THEN '四国'
           WHEN '福冈' THEN '九州'
           WHEN '佐贺' THEN '九州'
           WHEN '长崎' THEN '九州'
           ELSE '其他' END AS district,
       SUM(population)
FROM PopTbl
GROUP BY district;

/* 用一条SQL语句进行不同条件的统计 */
CREATE TABLE PopTbl2
(
    pref_name  VARCHAR(32),
    sex        CHAR(1) NOT NULL,
    population INTEGER NOT NULL,
    PRIMARY KEY (pref_name, sex)
);

INSERT INTO PopTbl2 VALUES('德岛', '1',	60 );
INSERT INTO PopTbl2 VALUES('德岛', '2',	40 );
INSERT INTO PopTbl2 VALUES('香川', '1',	100);
INSERT INTO PopTbl2 VALUES('香川', '2',	100);
INSERT INTO PopTbl2 VALUES('爱媛', '1',	100);
INSERT INTO PopTbl2 VALUES('爱媛', '2',	50 );
INSERT INTO PopTbl2 VALUES('高知', '1',	100);
INSERT INTO PopTbl2 VALUES('高知', '2',	100);
INSERT INTO PopTbl2 VALUES('福冈', '1',	100);
INSERT INTO PopTbl2 VALUES('福冈', '2',	200);
INSERT INTO PopTbl2 VALUES('佐贺', '1',	20 );
INSERT INTO PopTbl2 VALUES('佐贺', '2',	80 );
INSERT INTO PopTbl2 VALUES('长崎', '1',	125);
INSERT INTO PopTbl2 VALUES('长崎', '2',	125);
INSERT INTO PopTbl2 VALUES('东京', '1',	250);
INSERT INTO PopTbl2 VALUES('东京', '2',	150);

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


-- 女性人口
SELECT pref_name,
       SUM(population) f
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;


/* 员工工资信息表 */
CREATE TABLE Salaries
(
    name   VARCHAR(32) PRIMARY KEY,
    salary INTEGER NOT NULL
);

INSERT INTO Salaries VALUES('相田', 300000);
INSERT INTO Salaries VALUES('神崎', 270000);
INSERT INTO Salaries VALUES('木村', 220000);
INSERT INTO Salaries VALUES('齐藤', 290000);

/* 用CHECK约束定义多个列的条件关系 */
/* 蕴含式

CONSTRAINT check_salary
CHECK
(CASE WHEN sex = '2'
    THEN CASE WHEN salary <= 200000
    THEN 1 ELSE 0 END
    ELSE 1 END = 1)
*/

/* 逻辑与

CONSTRAINT check_salary CHECK
( sex = '2' AND salary <= 200000 )

*/

/* 在 UPDATE 语句里进行条件分支 */
/* 错误示例 */

-- 条件 1
UPDATE Salaries
SET salary = salary * 0.9
WHERE salary >= 300000;


-- 条件 2
UPDATE Salaries
SET salary = salary * 1.2
WHERE salary >= 250000
  AND salary < 280000;

UPDATE Salaries SET salary = 300000 WHERE name='相田';
UPDATE Salaries SET salary = 270000 WHERE name='神崎';

/* 用CASE表达式写正确的更新操作 */
UPDATE Salaries
SET salary = CASE
                 WHEN salary >= 300000
                     THEN salary * 0.9
                 WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                 ELSE salary END;

/* 在UPDATE语句里进行条件分支 */
CREATE TABLE SomeTable
(
    p_key CHAR(1) PRIMARY KEY,
    col_1 INTEGER NOT NULL,
    col_2 CHAR(2) NOT NULL
);

INSERT INTO SomeTable VALUES('a', 1, '一');
INSERT INTO SomeTable VALUES('b', 2, '二');
INSERT INTO SomeTable VALUES('c', 3, '三');

/* 调换主键值*/
/* 三次 UPDATE 操作 */
UPDATE SomeTable SET p_key='d' where p_key='a';
UPDATE SomeTable SET p_key='a' where p_key='b';
UPDATE SomeTable SET p_key='b' where p_key='d';

/* 用CASE表达式调换主键值 */
/* 注意：在 PostgreSQL 和 MySQL 会因主键重复而出现错误 */
UPDATE SomeTable
SET p_key = CASE
                WHEN p_key = 'a'
                    THEN 'b'
                WHEN p_key = 'b'
                    THEN 'a'
                ELSE p_key END
WHERE p_key IN ('a', 'b');



/* 表之间的数据匹配 */
CREATE TABLE CourseMaster
(
    course_id   INTEGER PRIMARY KEY,
    course_name VARCHAR(32) NOT NULL
);

INSERT INTO CourseMaster VALUES(1, '会计入门');
INSERT INTO CourseMaster VALUES(2, '财务知识');
INSERT INTO CourseMaster VALUES(3, '簿记考试');
INSERT INTO CourseMaster VALUES(4, '税务师');

CREATE TABLE OpenCourses
(
    month     INTEGER,
    course_id INTEGER,
    PRIMARY KEY (month, course_id)
);

INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);

/* 表的匹配：使用 IN 谓词 */
SELECT CM.course_name,
       CASE
           WHEN CM.course_id IN
                (SELECT course_id
                 FROM OpenCourses
                 WHERE month = 200706) THEN '○'
           ELSE '×' END AS "6月",
       CASE
           WHEN CM.course_id IN
                (SELECT course_id
                 FROM OpenCourses
                 WHERE month = 200707) THEN '○'
           ELSE '×' END AS "7月",
       CASE
           WHEN CM.course_id IN
                (SELECT course_id
                 FROM OpenCourses
                 WHERE month = 200708) THEN '○'
           ELSE '×' END AS "8月"
FROM CourseMaster CM;

/* 表的匹配：使用 EXISTS 谓词 */
SELECT CM.course_name,
       CASE
           WHEN EXISTS
               (SELECT course_id
                FROM OpenCourses OC
                WHERE month = 200706
                  AND CM.course_id = OC.course_id) THEN '○'
           ELSE '×' END AS "6月",
       CASE
           WHEN EXISTS
               (SELECT course_id
                FROM OpenCourses OC
                WHERE month = 200707
                  AND CM.course_id = OC.course_id) THEN '○'
           ELSE '×' END AS "7月",
       CASE
           WHEN EXISTS
               (SELECT course_id
                FROM OpenCourses OC
                WHERE month = 200708
                  AND CM.course_id = OC.course_id) THEN '○'
           ELSE '×' END AS "8月"
FROM CourseMaster CM;

/* 在CASE表达式中使用聚合函数 */
CREATE TABLE StudentClub
(
    std_id        INTEGER,
    club_id       INTEGER,
    club_name     VARCHAR(32),
    main_club_flg CHAR(1),
    PRIMARY KEY (std_id, club_id)
);

INSERT INTO StudentClub VALUES(100, 1, '棒球',        'Y');
INSERT INTO StudentClub VALUES(100, 2, '管弦乐',      'N');
INSERT INTO StudentClub VALUES(200, 2, '管弦乐',      'N');
INSERT INTO StudentClub VALUES(200, 3, '羽毛球',       'Y');
INSERT INTO StudentClub VALUES(200, 4, '足球',        'N');
INSERT INTO StudentClub VALUES(300, 4, '足球',        'N');
INSERT INTO StudentClub VALUES(400, 5, '游泳',        'N');
INSERT INTO StudentClub VALUES(500, 6, '围棋',        'N');

-- 条件 1 ： 选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;


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

/* 在CASE表达式中使用聚合函数 */
SELECT std_id,
       CASE
           WHEN COUNT(*) = 1 /* 只加入了一个社团的学生 */
               THEN MAX(club_id)
           ELSE MAX(CASE
                        WHEN main_club_flg = 'Y'
                            THEN club_id
                        ELSE NULL END)
           END AS main_club
FROM StudentClub
GROUP BY std_id;


/* 转换行列 —— 在表头里加入汇总和再揭(p.287) */
SELECT sex,
       SUM(population)                                            AS total,
       SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
       SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
       SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
       SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
       SUM(CASE
               WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
                   THEN population
               ELSE 0 END)                                        AS zaijie
FROM PopTbl2
GROUP BY sex;









