標準 group by 和 having 查詢
- 選擇列表中的列必須也出現(xiàn)在 group by 表達式中著榴，或者必須是集合函數(shù)的參數(shù)。
- group by 表達式只能包含選擇列表中出現(xiàn)的列名泌类。不過，選擇列表中僅作為集合函數(shù)的參數(shù)使用的列不適用此限定痹屹。
- having 表達式中的列必須是單值的（例如集合的參數(shù)）境氢，而且它們必須出現(xiàn)在選擇列表或 group by 子句中。使用選擇列表集合和 having 子句的查詢必須 包含 group by 子句车要。如果在未使用選擇列表集合的查詢中省略 group by允粤，則所有未被 where 子句排除的行將被視為單個組。
在非分組查詢中翼岁，“使用 where 排除行”的原則非常簡單直接类垫。在分組查詢中，此原則擴展為“使用 where 在 group by 之前排除行琅坡，使用 having 從顯示的結(jié)果中排除行”
- SQL 標準允許連接兩個或更多表的查詢使用 group by 和 having悉患，只要它們遵循上述原則。指定連接或其它復(fù)雜查詢時榆俺，請使用groupby和having的標準語法售躁，除非您完全理解Transact-SQL擴展對這兩個子句的影響。
group by 和 having 的 Transact-SQL 擴展
對標準 SQL 的 Transact-SQL 擴展使得數(shù)據(jù)的顯示更加靈活茴晋，因為在擴展中允許引用未在創(chuàng)建組或摘要計算時使用的列和表達式：
包含集合的選擇列表可以包含既不是集合函數(shù)的參數(shù)迂求，又未包含在 group by 子句中的擴展 列。擴展列影響最終結(jié)果的顯示晃跺，因為顯示了附加的行揩局。
group by 子句可包含未列于選擇列表中的列或表達式。
group by all 子句顯示所有組掀虎，甚至是那些由 where 子句從計算中排除的組凌盯。
having 子句可以包含未出現(xiàn)在選擇列表以及 group by 子句中的列或表達式付枫。
Transact-SQL 擴展將行或列添加到顯示中時，或省略 group by 時驰怎，查詢結(jié)果可能難以理解阐滩。
實例解析
One long running SQL in my work. It is a wrong SQL in fact:
- 問題
這個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)
- 排解過程
運行SQL發(fā)現(xiàn)县忌，從Select列表中去掉第二個表dmo_misc..entity_changed的項掂榔，可以得到我們想要的結(jié)果。查閱相關(guān)文檔發(fā)現(xiàn)症杏，如果在select列表包含既不是集合函數(shù)的參數(shù)装获，那么就是Transact-SQL 擴展的group by， 這種方式有時看起來就像一個查詢忽略了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)計信息發(fā)現(xiàn)直接將where后的條件加到having后邊跟先將分組的選出來厉颤，放到放在select的字句里效果是一樣的穴豫，推測兩者在數(shù)據(jù)庫內(nèi)部實現(xiàn)機制是一樣的。
以下為參考資料：
在 select 列表中使用不在 group by 列表中且不包含集合函數(shù)的列是一種Transact-SQL 擴展逼友。 擴展列影響最終結(jié)果的顯示精肃，因為顯示了附加的行。
Transact-SQL 擴展將行或列添加到顯示中時, 查詢結(jié)果可能難以理解帜乞。處理 Transact-SQL 擴展列的方式有時看起來就像一個查詢忽略了where 子句司抱。示例如下：
Adaptive Server 首先使用 where 子句創(chuàng)建一個僅包含類型和集合值的工作表。此工作表通過分組列 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é)果中去除價格低于 $10.00 的行怨喘，就必須添加一個重復(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)
參考文獻：Sybase 參考手冊：命令A(yù)daptive Server? Enterprise 15.0
