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)