SQL权威指南(第4版)

13.7 函数中的NULL值

• NULLIF
• COALESCE(V1,V2……) 返回第一个不为null的参数

18.1.2 带GROUP BY的CASE表达式

CASE表达式和GROUP BY查询结合起来非常有用。如 查询每个部门每个性别的员工数量

select dept_nbr,
    sum(case when sex_code=1 then 1 else 0) as males,
  sum(case when sex_code=2 then 1 else 0) as females
from personnel
group by dept_nbr;

也可以在CASE表达式内调用聚合函数，例如下面的员工技能表

CREATE TABLE PersonnelSkills
( emp_id CHAR ( 11 ) NOT NULL,
    skill_code CHAR ( 11 ) NOT NULL,
    primary_skill_flg CHAR ( 1 ) NOT NULL CONSTRAINT primary_skill_given CHECK (primary_skill_flg IN ( 'Y', 'N' ),
PRIMARY KEY ( emp_id, skill_code ));

每个员工有一个主技能。列出员工技能：1 如果只有一个技能则列出唯一技能，2 如果有多行技能则列出主要技能

SELECT
    emp_id,
CASE COUNT(*)  
        WHEN 1  THEN MAX(skill_code) 
         ELSE MAX(CASE WHEN primary_skill_flg = 'Y' THEN skill_code END )
       END   AS main_skill 
FROM
    PersonnelSkills 
GROUP BY
    emp_id;
  -- 书上的是 以下，但是感觉有问题
  SELECT emp_id,
 CASE WHEN COUNT(*) = 1
 THEN MAX(skill_code)
 ELSE MAX(CASE WHEN primary_skill_flg = 'Y'
 THEN skill_code END)
 ELSE NULL END)
 END AS main_skill
 FROM PersonnelSkills
GROUP BY emp_id;

22.6 EXISTS和三值逻辑

CREATE TABLE suppliers_parts(
sup_nbr char(2) not NULL PRIMARY KEY,
part_nbr char(2) not NULL ,
onhand_qty INTEGER CHECK (onhand_qty>0)
);
INSERT into suppliers_parts VALUES('S1','p',null),('S2','p',200),('S3','p',1000);
SELECT * FROM suppliers_parts ;s

行 ('S1','p',null) 表示供应商S1提供零件P，但我们不知道提供的零件数量。找出“提供零件P，且提供数量不是1000的供应商”

-- 该查询结果只返回 s2
SELECT * from suppliers_parts spx WHERE spx.part_nbr='P' and 1000 not in (
SELECT onhand_qty from suppliers_parts as spy WHERE spy.sup_nbr=spx.sup_nbr and spy.part_nbr='P'
)
-- 该查询结果返回 s1 s2
SELECT * from suppliers_parts spx WHERE spx.part_nbr='P' and   not EXISTS (
SELECT * from suppliers_parts as spy WHERE spy.sup_nbr=spx.sup_nbr and spy.part_nbr='P'
and spy.onhand_qty =1000
)