/* 寻找缺失的编号 */
CREATE TABLE SeqTbl
(seq  INTEGER PRIMARY KEY,
 name VARCHAR(16) NOT NULL);

INSERT INTO SeqTbl VALUES(1,	'迪克');
INSERT INTO SeqTbl VALUES(2,	'安');
INSERT INTO SeqTbl VALUES(3,	'莱露');
INSERT INTO SeqTbl VALUES(5,	'卡');
INSERT INTO SeqTbl VALUES(6,	'玛丽');
INSERT INTO SeqTbl VALUES(8,	'本');


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

-- 无GROUP BY子句   MySQL不支持GROUP BY ()
SELECT '存在缺失的编号' AS gap 
  FROM SeqTbl 
GROUP BY ()
HAVING COUNT(*) <> MAX(seq);


-- 查询缺失编号的最小值
SELECT MIN(seq + 1) AS gap 
  FROM SeqTbl 
 WHERE (seq+ 1) NOT IN (SELECT seq FROM SeqTbl);


-- 寻找缺失的编号：升级版
CREATE TABLE SeqTbl
( seq INTEGER NOT NULL PRIMARY KEY);

-- 不存在缺失编号（起始值＝1）
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1,	'迪克');
INSERT INTO SeqTbl VALUES(2,	'安');
INSERT INTO SeqTbl VALUES(3,	'莱露');
INSERT INTO SeqTbl VALUES(4,	'卡');
INSERT INTO SeqTbl VALUES(5,	'玛丽');
INSERT INTO SeqTbl VALUES(6,	'本');

-- 存在缺失编号（起始值＝1）
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(1);
INSERT INTO SeqTbl VALUES(2);
INSERT INTO SeqTbl VALUES(4);
INSERT INTO SeqTbl VALUES(5);
INSERT INTO SeqTbl VALUES(8);

-- 不存在缺失编号（起始值<>1）
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3,	'迪克');
INSERT INTO SeqTbl VALUES(4,	'安');
INSERT INTO SeqTbl VALUES(5,	'莱露');
INSERT INTO SeqTbl VALUES(6,	'卡');
INSERT INTO SeqTbl VALUES(7,	'玛丽');
INSERT INTO SeqTbl VALUES(8,	'本');



-- 存在缺失编号（起始值<>1）
DELETE FROM SeqTbl;
INSERT INTO SeqTbl VALUES(3,	'迪克');
INSERT INTO SeqTbl VALUES(4,	'安');
INSERT INTO SeqTbl VALUES(7,	'莱露');
INSERT INTO SeqTbl VALUES(8,	'卡');
INSERT INTO SeqTbl VALUES(10,	'玛丽');


SELECT '存在缺失的编号' AS gap 
  FROM SeqTbl 
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1;


-- 不论是否存在缺失的编号, 都返回一行结果
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空' 
            WHEN COUNT(*) <> MAX(seq) -MIN(seq) + 1 THEN '存在缺失的编号' 
            ELSE '連続 ' END AS gap 
  FROM SeqTbl;


-- 查找最小的缺失编号：当表中没有1时，返回1
SELECT  CASE  WHEN  COUNT(*) = 0 OR MIN(seq)  >  1  -- 最小值不是1时→返回1
              THEN  1  
              ELSE  (SELECT MIN(seq +1)  -- 最小值是1时→返回最小的缺失编号 
                       FROM SeqTbl S1  
                      WHERE NOT EXISTS  
                            (SELECT *  
                               FROM SeqTbl S2  
                              WHERE S2.seq = S1.seq + 1)) END as MinID
  FROM SeqTbl;



CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);

INSERT INTO Graduates VALUES('桑普森', 400000);
INSERT INTO Graduates VALUES('迈克',   30000);
INSERT INTO Graduates VALUES('怀特',   20000);
INSERT INTO Graduates VALUES('阿诺德', 20000);
INSERT INTO Graduates VALUES('史密斯', 20000);
INSERT INTO Graduates VALUES('劳伦斯', 15000);
INSERT INTO Graduates VALUES('哈德逊', 15000);
INSERT INTO Graduates VALUES('肯特',   10000);
INSERT INTO Graduates VALUES('贝克',   10000);
INSERT INTO Graduates VALUES('斯科特',  10000);


-- 求众数的SQL语句(1)：使用谓词
SELECT income, COUNT(*) AS cnt 
  FROM Graduates 
 GROUP BY income 
HAVING COUNT(*) >= ALL ( SELECT COUNT(*) 
                           FROM Graduates
                          GROUP BY income);


-- 求众数的SQL语句(2)：使用极值函数
SELECT income, COUNT(*) AS cnt 
  FROM Graduates 
 GROUP BY income 
HAVING COUNT(*) >= ( SELECT MAX(cnt) 
                       FROM ( SELECT COUNT(*) AS cnt 
                                FROM Graduates 
                               GROUP BY income) TMP ) ;


# 众数
select income , count(*)
from Graduates g
group by income
order by count(*) desc;





CREATE TABLE NullTbl (col_1 INTEGER);

INSERT INTO NullTbl VALUES (NULL);
INSERT INTO NullTbl VALUES (NULL);
INSERT INTO NullTbl VALUES (NULL);

SELECT COUNT(*), COUNT(col_1) 
  FROM NullTbl;


/* 查询不包含NULL的集合 */
CREATE TABLE Students1_6
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students1_6 VALUES(100,  '理学院',   '2018-10-10');
INSERT INTO Students1_6 VALUES(101,  '理学院',   '2018-09-22');
INSERT INTO Students1_6 VALUES(102,  '文学院',   NULL);
INSERT INTO Students1_6 VALUES(103,  '文学院',   '2018-09-10');
INSERT INTO Students1_6 VALUES(200,  '文学院',   '2018-09-22');
INSERT INTO Students1_6 VALUES(201,  '工学院',   NULL);
INSERT INTO Students1_6 VALUES(202,  '经济学院', '2018-09-25');


-- 查询“提交日期”列内不包含NULL的学院(1)：使用COUNT函数 
SELECT dpt 
  FROM Students1_6 
 GROUP BY dpt 
HAVING COUNT(*) = COUNT(sbmt_date);

-- 查询“提交日期”列内不包含NULL的学院(2)：使用CASE表达式
SELECT dpt 
  FROM Students1_6 
 GROUP BY dpt 
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL 
                           THEN 1 ELSE 0 END);


-- 为集合设置详细的条件
CREATE TABLE TestResults
(student_id CHAR(12) NOT NULL PRIMARY KEY,
 class   CHAR(1)  NOT NULL,
 sex     CHAR(1)  NOT NULL,
 score   INTEGER  NOT NULL);

INSERT INTO TestResults VALUES('001', 'A', '男', 100);
INSERT INTO TestResults VALUES('002', 'A', '女', 100);
INSERT INTO TestResults VALUES('003', 'A', '女',  49);
INSERT INTO TestResults VALUES('004', 'A', '男',  30);
INSERT INTO TestResults VALUES('005', 'B', '女', 100);
INSERT INTO TestResults VALUES('006', 'B', '男',  92);
INSERT INTO TestResults VALUES('007', 'B', '男',  80);
INSERT INTO TestResults VALUES('008', 'B', '男',  80);
INSERT INTO TestResults VALUES('009', 'B', '女',  10);
INSERT INTO TestResults VALUES('010', 'C', '男',  92);
INSERT INTO TestResults VALUES('011', 'C', '男',  80);
INSERT INTO TestResults VALUES('012', 'C', '女',  21);
INSERT INTO TestResults VALUES('013', 'D', '女', 100);
INSERT INTO TestResults VALUES('014', 'D', '女',   0);
INSERT INTO TestResults VALUES('015', 'D', '女',   0);


# 查询75%以上的学生分数都在80分以上的班级

select class
from TestResults
group by class
having sum(case when score >= 80 then 1
                else 0 end) / count(*) > 0.75;









SELECT class 
  FROM TestResults 
 GROUP BY class 
HAVING COUNT(*) * 0.75 
        <= SUM(CASE WHEN score >= 80 
                    THEN 1 
                    ELSE 0 END) ;



SELECT class 
  FROM TestResults 
 GROUP BY class 
HAVING SUM(CASE WHEN score >= 50 AND sex = '男' 
                THEN 1 
                ELSE 0 END) 
         > SUM(CASE WHEN score >= 50 AND sex = '女' 
                    THEN 1 
                    ELSE 0 END) ; 

-- 比较男生和女生平均分的SQL语句(1)：对空集求平均值使用AVG后返回0
SELECT class 
  FROM TestResults 
 GROUP BY class 
HAVING AVG(CASE WHEN sex = '男' THEN score ELSE 0 END) 
         < AVG(CASE WHEN sex = '女' THEN score ELSE 0 END) ;


-- 比较男生和女生平均分的SQL语句(1)：对空集求平均值使用AVG后返回NULL
SELECT class 
  FROM TestResults 
 GROUP BY class 
HAVING AVG(CASE WHEN sex = '男' THEN score ELSE NULL END) 
        < AVG(CASE WHEN sex = '女' THEN score ELSE NULL END) ;




CREATE TABLE Teams
(member  CHAR(12) NOT NULL PRIMARY KEY,
 team_id INTEGER  NOT NULL,
 status  CHAR(8)  NOT NULL);

INSERT INTO Teams VALUES('乔',   1, '待命');
INSERT INTO Teams VALUES('肯',   1, '出勤中');
INSERT INTO Teams VALUES('米克',  1, '待命');
INSERT INTO Teams VALUES('卡伦',  2, '出勤中');
INSERT INTO Teams VALUES('凯斯',  2, '休息');
INSERT INTO Teams VALUES('简',   3, '待命');
INSERT INTO Teams VALUES('哈特',  3, '待命');
INSERT INTO Teams VALUES('迪克',  3, '待命');
INSERT INTO Teams VALUES('贝斯',  4, '待命');
INSERT INTO Teams VALUES('阿伦',  5, '出勤中');
INSERT INTO Teams VALUES('罗伯特', 5, '休息');
INSERT INTO Teams VALUES('卡根',  5, '待命');

-- 用谓词表达全称量化命题 
SELECT team_id, member 
  FROM Teams T1 
 WHERE NOT EXISTS (SELECT * 
                     FROM Teams T2 
                    WHERE T1.team_id = T2.team_id 
                      AND status <> '待命' ); 

/* 用集合表达全称量化命题(1) */
SELECT team_id
  FROM Teams
 GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命'
                           THEN 1
                           ELSE 0 END);

-- 用集合表达全称量化命题(2)
SELECT team_id 
  FROM Teams 
 GROUP BY team_id 
HAVING MAX(status) = '待命' 
   AND MIN(status) = '待命';

-- 列表显示各个队伍是否所有队员都在待命
SELECT team_id, 
       CASE WHEN MAX(status) = '待命' AND MIN(status) = '待命' 
            THEN '全员待命' 
            ELSE '队长！人手不够' END AS status 
  FROM Teams GROUP BY team_id; 


-- 单重集合与多重集合
CREATE TABLE Materials
(center         CHAR(12) NOT NULL,
 receive_date   DATE     NOT NULL,
 material       CHAR(12) NOT NULL,
 PRIMARY KEY(center, receive_date));

INSERT INTO Materials VALUES('东京'	,'2018-4-01',	'锡');
INSERT INTO Materials VALUES('东京'	,'2018-4-12',	'锌');
INSERT INTO Materials VALUES('东京'	,'2018-5-17',	'铝');
INSERT INTO Materials VALUES('东京'	,'2018-5-20',	'锌');
INSERT INTO Materials VALUES('大阪'	,'2018-4-20',	'铜');
INSERT INTO Materials VALUES('大阪'	,'2018-4-22',	'镍');
INSERT INTO Materials VALUES('大阪'	,'2018-4-29',	'铅');
INSERT INTO Materials VALUES('名古屋',	'2018-3-15',	'钛');
INSERT INTO Materials VALUES('名古屋',	'2018-4-01',	'钢');
INSERT INTO Materials VALUES('名古屋',	'2018-4-24',	'钢');
INSERT INTO Materials VALUES('名古屋',	'2018-5-02',	'镁');
INSERT INTO Materials VALUES('名古屋',	'2018-5-10',	'钛');
INSERT INTO Materials VALUES('福冈'	,'2018-5-10',	'锌');
INSERT INTO Materials VALUES('福冈'	,'2018-5-28',	'锡');


-- 选中材料存在重复的生产地
SELECT center
  FROM Materials
 GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);


SELECT center, CASE WHEN COUNT(material) <> COUNT(DISTINCT material)
                    THEN '存在重复'
                    ELSE '不存在重复'
                END AS status
  FROM Materials
 GROUP BY center;


-- 存在重复的集合：使用EXISTS
SELECT center, material
  FROM Materials M1
 WHERE EXISTS (SELECT *
                 FROM Materials M2
                WHERE M1.center = M2.center
                  AND M1.receive_date <> M2.receive_date
                  AND M1.material = M2.material);



/* 用关系除法运算进行购物篮分析 */
CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
 
CREATE TABLE ShopItems
(shop VARCHAR(16),
 item VARCHAR(16),
    PRIMARY KEY(shop, item));

INSERT INTO Items VALUES('啤酒');
INSERT INTO Items VALUES('纸尿裤');
INSERT INTO Items VALUES('自行车');

INSERT INTO ShopItems VALUES('仙台',  '啤酒');
INSERT INTO ShopItems VALUES('仙台',  '纸尿裤');
INSERT INTO ShopItems VALUES('仙台',  '自行车');
INSERT INTO ShopItems VALUES('仙台',  '窗帘');
INSERT INTO ShopItems VALUES('東京',  '啤酒');
INSERT INTO ShopItems VALUES('東京',  '纸尿裤');
INSERT INTO ShopItems VALUES('東京',  '自行车');
INSERT INTO ShopItems VALUES('大阪',  '电视');
INSERT INTO ShopItems VALUES('大阪',  '纸尿裤');
INSERT INTO ShopItems VALUES('大阪',  '自行车');

-- 查询啤酒、纸尿裤和自行车同时在库的店铺：错误的SQL语句
SELECT DISTINCT shop 
  FROM ShopItems 
 WHERE item IN (SELECT item FROM Items);



-- 查询啤酒、纸尿裤和自行车同时在库的店铺：正确的SQL语句
SELECT SI.shop 
  FROM ShopItems SI INNER JOIN Items I 
    ON SI.item = I.item 
 GROUP BY SI.shop 
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);



-- COUNT(I.item)的值已经不一定是3了
SELECT SI.shop, COUNT(SI.item), COUNT(I.item) 
  FROM ShopItems SI INNER JOIN Items I 
    ON SI.item = I.item 
 GROUP BY SI.shop;


-- 精确关系除法运算：使用外连接和COUNT函数
SELECT SI.shop 
  FROM ShopItems SI LEFT OUTER JOIN Items I 
    ON SI.item=I.item 
 GROUP BY SI.shop 
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)  -- 条件1
   AND COUNT(I.item)  = (SELECT COUNT(item) FROM Items); -- 条件2



-- 精确关系除法运算：使用外连接和COUNT函数
SELECT SI.shop
FROM ShopItems SI LEFT OUTER JOIN Items I
                                  ON SI.item=I.item
GROUP BY SI.shop
having count(SI.item) = count(I.item);



# # # # # # # # # # # # # # # #
# 习题
# # # # # # # # # # # # # # # #

# 1-6-1
# -- 查找最小的缺失编号（不要求从1开始） 不存在缺失的编号 则返回"不存在缺失的编号"
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
            WHEN COUNT(*) <> MAX(seq) -MIN(seq) + 1 THEN '存在缺失的编号'
            ELSE '不存在缺失的编号' END AS gap
FROM SeqTbl;


# 查找最小的缺失编号（要求从1开始） 不存在缺失的编号 则返回"不存在缺失的编号"
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
            WHEN COUNT(*) <> MAX(seq) THEN '存在缺失的编号'
            ELSE '不存在缺失的编号' END AS gap
FROM SeqTbl;








# 1-6-2
# 全体学生都在9月份提交了报告的学院
select stu1.dpt
from Students1_6 stu1
group by stu1.dpt
having count(*) = (select count(*)
                   from Students1_6 stu2
                   where stu1.dpt = stu2.dpt and month(stu2.sbmt_date) = 9)



select stu1.dpt
from Students1_6 stu1
group by stu1.dpt
having count(*) = sum(case when sbmt_date is not null && month(sbmt_date) = 9 then 1
                           else 0 end);




# 返回商铺含有的商品种类  同时  返回商品确实的商品种类
select count(i.item) as my_iten_cnt,
       (select count(*) from Items) - count(i.item) as diff_cnt
from ShopItems s left join Items i on s.item = i.item
group by s.shop;




select *
from ShopItems s left join Items i on s.item = i.item