Oracle中的GROUP BY子句用于將查詢結(jié)果按照一個(gè)或多個(gè)列進(jìn)行分組,以便對(duì)每個(gè)組執(zhí)行聚合函數(shù)(如COUNT、SUM、AVG等)
SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE conditions
GROUP BY column1, column2;
使用GROUP BY子句時(shí),SELECT子句中只能包含以下內(nèi)容:
使用HAVING子句過(guò)濾分組結(jié)果:
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 10;
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY ROLLUP (column1, column2);
這將首先按照column1和column2進(jìn)行分組,然后按照column1進(jìn)行分組,最后對(duì)所有記錄進(jìn)行匯總。
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY CUBE (column1, column2);
這將按照column1和column2的所有可能組合進(jìn)行分組和匯總。
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY GROUPING SETS ((column1, column2), (column1), (column2), ());
這將按照column1和column2進(jìn)行分組,然后分別按照column1和column2進(jìn)行分組,最后對(duì)所有記錄進(jìn)行匯總。
與其他子句一起使用GROUP BY:
優(yōu)化GROUP BY查詢性能:
總之,熟練掌握Oracle中的GROUP BY子句及其相關(guān)技巧,可以幫助你更高效地處理數(shù)據(jù)和編寫SQL查詢。