SQL查询排名第二名的信息
- SQL Server
select top 1 * from
(select top 2 from TABLE order by COLUMN desc)
order by COLUMN asc
- Oracle (特别喜欢用别名)
select * from
(select t.*, rownum r from
(select * from TABLE order by COLUMN desc) t
where r <= 2) A
where A.r >= 2
开窗函数row_number()
select * from
(select t.*, row_number() over(order by COLUMN desc) sort from TABLE) t
Where t.sort = 2
- MySQL
select * from TABLE order by COLUMN limit 1, 1
求数量例题
求某一个列的属性值出现多次,其实是COUNT函数简单的使用例子。
给出了一个city表,包括id
,cityname
,求重复的城市以及数量。
SELECT cityname, COUNT(*) FROM city
GROUP BY cityname
HAVING COUNT(*) >= 2
从多张表中查询相同字段所包含的不同信息
有两个表,一个表是城市,省,另一个表是城市,人口。查找每个省的人口
SELECT cityname, province, citypopulation
FROM city1, city2
WHERE city1.cityname = city2.cityname
-- 实际上是内连接,等价于
SELECT cityname, province, citypopulation
FROM city1 INNER JOIN city2 ON city1.cityname = city2.cityname
-- 自然连接会去重
SELECT cityname, province, citypopulation
FROM city1 NATURAL JOIN city ON city1.cityname = city2.cityname
-- 补充知识
-- 内连接要求两个表的相关字段都满足连接条件
-- 包括三种 等值连接 不等连接 自然连接
-- 与此对应 有外连接 实际上我们通常所说的左连接 右连接 全连接 都是指外连接
-- 只不过因为只有外连接有着三种情况 关键字 OUTER 可以省略
-- 例子如下:
-- 查询学生表和学生选课表
-- 只有学生选了课 才会在学生选课表中有记录
-- 现在我需要看所有学生的选课信息 包括哪些还没有选课的
-- 左外连接
SELECT Sno, Sname, Class, Cno
FROM student s LEFT JOIN studnetcourse sc -- FROM studnet s LEFT OUTER JOIN
ON s.Sno = sc.Sno
-- 右外连接
SELECT Sno, Sname, Class, Cno
FROM student s RIGHT JOIN studentcourse sc
ON s.Sno = sc.Sno
-- 全外连接
SELECT Sno, Sname, Class, Cno
FROM student s FULL OUTER JOIN studentcourse sc
On s.Sno = sc.Sno
-- 交叉连接 笛卡尔积 一般无意义
SELECT *
FROM s CROSS JOIN sc
在实际应用中,我们应该尽量避免使用连接,因为查询代价比较高。因此,我们应该将人口信息直接放到city1表。