針對兩個關係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與真假值之間的關係:
表格:與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()函數的用法:
文法:coalesce(運算式[ ,,……n,,,]);
COALESCE ( expression [ ,...n ] )
變數均為null時,返回null;如果至少有一個不是null,那麼返回第一個不是null的值。注意彙總中,null的用法。
特別注意:
COALESCE(expression1,...n) 與此 CASE 函數等價:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
更多可以參考:
sql server的coalesce()的兩種用法:
http://content.edu-edu.com.cn/info/2010/07/21/000048p2.shtml