<SQL 进阶教程>里的1-2与1-3节
艾宾浩斯记忆力曲线,1,2,6天,30天后要拿出来再看一下。
我自己就简化为1,6,30.
1-2 自连接的两种用途:
(1)非等值自连接实现,列的组合
select p1.name as name1,p2.name as name2
from fruits p1, fruits p2
where p1.name<p2.name
(2)非等值自连接实现,排序
SELECT
NAME,
price,
(
SELECT
count(f2.price)
FROM
fruits f2
WHERE
f1.price < f2.price
) + 1 AS ranki
FROM
fruits f1
ORDER BY
ranki
1-3 三值逻辑和NULL值
(1)NULL 不是值,要用 is null 来判断
select *
from sa
where age is NULL
(2)not in 中含有空,查询结果就可能全部为空,
# 这种情况下用 not exists 保险
select *
from sa
where not exists(select *
from sb
where sb.city='东京'
and sa.age=sb.age)