查询数据库中第二大的记录的方法
表数据
方法1
SELECT
NAME
FROM
USER
WHERE
age NOT IN (SELECT MAX(age) FROM USER)
ORDER BY age DESC
LIMIT 1;
方法2
SELECT
NAME
FROM
USER
WHERE
age < (SELECT MAX(age) FROM USER)
ORDER BY age DESC
LIMIT 1;
子查询
表数据
- orderlist表数据
- user表
单行单列
-- 结果是单行单列的
-- 查询年龄最高的用户姓名
SELECT NAME,age FROM USER WHERE age=(SELECT MAX(age) FROM USER);
多行多列
-- 结果是多行多列的
-- 查询张三1和张三2的订单信息
SELECT * FROM orderlist WHERE uid IN (1,2);
SELECT id FROM USER WHERE NAME IN ('张三1','张三2');
SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME IN ('张三1','张三2'));
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id > 4;
SELECT
u.name,
o.number
FROM
USER u,
(SELECT * FROM orderlist WHERE id > 4) o
WHERE
u.id = o.uid;