Derived Tables

Derived Tables

语法描述

当 SELECT 语句的 FROM 从句中使用独立子查询时，我们也经常将其称为派生表，因为实际上外部查询将子查询的结果当作了一个数据源。

语法结构

每个 FROM 子查询的表都必须要有一个名字，因此 [AS] 操作符是必须的。子查询的 SELECT 列表中每个列也必须要有一个唯一的名字。

1. > SELECT ... FROM (subquery) [AS] name ...

示例

1. > CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
2. > INSERT INTO tb1 VALUES (1, '1', 1.0);
3. > INSERT INTO tb1 VALUES (2, '2', 2.0);
4. > INSERT INTO tb1 VALUES (3, '3', 3.0);
5. > select * from tb1;
6. +------+------+--------+
7. | c1   | c2   | c3     |
8. +------+------+--------+
9. |    1 | 1    | 1.0000 |
10. |    2 | 2    | 2.0000 |
11. |    3 | 3    | 3.0000 |
12. +------+------+--------+
13. 3 rows in set (0.03 sec)

15. > SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
16. +------+------+--------+
17. | sc1  | sc2  | sc3    |
18. +------+------+--------+
19. |    2 | 2    | 6.0000 |
20. |    3 | 3    | 9.0000 |
21. +------+------+--------+
22. 2 rows in set (0.02 sec)
23. ``````sql
24. CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
25. INSERT INTO tb1 VALUES (1, '1', 1.0);
26. INSERT INTO tb1 VALUES (2, '2', 2.0);
27. INSERT INTO tb1 VALUES (3, '3', 3.0);

29. mysql> select * from tb1;
30. +------+------+--------+
31. | c1   | c2   | c3     |
32. +------+------+--------+
33. |    1 | 1    | 1.0000 |
34. |    2 | 2    | 2.0000 |
35. |    3 | 3    | 3.0000 |
36. +------+------+--------+
37. 3 rows in set (0.03 sec)

39. mysql> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
40. +------+------+--------+
41. | sc1  | sc2  | sc3    |
42. +------+------+--------+
43. |    2 | 2    | 6.0000 |
44. |    3 | 3    | 9.0000 |
45. +------+------+--------+
46. 2 rows in set (0.02 sec)

Subquery with Join:

1. > create table t1 (libname1 varchar(21) not null primary key, city varchar(20));
2. > create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60));
3. > create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int);
4. > insert into t2 values ('001','Daffy','Aducklife');
5. > insert into t2 values ('002','Bugs','Arabbitlife');
6. > insert into t2 values ('003','Cowboy','Lifeontherange');
7. > insert into t2 values ('000','Anonymous','Wannabuythisbook?');
8. > insert into t2 values ('004','BestSeller','OneHeckuvabook');
9. > insert into t2 values ('005','EveryoneBuys','Thisverybook');
10. > insert into t2 values ('006','SanFran','Itisasanfranlifestyle');
11. > insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
12. > insert into t3 values('000','NewYorkPublicLibra',1);
13. > insert into t3 values('001','NewYorkPublicLibra',2);
14. > insert into t3 values('002','NewYorkPublicLibra',3);
15. > insert into t3 values('003','NewYorkPublicLibra',4);
16. > insert into t3 values('004','NewYorkPublicLibra',5);
17. > insert into t3 values('005','NewYorkPublicLibra',6);
18. > insert into t3 values('006','SanFransiscoPublic',5);
19. > insert into t3 values('007','BerkeleyPublic1',3);
20. > insert into t3 values('007','BerkeleyPublic2',3);
21. > insert into t3 values('001','NYC Lib',8);
22. > insert into t1 values ('NewYorkPublicLibra','NewYork');
23. > insert into t1 values ('SanFransiscoPublic','SanFran');
24. > insert into t1 values ('BerkeleyPublic1','Berkeley');
25. > insert into t1 values ('BerkeleyPublic2','Berkeley');
26. > insert into t1 values ('NYCLib','NewYork');
27. > select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ;
28. +----------+--------------------+------+
29. | city     | libname1           | a    |
30. +----------+--------------------+------+
31. | NewYork  | NewYorkPublicLibra |    6 |
32. | SanFran  | SanFransiscoPublic |    1 |
33. | Berkeley | BerkeleyPublic1    |    1 |
34. | Berkeley | BerkeleyPublic2    |    1 |
35. +----------+--------------------+------+
36. 4 rows in set (0.00 sec)
37. ``````sql
38. create table t1 (libname1 varchar(21) not null primary key, city varchar(20));
39. create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60));
40. create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int);
41. insert into t2 values ('001','Daffy','Aducklife');
42. insert into t2 values ('002','Bugs','Arabbitlife');
43. insert into t2 values ('003','Cowboy','Lifeontherange');
44. insert into t2 values ('000','Anonymous','Wannabuythisbook?');
45. insert into t2 values ('004','BestSeller','OneHeckuvabook');
46. insert into t2 values ('005','EveryoneBuys','Thisverybook');
47. insert into t2 values ('006','SanFran','Itisasanfranlifestyle');
48. insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
49. insert into t3 values('000','NewYorkPublicLibra',1);
50. insert into t3 values('001','NewYorkPublicLibra',2);
51. insert into t3 values('002','NewYorkPublicLibra',3);
52. insert into t3 values('003','NewYorkPublicLibra',4);
53. insert into t3 values('004','NewYorkPublicLibra',5);
54. insert into t3 values('005','NewYorkPublicLibra',6);
55. insert into t3 values('006','SanFransiscoPublic',5);
56. insert into t3 values('007','BerkeleyPublic1',3);
57. insert into t3 values('007','BerkeleyPublic2',3);
58. insert into t3 values('001','NYC Lib',8);
59. insert into t1 values ('NewYorkPublicLibra','NewYork');
60. insert into t1 values ('SanFransiscoPublic','SanFran');
61. insert into t1 values ('BerkeleyPublic1','Berkeley');
62. insert into t1 values ('BerkeleyPublic2','Berkeley');
63. insert into t1 values ('NYCLib','NewYork');

65. mysql> select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ;
66. +----------+--------------------+------+
67. | city     | libname1           | a    |
68. +----------+--------------------+------+
69. | NewYork  | NewYorkPublicLibra |    6 |
70. | SanFran  | SanFransiscoPublic |    1 |
71. | Berkeley | BerkeleyPublic1    |    1 |
72. | Berkeley | BerkeleyPublic2    |    1 |
73. +----------+--------------------+------+
74. 4 rows in set (0.00 sec)