针对两个关系A、B ,union关键字将两者连接成为一个只包含A和B中非重复字段的单一关系。sql中,union联合两个select结果,默认消除重复数据(利用union all保留全部数据):
select f.*,top_foods.count from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 1) top_foods
on f.id = top_foods.food_id
union
select f.*,bottom_foods.count from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 1) bottom_foods
on f.id = bottom_foods.food_id
order by top_foods.count desc;
,这是要找出foods表中最高频率和最低频率的食品。
intersect操作两个关系A和B,选择在A也在B中的行。会用intersect找出处于3和5之间的处于前10位的食品:
select f.* from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 10) top_foods
on f.id = top_foods.food_id
intersect
select f.* from foods f
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id = e.id
where e.season between 3 and 5
order by f.name;
except 操作两个关系A和B,找出所有在A而不在B的行:
select f.* from foods f
inner join
(select food_id,count(food_id) as count from foods_episodes
group by food_id
order by count(food_id) desc limit 10) top_foods
on f.id = top_foods.food_id
except
select f.* from foods f
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id = e.id
where e.season between 3 and 5
order by f.name;
注意:复合查询只是要求在结尾有一个order by 语句。
处理sqlite中的null:
null是缺失信息的占位符,本身不是值。null与真假值之间的关系:
x | y | x and y | x or y |
True |
True |
True |
True |
True |
False |
False |
True |
True |
NULL |
NULL |
True |
False |
False |
False |
False |
False |
NULL |
False |
NULL |
NULL |
NULL |
NULL |
NULL |
注意:我们如果要检验null是否存在,可以使用is null,或者is not null来检验,而使用equal或者greater than 可能得到很奇怪的结果。null不等于任何值,null和null也是不一样的,因为你根本就不知道null存储了什么。
非0的任何值都是“真”。
语法:coalesce(表达式[ ,,……n,,,]);
COALESCE ( expression [,...n ] )
变量均为null时,返回null;如果至少有一个不是null,那么返回第一个不是null的值。注意聚合中,null的用法。
特别注意:
COALESCE(expression1,...n) 与此 CASE 函数等价:
CASE WHEN (expression1 IS NOT NULL) THEN expression1 ...
更多可以参考:
http://content.edu-edu.com.cn/info/2010/07/21/000048p2.shtml