標(biāo)準(zhǔn) group by 和 having 查詢
- 選擇列表中的列必須也出現(xiàn)在 group by 表達(dá)式中，或者必須是集合函數(shù)的參數(shù)。
- group by 表達(dá)式只能包含選擇列表中出現(xiàn)的列名。不過，選擇列表中僅作為集合函數(shù)的參數(shù)使用的列不適用此限定。
- having 表達(dá)式中的列必須是單值的（例如集合的參數(shù)），而且它們必須出現(xiàn)在選擇列表或 group by 子句中。使用選擇列表集合和 having 子句的查詢必須 包含 group by 子句。如果在未使用選擇列表集合的查詢中省略 group by，則所有未被 where 子句排除的行將被視為單個(gè)組。
在非分組查詢中，“使用 where 排除行”的原則非常簡單直接。在分組查詢中，此原則擴(kuò)展為“使用 where 在 group by 之前排除行，使用 having 從顯示的結(jié)果中排除行”
- SQL 標(biāo)準(zhǔn)允許連接兩個(gè)或更多表的查詢使用 group by 和 having，只要它們遵循上述原則。指定連接或其它復(fù)雜查詢時(shí)，請使用groupby和having的標(biāo)準(zhǔn)語法，除非您完全理解Transact-SQL擴(kuò)展對這兩個(gè)子句的影響。
group by 和 having 的 Transact-SQL 擴(kuò)展
對標(biāo)準(zhǔn) SQL 的 Transact-SQL 擴(kuò)展使得數(shù)據(jù)的顯示更加靈活，因?yàn)樵跀U(kuò)展中允許引用未在創(chuàng)建組或摘要計(jì)算時(shí)使用的列和表達(dá)式：
包含集合的選擇列表可以包含既不是集合函數(shù)的參數(shù)，又未包含在 group by 子句中的擴(kuò)展 列。擴(kuò)展列影響最終結(jié)果的顯示，因?yàn)轱@示了附加的行。
group by 子句可包含未列于選擇列表中的列或表達(dá)式。
group by all 子句顯示所有組，甚至是那些由 where 子句從計(jì)算中排除的組。
having 子句可以包含未出現(xiàn)在選擇列表以及 group by 子句中的列或表達(dá)式。
Transact-SQL 擴(kuò)展將行或列添加到顯示中時(shí)，或省略 group by 時(shí)，查詢結(jié)果可能難以理解。
實(shí)例解析
One long running SQL in my work. It is a wrong SQL in fact:
- 問題
這個(gè)SQL語句最后取的是笛卡爾積，而不是看上去想要的內(nèi)容
SELECT
p.party_id,
p.party_ticker,
p.party_rtr_ticker,
p.dbs_cid,
p.party_cntry_incorp_cd,
p.party_long_name,
p.debt_issued_fl,
p.party_setup_dt,
e.last_chg_dt,
datediff(hour, e.last_chg_dt, getdate() ) AS AGE_in_hours
FROM
dmo_govcorp..party p, dmo_misc..entity_changed e
where e.obj_oid = p.party_id
AND e.obj_type_cd = 'GCPA'
GROUP BY
p.party_rtr_ticker
HAVING
(COUNT(p.party_rtr_ticker)>1)
- 排解過程
運(yùn)行SQL發(fā)現(xiàn)，從Select列表中去掉第二個(gè)表dmo_misc..entity_changed的項(xiàng)，可以得到我們想要的結(jié)果。查閱相關(guān)文檔發(fā)現(xiàn)，如果在select列表包含既不是集合函數(shù)的參數(shù)，那么就是Transact-SQL 擴(kuò)展的group by， 這種方式有時(shí)看起來就像一個(gè)查詢忽略了where 子句 - 解決
We can solve the problem by adding all the conditions in where clauses into having clauses as below to solve it as below:
SELECT
p.party_id,
p.party_ticker,
p.party_rtr_ticker,
p.dbs_cid,
p.party_cntry_incorp_cd,
p.party_long_name,
p.debt_issued_fl,
p.party_setup_dt,
e.last_chg_dt,
datediff(hour, e.last_chg_dt, getdate() ) AS AGE_in_hours
FROM
dmo_govcorp..party p, dmo_misc..entity_changed e
where e.obj_oid = p.party_id
AND e.obj_type_cd = 'GCPA'
GROUP BY
p.party_rtr_ticker
HAVING
(COUNT(p.party_rtr_ticker)>1) and e.obj_oid = p.party_id and e.obj_type_cd = 'GCPA'
- Finding
打開統(tǒng)計(jì)信息發(fā)現(xiàn)直接將where后的條件加到having后邊跟先將分組的選出來，放到放在select的字句里效果是一樣的，推測兩者在數(shù)據(jù)庫內(nèi)部實(shí)現(xiàn)機(jī)制是一樣的。
以下為參考資料：
在 select 列表中使用不在 group by 列表中且不包含集合函數(shù)的列是一種Transact-SQL 擴(kuò)展。 擴(kuò)展列影響最終結(jié)果的顯示，因?yàn)轱@示了附加的行。
Transact-SQL 擴(kuò)展將行或列添加到顯示中時(shí), 查詢結(jié)果可能難以理解。處理 Transact-SQL 擴(kuò)展列的方式有時(shí)看起來就像一個(gè)查詢忽略了where 子句。示例如下：
Adaptive Server 首先使用 where 子句創(chuàng)建一個(gè)僅包含類型和集合值的工作表。此工作表通過分組列 type 連接回 titles 表，以在結(jié)果中包括 price 列，但連接中沒有 使用 where 子句。
select type, price, avg(price)
from titles
where price > 10.00
group by type
type price
------------ ---------------- --------------
business 19.99 17.31
business 11.95 17.31
business 2.99 17.31
business 19.99 17.31
mod_cook 19.99 19.99
mod_cook 2.99 19.99
popular_comp 22.95 21.48
popular_comp 20.00 21.48
popular_comp NULL 21.48
psychology 21.59 17.51
psychology 10.95 17.51
psychology 7.00 17.51
psychology 19.99 17.51
psychology 7.99 17.51
trad_cook 20.95 15.96
trad_cook 11.95 15.96
trad_cook 14.99 15.96
(17 rows affected)
如果還想在顯示的結(jié)果中去除價(jià)格低于 $10.00 的行，就必須添加一個(gè)重復(fù) where 子句的 having 子句
select type, price, avg(price)
from titles
where price > 10.00
group by type
having price > 10.00
type price
----------- ---------------- --------------
business 19.99 17.31
business 11.95 17.31
business 19.99 17.31
mod_cook 19.99 19.99
popular_comp 22.95 21.48
popular_comp 20.00 21.48
psychology 21.59 17.51
psychology 10.95 17.51
psychology 19.99 17.51
trad_cook 20.95 15.96
trad_cook 11.95 15.96
trad_cook 14.99 15.96
(12 rows affected)
參考文獻(xiàn)：Sybase 參考手冊：命令A(yù)daptive Server? Enterprise 15.0
