DB2 730学习笔记6
使用联结从多个表中检索数据联结join是一种将来自两个或更多表中的数据组合起来的查询。
查询标识出哪些是经理的职员
select * from org
DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
-------- -------------- ------- ---------- -------------
10 Head Office 160 Corporate New York
15 New England 50 Eastern Boston
20 Mid Atlantic 10 Eastern Washington
38 South Atlantic 30 Eastern Atlanta
42 Great Lakes 100 Midwest Chicago
51 Plains 140 Midwest Dallas
66 Pacific 270 Western San Francisco
84 Mountain 290 Western Denver
8 record(s) selected.
select * from staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20 Sales 8 78171.25 612.45
30 Marenghi 38 Mgr 5 77506.75 -
40 O'Brien 38 Sales 6 78006.00 846.55
50 Hanes 15 Mgr 10 80659.80 -
60 Quigley 38 Sales - 66808.30 650.25
70 Rothman 15 Sales 7 76502.83 1152.00
80 James 20 Clerk - 43504.60 128.20
90 Koonitz 42 Sales 6 38001.75 1386.70
100 Plotz 42 Mgr 7 78352.80 -
110 Ngan 15 Clerk 5 42508.20 206.60
120 Naughton 38 Clerk - 42954.75 180.00
130 Yamaguchi 42 Clerk 6 40505.90 75.60
140 Fraye 51 Mgr 6 91150.00 -
150 Williams 51 Sales 6 79456.50 637.65
160 Molinare 10 Mgr 7 82959.20 -
170 Kermisch 15 Clerk 4 42258.50 110.10
180 Abrahams 38 Clerk 3 37009.75 236.50
190 Sneider 20 Clerk 8 34252.75 126.50
200 Scoutten 42 Clerk - 41508.60 84.20
210 Lu 10 Mgr 10 90010.00 -
220 Smith 51 Sales 7 87654.50 992.80
230 Lundquist 51 Clerk 3 83369.80 189.65
240 Daniels 10 Mgr 5 79260.25 -
250 Wheeler 51 Clerk 6 74460.00 513.30
260 Jones 10 Mgr 12 81234.00 -
270 Lea 66 Mgr 9 88555.50 -
280 Wilson 66 Sales 9 78674.50 811.50
290 Quill 84 Mgr 10 89818.00 -
300 Davis 84 Sales 5 65454.50 806.10
310 Graham 66 Sales 13 71000.00 200.30
320 Gonzales 66 Sales 4 76858.20 844.00
330 Burke 66 Clerk 1 49988.00 55.50
340 Edwards 84 Sales 7 67844.00 1285.00
350 Gafney 84 Clerk 5 43030.50 188.00
35 record(s) selected.
联结查询（内联结查询，返回差集中满足联结条件的行）
SELECT deptnumb, deptname, id AS manager_id, name AS manager
FROM org, staff WHERE manager = id ORDER BY deptnumb
也可以写为:
SELECT deptnumb,deptname,id AS manager_id,name AS manager
FROM org INNER JOIN staff ON manager = id ORDER BY deptnumb
DEPTNUMB DEPTNAME MANAGER_ID MANAGER
-------- -------------- ---------- ---------
10 Head Office 160 Molinare
15 New England 50 Hanes
20 Mid Atlantic 10 Sanders
38 South Atlantic 30 Marenghi
42 Great Lakes 100 Plotz
51 Plains 140 Fraye
66 Pacific 270 Lea
84 Mountain 290 Quill
8 record(s) selected.
外部联结Outer join返回内部联结操作产生的行，加上内部联结操作不会返回的行。
有三种类型的外部联结：
左外部联结Left outer join或Left join包括内部联结，加上左表中内部联结操作不会返回的行。
SELECT deptnumb,deptname,id AS manager_id,name AS manager FROM org LEFT JOIN staff ON manager = id ORDER BY deptnumb
DEPTNUMB DEPTNAME MANAGER_ID MANAGER
-------- -------------- ---------- ---------
10 Head Office 160 Molinare
15 New England 50 Hanes
20 Mid Atlantic 10 Sanders
38 South Atlantic 30 Marenghi
42 Great Lakes 100 Plotz
51 Plains 140 Fraye
66 Pacific 270 Lea
84 Mountain 290 Quill
8 record(s) selected.
右外部联结Right outer join或Rigth join包括内部联结，加上右表中内部联结操作不会返回的行。
SELECT deptnumb,deptname,id AS manager_id,name AS manager FROM org RIGHT JOIN staff ON manager = id ORDER BY deptnumb
DEPTNUMB DEPTNAME MANAGER_ID MANAGER
-------- -------------- ---------- ---------
10 Head Office 160 Molinare
15 New England 50 Hanes
20 Mid Atlantic 10 Sanders
38 South Atlantic 30 Marenghi
42 Great Lakes 100 Plotz
51 Plains 140 Fraye
66 Pacific 270 Lea
84 Mountain 290 Quill
- - 20 Pernal
- - 40 O'Brien
- - 60 Quigley
- - 70 Rothman
- - 80 James
- - 90 Koonitz
- - 110 Ngan
- - 120 Naughton
- - 130 Yamaguchi
- - 150 Williams
- - 170 Kermisch
- - 180 Abrahams
- - 190 Sneider
- - 200 Scoutten
- - 210 Lu
- - 220 Smith
- - 230 Lundquist
- - 240 Daniels
- - 250 Wheeler
- - 260 Jones
- - 280 Wilson
- - 300 Davis
- - 310 Graham
- - 320 Gonzales
- - 330 Burke
- - 340 Edwards
- - 350 Gafney
35 record(s) selected.
完全外部联结Full outer join或Full join包括内部联结，加上左表和右表中联结操作不会返回的行。
使用UNION集合操作符将两个或更多的查询组合成一个查询。
SELECT sales_person FROM sales WHERE region = 'Ontario-South'
SALES_PERSON
---------------
LUCCHESSI
LEE
LUCCHESSI
LEE
GOUNOT
LUCCHESSI
LEE
GOUNOT
LEE
GOUNOT
LUCCHESSI
LEE
GOUNOT
13 record(s) selected.
SELECT sales_person FROM sales WHERE sales > 3
SALES_PERSON
---------------
LEE
GOUNOT
LEE
LEE
LEE
GOUNOT
LEE
LEE
LEE
LEE
LEE
GOUNOT
12 record(s) selected.
SELECT sales_person FROM sales
WHERE region = 'Ontario-South'
UNION
SELECT sales_person FROM sales
WHERE sales > 3
SALES_PERSON
---------------
GOUNOT
LEE
LUCCHESSI
3 record(s) selected.
EXCEPT集合操作符生成的结果表中包含第一个查询返回的所有行，但是去掉了第二个任何后续查询返回的行。
SELECT sales_person FROM sales
WHERE region = 'Ontario-South'
EXCEPT
SELECT sales_person FROM sales
WHERE sales > 3
SALES_PERSON
---------------
LUCCHESSI
1 record(s) selected.
INTERSECT集合操作符生成的结果表只包含所有查询都返回的行。
SELECT sales_person FROM sales
WHERE region = 'Ontario-South'
INTERSECT
SELECT sales_person FROM sales
WHERE sales > 3
SALES_PERSON
---------------
GOUNOT
LEE
2 record(s) selected.
使用GROUP BY 子句对结果进行总结
使用GROUP BY 自己对结果集中的行进行组织。每个组在结果集中由一行表示。
SELECT sales_date,MAX(sales) AS max_sales FROM sales GROUP BY sales_dateSALES_DATE
SALES_DATE MAX_SALES
---------- -----------
03/29/1996 2
12/31/2005 3
03/29/2006 7
03/30/2006 18
03/31/2006 14
04/01/2006 9
GROUPING SETS子句，分组集可以用来在一遍分析不同聚合层次上的数据。
YEAR函数返回日期值的年份；
SUM函数用来返回每个分组的销售区间的总量；
分组集列表指定如何对数据进行分组；
在分组集列表中添加一对空的圆括号，可以获得结果集中的总量。
SELECT YEAR(sales_date) AS year,region,SUM(sales) AS total_sale FROM sales GROUP BY GROUPING SETS (YEAR(sales_date),region,())
YEAR REGION TOTAL_SALE
----------- --------------- -----------
- - 155
- Manitoba 41
- Ontario-North 9
- Ontario-South 52
- Quebec 53
1996 - 2
2005 - 8
2006 - 145
8 record(s) selected.
HAVING子句与GROUP BY 子句一起使用，检索出满足特定条件的组的结果。
返回销售总量超过25的销售人员。
SELECT sales_person, SUM(sales) AS total_sales FROM sales
GROUP BY sales_person
HAVING SUM(sales) > 25
SALES_PERSON TOTAL_SALES
--------------- -----------
GOUNOT 50
LEE 91
2 record(s) selected.
页:
[1]