SQL grouping sets 子句

pingley發表於2012-04-04
SQL
SQL  grouping sets 子句
grouping sets子句允許你指定多個group by 選項。增強了group by 的功能。
可以透過一條select 語句實現複雜繁瑣的多條select 語句的查詢。並且更加的
高效，解析儲存一條SQL於語句。下面透過使用grouping sets 子句與沒有
使用grouping sets 子句來對比學習該子句。
查詢每個部門的每類工作的平均工資，使用grouping sets的語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by grouping sets ((department_id,job_id));
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
          110 AC_ACCOUNT        8300
           80 SA_REP         7909.09091
           90 AD_VP                  17000
           50 ST_CLERK               2785
          110 AC_MGR              12000
           50 ST_MAN                 7280
           80 SA_MAN              12200
           50 SH_CLERK              3215
沒有使用grouping sets 的對等語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by department_id,job_id;
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
          110 AC_ACCOUNT        8300
           80 SA_REP          7909.09091
           90 AD_VP                   17000
           50 ST_CLERK                2785
          110 AC_MGR               12000
           50 ST_MAN                 7280
           80 SA_MAN              12200
           50 SH_CLERK             3215
再查詢每個部門每類工作平均工資的基礎上，還要查詢出每個部門的平均工資。使用grouping sets的語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by grouping sets ((department_id,job_id),department_id);
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
           10 AD_ASST              4400#每個部門每類工作的平均工資。
           10                              4400#每個部門的平均工資。
           20 MK_MAN           13000
           20 MK_REP               6000
           20                             9500
           30 PU_MAN           11000
           30 PU_CLERK           2780
           30                             4150
           40 HR_REP               6500
沒有使用grouping sets 的對等語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by (department_id,job_id)
  4  union
  5  select department_id,null,avg(salary)
  6  from hr.employees
  7  group by department_id;
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
           10 AD_ASST              4400
           10                              4400
           20 MK_MAN           13000
           20 MK_REP               6000
           20                             9500
           30 PU_CLERK           2780
           30 PU_MAN           11000
           30                             4150
           40 HR_REP               6500

SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by grouping sets ((department_id,job_id),department_id,());
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
           10 AD_ASST              4400
           10                              4400
           20 MK_MAN           13000
           20 MK_REP               6000
           20                             9500
           30 PU_MAN           11000
           30 PU_CLERK          2780
再查詢每個部門每類工作平均工資與查詢出每個部門的平均工資的基礎上，還要查詢整個企業中僱員的平均工資。使用grouping sets的語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by grouping sets ((department_id,job_id),department_id,()); #() 表示做一個整體的聚集。
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
           10 AD_ASST              4400
           10                              4400
           20 MK_MAN           13000
           20 MK_REP               6000
           20                             9500
           30 PU_MAN           11000
           30 PU_CLERK           2780
                                              。
                                              。
                                              。
                                 6380.90909 #這一行查詢的是整個employees 中僱員總的平均工資。
沒有使用grouping sets 的對等語句。
SQL> select department_id,job_id,avg(salary)
  2  from hr.employees
  3  group by (department_id,job_id)
  4  union
  5  select department_id,null,avg(salary)
  6  from hr.employees
  7  group by department_id
  8  union 
  9  select null,null,avg(salary)
 10  from hr.employees;
DEPARTMENT_ID JOB_ID     AVG(SALARY)
------------- ---------- -----------
           10 AD_ASST              4400
           10                              4400
           20 MK_MAN           13000
           20 MK_REP               6000
           20                              9500
           30 PU_CLERK            2780
           30 PU_MAN           11000
           30                             4150
           40 HR_REP               6500
                                              。
                                              。
                                              。
                                6380.90909 #這一行查詢的是整個employees 中僱員總的平均工資。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26110315/viewspace-720291/，如需轉載，請註明出處，否則將追究法律責任。

相關文章