|
創(chuàng)建表
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create table users(id int,name varchar2(20),sex number);
insert into users(id,name) values(1,'張一');
insert into users(id,name,sex) values(2,'張二',1);
insert into users(id,name) values(3,'張三');
insert into users(id,name) values(4,'張四');
insert into users(id,name,sex) values(5,'張五',2);
insert into users(id,name,sex) values(6,'張六',1);
insert into users(id,name,sex) values(7,'張七',2);
insert into users(id,name,sex) values(8,'張八',1);
commit;
|
1、上表結(jié)果中的"sex"是用代碼表示的，希望將代碼用中文表示?？稍谡Z(yǔ)句中使用case語(yǔ)句：
|
1
2
3
4
5
6
7
|
select * from users;
select tt.* ,(case tt.sex when 1 then '男'
when 2 then '女'
else '空的'
end
)性別
from users tt ;
|
2、如果不希望列表中出現(xiàn)"sex"列，語(yǔ)句如下：
|
1
2
3
4
5
6
|
select tt.id,tt.name ,(case tt.sex when 1 then '男'
when 2 then '女'
else '空的'
end
)性別
from users tt ;
|
3、將sum與case結(jié)合使用，可以實(shí)現(xiàn)分段統(tǒng)計(jì)。
|
1
2
3
4
5
6
7
8
|
select sum(case when tt.sex = 1 then 1 else 0 end)男性,
sum(case when tt.sex = 2 then 1 else 0 end)女性,
sum(case when tt.sex <>1 and tt.sex <>1 then 1 else 0 end)性別為空
from users tt;
男性 女性 性別為空
---------- ---------- ----------
3 2 0
|
|
1
2
3
4
|
select count(case when tt.sex = 1 then 1 end)男性,
count(case when tt.sex = 2 then 1 end)女性,
count(case when tt.sex <>1 and tt.sex <>1 then 1 end)性別為空
from users tt;
|
3.1、這里發(fā)現(xiàn)了一個(gè)錯(cuò)誤 性別為空的為0 why? 不是3嗎?
t-sql 三個(gè)用于查詢的篩選器
on 第一個(gè)
where 第二個(gè)
having 第三個(gè)
在SQL中，邏輯表達(dá)式的可能值為True False Unknown,稱為三值邏輯
當(dāng)比較丟失值和另一個(gè)值，邏輯結(jié)果為Unknown，例如:null>42
所有的篩選器都把Unknown 當(dāng)做Fasle 處理
但是Check 約束會(huì)把Unknown當(dāng)做True處理
而唯一約束，排序操作和分組操作認(rèn)為兩個(gè)Null值是相等的
如果表中有一列定義了唯一約束，將無法向表內(nèi)插入該值為Null的兩行
Group by 子句把所有Null值分到一組
Order by 子句把所有Null值排列在一起.
|
1
2
3
4
5
6
7
8
9
|
(2)應(yīng)用ON篩選器（聯(lián)接條件）
說到這里，首先得說下 SQL用于查詢的有三個(gè)篩選器，分別是ON，where，having。
這里的ON篩選器就是用VTB1作為輸入，再利用ON后面的聯(lián)結(jié)條件進(jìn)行篩選，生成VTB2。
sql編程第二特征：三值邏輯：它其實(shí)就是所謂的TRUE FALSE UNKOWN
主要來說這個(gè)UNKOWN 它這邏輯值和NULL在一起就會(huì)”出事“。凡是NULL參與的比如 NULL>42, NULL-8>9 這些結(jié)果都是UNKOWN
UNKOWN 的邏輯結(jié)果在不同的環(huán)境下是被不同的方式處理的：如
在三個(gè)篩選器上，它是會(huì)被當(dāng)做FALSE處理；
在CHECK約束上是會(huì)被當(dāng)成TRUE來處理，check(col>8) 你插入的COL為NULL，因?yàn)镹ULL>8 的結(jié)果不就是UNKOWN ，所以是可以插入的。
在篩選器上的2個(gè)NULL比較將是FALSE 但是在UNIQUE約束，分組，排序上2個(gè)NULL是等價(jià)的。
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
算術(shù)運(yùn)算
如果算術(shù)表達(dá)式的任一輸入為空，則該算術(shù)表達(dá)式（涉及諸如 +、-、* 或 / 的算術(shù)運(yùn)算）結(jié)果為空。
例：如果查詢中有一個(gè)表達(dá)式是r.A + 5, 并且對(duì)于關(guān)系中某個(gè)特定的元組， r.A為空，那么對(duì)此元組來說，該表達(dá)式的結(jié)果也為空。
比較運(yùn)算
SQL將涉及空值的任何比較運(yùn)算的結(jié)果視為unknown（既不是謂詞is null，也不是is not null）。unknown是在SQL中除true和false之外的第三個(gè)邏輯值。
補(bǔ)充：由于在where子句的謂詞中可以對(duì)比較結(jié)果使用諸如and、or和not的布爾運(yùn)算，將布爾運(yùn)算擴(kuò)展到可以處理unknown值，運(yùn)算結(jié)果如下：
and：true and unknown = unknown， false and unknown = false， unknown and unknown = unknown
or：true or unknown = true， false or unknown = unknown， unknown or unknown = unknown
not：not unknown = unknown
可以驗(yàn)證，如果r.A為空，那么"1 < r.A" 和 "not (1 < r.A)"的結(jié)果都為unknown。
"null = null"會(huì)返回unknown，而不是true。
|
|
1
2
|
select (case when tt.sex <>1 and tt.sex <>2 then 1 end)性別為空
from users tt;
|
|
1
|
解釋: sex=1 時(shí)候 不符合條件 1<>1 ,sex=2 時(shí)候 2<>2 不符合條件,為null的時(shí)候 unknown 篩選器當(dāng)做false處理,所以,都不符合條件,不能設(shè)置為1,設(shè)置為null.
|
這里就能解釋了
|
1
2
|
select count(case when tt.sex <>1 and tt.sex <>2 then 1 end)性別為空
from users tt; //結(jié)果為0
|
解釋: tt.sex <>1 為null<>1 為unkown , unknown and unknown 為unkonwn 被選擇器when 當(dāng)做fasle處理,被篩下丟棄,所以結(jié)果為0
|
1
2
|
select (case when tt.sex <>1 and tt.sex <>2 then 1 end)性別為空
from users tt;
|
所以最后對(duì)null的判斷不對(duì),改為:select count(case when tt.sex is null then 1 end)性別為空 from users tt;
SqlServer mysql 按月統(tǒng)計(jì)所有部門案例:
以訂單統(tǒng)計(jì)為例，前端展示柱狀圖（Jquery統(tǒng)計(jì)）：
表及主要字段描述如下；表名：Orders
1.日期CreateTime
2.金額Amount
3.用戶UserID
情況一：
根據(jù)部門統(tǒng)計(jì)某一年每月銷量（查詢一個(gè)部門月統(tǒng)計(jì)）
1）直接在SQL語(yǔ)句中判斷每月信息，好處，前臺(tái)直接調(diào)用；壞處，性能不高。
SQL語(yǔ)句:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT
SUM(CASE WHEN MONTH(s.CreateTime) = 1 THEN s.Amount ELSE 0 END) AS '一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 2 THEN s.Amount ELSE 0 END) AS '二月',
SUM(CASE WHEN MONTH(s.CreateTime) = 3 THEN s.Amount ELSE 0 END) AS '三月',
SUM(CASE WHEN MONTH(s.CreateTime) = 4 THEN s.Amount ELSE 0 END) AS '四月',
SUM(CASE WHEN MONTH(s.CreateTime) = 5 THEN s.Amount ELSE 0 END) AS '五月',
SUM(CASE WHEN MONTH(s.CreateTime) = 6 THEN s.Amount ELSE 0 END) AS '六月',
SUM(CASE WHEN MONTH(s.CreateTime) = 7 THEN s.Amount ELSE 0 END) AS '七月',
SUM(CASE WHEN MONTH(s.CreateTime) = 8 THEN s.Amount ELSE 0 END) AS '八月',
SUM(CASE WHEN MONTH(s.CreateTime) = 9 THEN s.Amount ELSE 0 END) AS '九月',
SUM(CASE WHEN MONTH(s.CreateTime) = 10 THEN s.Amount ELSE 0 END) AS '十月',
SUM(CASE WHEN MONTH(s.CreateTime) = 11 THEN s.Amount ELSE 0 END) AS '十一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 12 THEN s.Amount ELSE 0 END) AS '十二月'
FROM Orders AS s
WHERE YEAR(s.CreateTime) = 2014
|
|
1
2
|
一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 741327.00 120505.00 0.00
|
2）統(tǒng)計(jì)出數(shù)據(jù)庫(kù)里有值的月份，再前端邏輯判斷其他月份補(bǔ)0
SQL語(yǔ)句:
|
1
2
3
4
5
|
SELECT UserID, MONTH(CreateTime) as 月份, SUM(Amount) as 統(tǒng)計(jì)
FROM Orders
WHERE YEAR(CreateTime) = 2014 -- 這里假設(shè)你要查 2014年的每月的統(tǒng)計(jì)。
--其他條件
GROUP BY UserID, MONTH(CreateTime)
|
|
