# 几道比较难的SQL题

## 上条记录和下一条记录

(SELECT * FROM article WHERE create_time<now_article_time ORDER BY create_time DESC LIMIT 1)
UNION
(SELECT * FROM article WHERE create_time>now_article_time ORDER BY create_time ASC LIMIT 1)

## 显示行号

Select a.*,(@rowNum:=@rowNum+1) as rowNo
From a,(Select (@rowNum :=3) ) b

## MySQL打印学生成绩单

1 2 3
3 4 10
10 2 8
20 5 1
7 4 8
10 8 2
10 7 2

SELECT 1 + (SELECT COUNT(1)  FROM
stu AS y WHERE
y.chinese + y.math + y.english > x.chinese + x.math + x.english) AS rank,
(x.chinese + x.math + x.english) AS sum_score
FROM
stu AS x ORDER BY sum_score DESC;

case 表达式 when 取值1 then 答案1 when 取值2 then 答案2 else 答案3 end

SELECT  rank, sum_score FROM
(SELECT
IF(@last_sum = x.chinese + x.math + x.english, @rank, @rank:=@rank + 1) AS rank,
(x.chinese + x.math + x.english) AS sum_score,
(SELECT @last_sum:=x.chinese + x.math + x.english) AS yy
FROM
stu AS x, (SELECT @last_sum:=- 1, @rank:=0) AS nothing
ORDER BY sum_score DESC) AS haha;

1、通过创建临时表、两重select来只选中某些列
2、在where子句中执行变量的初始化
3、在select子句中执行变量的更新
4、通过定义变量last_sum来记录上一条记录 的信息
5、通过比较上一条记录的总分跟当前记录的总分来决定是否让rank增加

## SQL中的case语句

case [input_expression]
when when_expression then result_expression
[...n]
[else else_result_expression]
end

1、demo1：case后带表达式

select *,
case sgroup
when 1 then N'组1'
when 2 then N'组2'
when 3 then N'组3'
else N'未知' end groupname
from @stuinfo

2、demo2：case后不带表达式

select *,
case
when sgroup = 1 and gender = 'm' then N'第一组男生'
when sgroup = 1 and gender = 'f' then N'第一组女生'
when sgroup = 2 and gender = 'm' then N'第二组男生'
when sgroup = 2 and gender = 'f' then N'第二组女生'
when sgroup = 3 and gender = 'm' then N'第三组男生'
when sgroup = 3 and gender = 'f' then N'第三组女生'
else N'未知' end comment
from @stuinfo

3、demo3：在order by子句中使用case语句

select * from @stuinfo
order by
case when @orderby = 1 then id end desc,
case when @orderby = 2 then id end

## SQL语句实现数据库表的数据迁移

insert into standard(列名,列名,列名...is_force)
select 列名,列名,列名...,true from force_standard union
select 列名,列名,列名...,false from recommend_standard