1.总计与小计
1.1.with by 进行小计
特点:为明细表创建分类统计,不去除明细,而是在明细后增加一列,by后的字段表示按此类别排序统计,同时可允许多个聚合同时使用。
SELECT last_name AS NAME
,salary_amount AS SALARY
,department_number AS DEPT
FROM employee
WITH SUM (salary) BY DEPT;
返回的报表如下所示:
NAME SALARY DEPT
Stein 29450.00 301
Kanieski 29250.00 301
--------------
Sum(SALARY)58700.00
Johnson 36300.00 401
Trader 37850.00 401
--------------
Sum(SALARY)74150.00
Villegas 49700.00 403
Ryan 31200.00 403
--------------
Sum(SALARY) 80900.00
1.1.1.多聚合计算
SELECT last_name AS NAME
,salary_amount AS SALARY
,department_number AS DEPT
FROM employee
WITH SUM (salary) (TITLE `Dept Total')
,AVG (salary) (TITLE `Dept Avg ') BY DEPT;
NAME SALARY DEPT
Stein 29450.00 301
Kanieski 29250.00 301
--------------
Dept Total 58700.00
Dept Avg 29350.00
Johnson 36300.00 401
Trader 37850.00 401
--------------
Dept Total 74150.00
Dept Avg 37075.00
Villegas 49700.00 403
Ryan 31200.00 403
--------------
Dept Total 80900.00
Dept Avg 40450.00
1.1.2.多层小计
特点:越往后层次越高,类似排在前面的with by是小计,排在后面的是总计,以此类推
SELECT department_number AS Dept
,job_code AS Job
,employee_number AS Emp
,salary_amount AS Sal
FROM employee
WHERE department_number IN (401, 501)
WITH SUM(salary_amount) (TITLE 'Job Total') BY Job
WITH SUM(salary_amount) (TITLE 'DEPT TOTAL') BY Dept;
返回结果如下:
Dept Job Emp Sal
401 411100 1003 37850.00
------------
Job Total 37850.00
401 412101 1004 36300.00
. 159 .
401 412101 1001 25525.00
401 412101 1010 46000.00
------------
Job Total 107825.00
401 412102 1013 24500.00
401 412102 1022 32300.00
------------
Job Total 56800.00
401 413201 1002 43100.00
------------
Job Total 43100.00
------------
DEPT TOTAL 245575.00
1.1.3.with语句产生最后的总计
SELECT employee_number
,salary_amount
FROM employee
WHERE department_number = 301
WITH SUM(salary_amount) (TITLE 'GRAND TOTAL')
ORDER BY employee_number;
返回结果如下:
employee_number salary_amount
1006 29450.00
1008 29250.00
1019 57700.00
-------------
GRAND TOTAL 116400.00
1.1.4.DISTINCT修饰语
SELECT employee_number
,department_number
,manager_employee_number AS manager
FROM employee
WHERE employee_number BETWEEN 1003 AND 1008
WITH COUNT (DISTINCT manager) (TITLE 'TOTAL MANAGERS');
则结果为:
employee_number department_number manager
1006 301 1019
1006 301 1019
1005 403 801
1003 401 801
1007 403 1005
1008 301 1019
1004 401 1003
TOTAL MANAGERS 4
1.1.5.WITH BY, WITH和ORDER BY的联合使用
假设要显示所有雇员的姓及其工资,按部门进行小计,得出最后总计,并要
求按姓排序,则语句为:
SELECT last_name AS NAME
,salary_amount AS SALARY
,department_number AS DEPT
FROM employee
WITH SUM (SALARY) BY DEPT
WITH SUM (SALARY) (TITLE 'GRAND TOTAL')
ORDER BY NAME;
结果如下:
NAME SALARY DEPT
Kanieski 29250.00 301
Stein 29450.00 301
-------------
Sum (SALARY) 58700.00
Johnson 36300.00 401
Trader 37850.00 401
-------------
Sum (SALARY) 74150.00
. 163 .
Ryan 31200.00 403
Villegas 49700.00 403
-------------
Sum (SALARY) 80900.00
-------------
GRAND TOTAL 213750.00
1.1.6.WITH和GROUP BY的联合使用,利用WITH和GROUP BY可以建立最后的总计
列出每个部门的工资总计和平均值,最后列出整个公司的工资总额和平均
值,则语句如下:
SELECT department_number (TITLE 'dept_no')
,SUM (salary_amount)
,AVG (salary_amount)
FROM employee
GROUP BY department_number
WITH SUM (salary_amount) (TITLE 'GRAND TOTAL')
,AVG (salary_amount) (TITLE '')
ORDER BY department_number;
结果如下:
dept_no SUM (salary_amount) AVG (salary_amount)
301 58700.00 29350.00
401 74150.00 37075.00
403 80900.00 40450.00
--------------------- ----------------------
. 164 .
GRAND TOTAL 213750.00 35635.00
1.1.7.WITH BY和WITH总结
WITH BY WITH都是Teradata扩展的特性,ANSI SQL标准不支持。
WITH BY的基本格式为:
格式:WITH 汇总列表 BY 分类列表ASC/DESC
特点如下:
! 为详细数据列表建立总计和小计行
! 汇总列表可以多于一个字段
! 分类列表可以多于一个字段
! 分类列表的列已经隐含了ORDER BY功能
! WITH. . . BY后的相应字段决定了主要的排序键
! 一个 SQL语句可以有多个WITH BY子句
! 第一个 WITH BY排序层次最低
! ORDER BY用来指定其它附加的子排序,指在某个字段内的进一步排序,
如部门内再按薪资高低来进一步排序。
WITH的基本格式为:
格式:WITH 汇总列表
WITH的特点如下:
! 提供最终或总体的总计
! 汇总列表可以多于一个字段