一套sql面试题的mysql解法

1.表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql

select * from T order by c [desc] limit 20,10

2.表T(a,b,c,d)和表T1(a1,b1,c1,d1)，表T中a字段是T1中的a1的外键，请用T1表中的c1,d1更新表T的c,d.

update T
inner join (select a1,c1,d1 from T1) temp on t.a = temp.a1 
set t.c = temp.c1,t.d = temp.d1

3.已知表ODM_JYBB有year、month和am字段，如下

Year	Month	am
2010	01	1.1
2010	02	1.2
2010	03	1.3
2010	04	1.4
2011	01	2.1
2011	02	2.2
2011	03	2.3
2011	04	2.4

通过SQL查询转换成如下格式

Year	M01	M02	M03	M04
2010	1.1	1.2	1.3	1.4
2011	2.1	2.2	2.3	2.4

select
temp.Year as Year,
group_concat(case when temp.Month = '01' then temp.am end) as 'M01',
group_concat(case when temp.Month = '02' then temp.am end) as 'M02',
group_concat(case when temp.Month = '03' then temp.am end) as 'M03',
group_concat(case when temp.Month = '04' then temp.am end) as 'M04'
from ODM_JYBB temp
group by Year

4.通过SQL查询当前时间,显示格式如下所示

日期(data)	日期(string)	星期	月份	第几周	是否工作日
2019-10-10 10:10:10	2019-10-10 10:10:10	五	2019-10	41	是

select now() as '日期(date)',
date_format(now(),'%Y-%m-%d %T') as '日期(string)',
CASE dayofweek(now()) 
when '1' then '日'
when '2' then '一'
when '3' then '二'
when '4' then '三'
when '5' then '四'
when '6' then '五'
when '7' then '六'
else '' end
 as '星期',
date_format(now(),'%Y-%m') as '月份',
EXTRACT(week from now()) as '第几周',
if(dayofweek(ADDDATE(now(),0))%7=1 or dayofweek(ADDDATE(now(),0))%7=0,"否","是") as 是否工作日

5.一个叫 team 的表，里面只有一个字段 name, 一共有 4 条纪录，分别是 a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条 sql 语句显示所有可能的比赛组合. 

select
    t1.name as name01,
    t2.name as name02 
from
    team t1
    inner join team t2 on t1.name < t2.name 
order by
    t1.`name`