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)
  14. > SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
  15. +------+------+--------+
  16. | sc1 | sc2 | sc3 |
  17. +------+------+--------+
  18. | 2 | 2 | 6.0000 |
  19. | 3 | 3 | 9.0000 |
  20. +------+------+--------+
  21. 2 rows in set (0.02 sec)
  22. ``````sql
  23. CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
  24. INSERT INTO tb1 VALUES (1, '1', 1.0);
  25. INSERT INTO tb1 VALUES (2, '2', 2.0);
  26. INSERT INTO tb1 VALUES (3, '3', 3.0);
  27. mysql> select * from tb1;
  28. +------+------+--------+
  29. | c1 | c2 | c3 |
  30. +------+------+--------+
  31. | 1 | 1 | 1.0000 |
  32. | 2 | 2 | 2.0000 |
  33. | 3 | 3 | 3.0000 |
  34. +------+------+--------+
  35. 3 rows in set (0.03 sec)
  36. mysql> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
  37. +------+------+--------+
  38. | sc1 | sc2 | sc3 |
  39. +------+------+--------+
  40. | 2 | 2 | 6.0000 |
  41. | 3 | 3 | 9.0000 |
  42. +------+------+--------+
  43. 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');
  64. 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 ;
  65. +----------+--------------------+------+
  66. | city | libname1 | a |
  67. +----------+--------------------+------+
  68. | NewYork | NewYorkPublicLibra | 6 |
  69. | SanFran | SanFransiscoPublic | 1 |
  70. | Berkeley | BerkeleyPublic1 | 1 |
  71. | Berkeley | BerkeleyPublic2 | 1 |
  72. +----------+--------------------+------+
  73. 4 rows in set (0.00 sec)

限制

MatrixOne 暂不支持选择多列进行子查询。