第二節(jié) on、where、having的不同之處
這里有個(gè)例子來(lái)比較一下過(guò)濾條件放在on、where、having會(huì)有什么的不同之處:
表recdbf內(nèi)容如下: 還有一個(gè)tempyf的輔助表,記錄12個(gè)月
日期 性質(zhì) yf
2000年7月3日 特大 1
2000年7月9日 特大 2
2000年9月3日 特大 3
1999年3月2日 一般 4
1999年3月4日 一般 5
2000年1月3日 一般 6
2000年2月1日 一般 7
2000年2月3日 一般 8
2000年3月4日 一般 9
2000年8月7日 一般 10
2000年11月2日 一般 11
1999年2月3日 重大 12
2000年2月3日 重大
2000年5月2日 重大
2000年8月9日 重大
現(xiàn)在的要求是要統(tǒng)計(jì)yy年中十二個(gè)月的事故記錄中,一般、重大、特大各有多少。如果沒(méi)有事故的,則以0表示。
我們首先要把今年的記錄過(guò)濾出來(lái),過(guò)濾條件就是YEAR(日期)=?yy,然后按月份分組統(tǒng)計(jì)。
這樣一來(lái),如果某個(gè)月沒(méi)有事故記錄,那分組后的結(jié)果就沒(méi)有該月的記錄,這樣不符合要求。所以做個(gè)臨時(shí)表yf,該表有十二個(gè)記錄,分別代表1至12月,用它來(lái)左聯(lián)接recdbf,這樣,即使某個(gè)月沒(méi)有事故記錄,也會(huì)出現(xiàn)在最后的結(jié)果當(dāng)中,只是以null的形式出現(xiàn)罷了。但我們可以使用isnull()函數(shù)來(lái)判斷它是不是null值,如果是,則iif()會(huì)把它變?yōu)?,然后交與sum()進(jìn)行統(tǒng)計(jì)。
總體設(shè)想搞好后,現(xiàn)在就開(kāi)始寫命令了。開(kāi)始之前先說(shuō)明:tempyf.yf = MONTH(recdbf.日期)是yf表與recdbf表的聯(lián)接條件,是一定要在on的,這個(gè)不在討論范圍。我們要討論的是YEAR(日期) = ?yy這個(gè)條件放在什么地方會(huì)有什么樣的結(jié)果。
首先把過(guò)濾條件放在on這里:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy;
GROUP BY tempyf.yf
其中yy=2000,表示統(tǒng)計(jì)2000年的數(shù)據(jù)。
用where的命令如下:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
where YEAR(日期) = ?yy &&注意,條件從on移到這里來(lái)了
用having的命令如下:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
having YEAR(日期) = ?yy &&注意,條件從on移到這里來(lái)了
on的結(jié)果如下,這是正確的:
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
4 0 0 0
5 0 1 0
6 0 0 0
7 0 0 2
8 1 1 0
9 0 0 1
10 0 0 0
11 1 0 0
12 0 0 0
用where的結(jié)果如下:
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
5 0 1 0
7 0 0 2
8 1 1 0
用having的結(jié)果如下:
YF 一般 重大 特大
1 1 0 0
2 2 2 0
5 0 1 0
7 0 0 2
8 1 1 0
9 0 0 1
11 1 0 0
各位看到有什么不同嗎?
on是把先把recdbf中不是2000年的記錄過(guò)濾掉,剩下的就是2000年的了,再用tempyf去和它們進(jìn)行外聯(lián)接,其結(jié)果可用:
sele tempyf.*,recdbf.日期 ;
from tempyf left join recdbf ;
ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy;
orde by yf
來(lái)查看,這個(gè)中間結(jié)果出來(lái)后,再用isnull把空值的記錄變成0或1,然后由sum去統(tǒng)計(jì),結(jié)果就出來(lái)了。
而where呢:
1、 它是先把tempyf外聯(lián)接recdbf,相當(dāng)于sele tempyf.*,recdbf.* from tempyf left join recdbf on tempyf.yf=mont(recdbf.日期);
2、 然后把不是2000的記錄過(guò)濾掉,這里要注意的是,如果某個(gè)月沒(méi)有記錄的話,那在第一個(gè)步驟后日期那里是null值,這當(dāng)然不是2000的記錄,所以就給這個(gè)條件給過(guò)濾出去了,所以下一步的sum之后就只剩下那有記錄的那個(gè)月了,象4、6月等幾個(gè)月就沒(méi)有了;
3、 然后進(jìn)行sum(……)。
再看having:
1、第一步和where一樣;
2、 第二步不同,它是先sum(),這里的sum可不管你是1999年還是2000的,先累加起來(lái)再說(shuō),這時(shí),1999和2000年的2月份都有"重大"這個(gè)記錄,sum的結(jié)果是2,這里用第三個(gè)步驟去分辨這個(gè)2之中那個(gè)是1999年的,那個(gè)是2000的,這當(dāng)然分不清啦,所以也錯(cuò)了;
3、 根據(jù)步驟2來(lái)把2000的過(guò)濾出來(lái)。
所以on、where、having這三個(gè)都可以加條件的子句中,on是最先執(zhí)行,where次之,having最后。有時(shí)候如果這先后順序不影響中間結(jié)果的話,那最終結(jié)果是相同的。但因?yàn)閛n是先把不符合條件的記錄過(guò)濾后才進(jìn)行統(tǒng)計(jì),它就可以減少中間運(yùn)算要處理的數(shù)據(jù),按理說(shuō)應(yīng)該速度是最快的。
這里有個(gè)例子來(lái)比較一下過(guò)濾條件放在on、where、having會(huì)有什么的不同之處:
表recdbf內(nèi)容如下: 還有一個(gè)tempyf的輔助表,記錄12個(gè)月
日期 性質(zhì) yf
2000年7月3日 特大 1
2000年7月9日 特大 2
2000年9月3日 特大 3
1999年3月2日 一般 4
1999年3月4日 一般 5
2000年1月3日 一般 6
2000年2月1日 一般 7
2000年2月3日 一般 8
2000年3月4日 一般 9
2000年8月7日 一般 10
2000年11月2日 一般 11
1999年2月3日 重大 12
2000年2月3日 重大
2000年5月2日 重大
2000年8月9日 重大
現(xiàn)在的要求是要統(tǒng)計(jì)yy年中十二個(gè)月的事故記錄中,一般、重大、特大各有多少。如果沒(méi)有事故的,則以0表示。
我們首先要把今年的記錄過(guò)濾出來(lái),過(guò)濾條件就是YEAR(日期)=?yy,然后按月份分組統(tǒng)計(jì)。
這樣一來(lái),如果某個(gè)月沒(méi)有事故記錄,那分組后的結(jié)果就沒(méi)有該月的記錄,這樣不符合要求。所以做個(gè)臨時(shí)表yf,該表有十二個(gè)記錄,分別代表1至12月,用它來(lái)左聯(lián)接recdbf,這樣,即使某個(gè)月沒(méi)有事故記錄,也會(huì)出現(xiàn)在最后的結(jié)果當(dāng)中,只是以null的形式出現(xiàn)罷了。但我們可以使用isnull()函數(shù)來(lái)判斷它是不是null值,如果是,則iif()會(huì)把它變?yōu)?,然后交與sum()進(jìn)行統(tǒng)計(jì)。
總體設(shè)想搞好后,現(xiàn)在就開(kāi)始寫命令了。開(kāi)始之前先說(shuō)明:tempyf.yf = MONTH(recdbf.日期)是yf表與recdbf表的聯(lián)接條件,是一定要在on的,這個(gè)不在討論范圍。我們要討論的是YEAR(日期) = ?yy這個(gè)條件放在什么地方會(huì)有什么樣的結(jié)果。
首先把過(guò)濾條件放在on這里:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy;
GROUP BY tempyf.yf
其中yy=2000,表示統(tǒng)計(jì)2000年的數(shù)據(jù)。
用where的命令如下:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
where YEAR(日期) = ?yy &&注意,條件從on移到這里來(lái)了
用having的命令如下:
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性質(zhì))=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性質(zhì))=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性質(zhì))=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
having YEAR(日期) = ?yy &&注意,條件從on移到這里來(lái)了
on的結(jié)果如下,這是正確的:
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
4 0 0 0
5 0 1 0
6 0 0 0
7 0 0 2
8 1 1 0
9 0 0 1
10 0 0 0
11 1 0 0
12 0 0 0
用where的結(jié)果如下:
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
5 0 1 0
7 0 0 2
8 1 1 0
用having的結(jié)果如下:
YF 一般 重大 特大
1 1 0 0
2 2 2 0
5 0 1 0
7 0 0 2
8 1 1 0
9 0 0 1
11 1 0 0
各位看到有什么不同嗎?
on是把先把recdbf中不是2000年的記錄過(guò)濾掉,剩下的就是2000年的了,再用tempyf去和它們進(jìn)行外聯(lián)接,其結(jié)果可用:
sele tempyf.*,recdbf.日期 ;
from tempyf left join recdbf ;
ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy;
orde by yf
來(lái)查看,這個(gè)中間結(jié)果出來(lái)后,再用isnull把空值的記錄變成0或1,然后由sum去統(tǒng)計(jì),結(jié)果就出來(lái)了。
而where呢:
1、 它是先把tempyf外聯(lián)接recdbf,相當(dāng)于sele tempyf.*,recdbf.* from tempyf left join recdbf on tempyf.yf=mont(recdbf.日期);
2、 然后把不是2000的記錄過(guò)濾掉,這里要注意的是,如果某個(gè)月沒(méi)有記錄的話,那在第一個(gè)步驟后日期那里是null值,這當(dāng)然不是2000的記錄,所以就給這個(gè)條件給過(guò)濾出去了,所以下一步的sum之后就只剩下那有記錄的那個(gè)月了,象4、6月等幾個(gè)月就沒(méi)有了;
3、 然后進(jìn)行sum(……)。
再看having:
1、第一步和where一樣;
2、 第二步不同,它是先sum(),這里的sum可不管你是1999年還是2000的,先累加起來(lái)再說(shuō),這時(shí),1999和2000年的2月份都有"重大"這個(gè)記錄,sum的結(jié)果是2,這里用第三個(gè)步驟去分辨這個(gè)2之中那個(gè)是1999年的,那個(gè)是2000的,這當(dāng)然分不清啦,所以也錯(cuò)了;
3、 根據(jù)步驟2來(lái)把2000的過(guò)濾出來(lái)。
所以on、where、having這三個(gè)都可以加條件的子句中,on是最先執(zhí)行,where次之,having最后。有時(shí)候如果這先后順序不影響中間結(jié)果的話,那最終結(jié)果是相同的。但因?yàn)閛n是先把不符合條件的記錄過(guò)濾后才進(jìn)行統(tǒng)計(jì),它就可以減少中間運(yùn)算要處理的數(shù)據(jù),按理說(shuō)應(yīng)該速度是最快的。