SQLite SELECT 语句中的子查询 - SQLite 基础教程

SQLite 子查询通常与 SELECT 语句一起使用

语法如下

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

例如语句

SELECT * FROM tbl_employee WHERE age IN 
  (SELECT age FROM tbl_age WHERE age > 27 );

用于筛选出 tbl_employee 中的年龄在 tbl_age 中大于 27 岁的那些记录

范例

先运行下面的 SQL 语句创建测试数据

有两张表，一张是职工表，一张是年龄表

DROP TABLE tbl_employee;
DROP TABLE tbl_age;

CREATE TABLE tbl_employee (
   id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,
   name           CHAR(64) NOT NULL,
   age            INTEGER  NOT NULL,
   city           CHAR(64),
   salary         REAL
);


CREATE TABLE tbl_age (
   id      INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,
   age  INTEGER  NOT NULL
);


INSERT INTO tbl_employee (name,age,city,salary) VALUES
('张三', 25, 'pek', 102400.00 ),
('李四', 18, 'shanghai', 88888.00 ),
('王五', 22, 'hangzhou', 22000.00 ),
('孙六', 32, 'pek', 32000.00 ),
('钱七', 25, 'shanghai', 25000.00 ),
('赵二', 45, 'pek', 45800.00 ),
('冯九', 37, 'chengdu', 15000.00 );

INSERT INTO tbl_age (age) VALUES
(10),
(15),
(28),
(35),
(37);

然后就可以使用下面的语句筛选出 tbl_employee 中的年龄在 tbl_age 中大于 27 岁的那些记录

SELECT * FROM tbl_employee WHERE age IN 
  (SELECT age FROM tbl_age WHERE age > 27 );

语句输出结果如下

id          name        age         city        salary    
----------  ----------  ----------  ----------  ----------
7           冯九          37          chengdu     15000.0