sql concat函数_三、SQL面试题错题整理

最新推荐文章于 2023-04-15 14:02:45 发布

weixin_39985279

最新推荐文章于 2023-04-15 14:02:45 发布

阅读量389

点赞数

一、sqlzoo

1-1 函数

1、round

1）查找南美洲（South America）的国家名称，人口数以百万表示（1后面6个0）， gpd以十亿表示（1后面9个0）使用ROUND函数将值显示为两位小数

select name,round(population/1000000,2),round(gdp/1000000000,2) 
from world
where continent='South America';

2）查找gdp大于1万亿（100亿，即12个零）的国家的名称和人均GDP。并将人均DGP的值舍入到最接近的1000

SELECT name,round(gdp/population,-3)
from world
WHERE gdp>1000000000000

2、concat函数

1）显示所有国家名字，其首都是国家名字加上”City”。

SELECT name
 FROM world
 WHERE capital=concat(name, ' City')

2）所有首都和其国家名字，并且首都要有国家名字出现

SELECT capital,name
 FROM world
 WHERE capital Like concat('%',name,'%')

3、replace函数

函数用法

REPLACE(String,from_str,to_str)

1）显示国家名字、延伸词，如首都是国家名字的延伸。

SELECT name,replace(capital,name,'')
FROM world
WHERE capital LIKE concat(name,'_%')

4、In函数

1）把1984年获奖者和主题，按主题和获胜者名称排序, 并把化学奖和物理奖排到最后面显示

SELECT winner,subject
FROM nobel
ORDER BY subject IN ('Chemistry','Physics'),subject,winner

5、concat函数

1）查找欧洲的国家名称和每个国家的人口，其中人口以德国人口的百分比来显示人口数

SELECT name,concat(round(100*population/(SELECT population FROM world WHERE name='Germany'),0),'%')
FROM world
WHERE continent='Europe'

6、ANY、ALL函数

1）世界上所有gdp大于欧洲任何一个国家gdp的国家

SELECT name
FROM world
WHERE gdp >
ALL(SELECT gdp FROM world WHERE continent='Europe' AND gdp>0)

notes：

需要对gdp没有填写的条件进行限制

2)列出洲份名称和国家名称，其中每个洲只取出一个国家（条件：该国家排序在这个洲的首位）

SELECT continent,name 
FROM world w1
WHERE name <= ALL(
SELECT name 
FROM world w2
WHERE w1.continent=w2.continent
)

3）有些國家的人口是同洲份的所有其他國的3倍或以上。列出國家名字name 和洲份continent

SELECT name,continent
FROM world w1
WHERE population> ALL(
SELECT 3*population
FROM world w2
WHERE w1.continent=w2.continent AND w1.name!= w2.name
)

二、CASE表达式

1）第一列显示name；在第二列显示name，但是当name是North America时，使用'Caribbean'代替'North America'，否则name不变。查询条件是name以'N'开头

SELECT name, 
  CASE
  WHEN continent='Oceania' THEN 'Australasia'
  ELSE continent 
  END
FROM world  WHERE name LIKE 'N%';

二、猴子Live

2-1 关联子查询

1）查找出每个课程中大于对应课程平均成绩的学生

SELECT 学号,课程号,成绩
FROM score s1
WHERE 成绩>	(
		SELECT AVG(成绩)
		FROM score s2
		WHERE s1.课程号=s2.课程号
		GROUP BY 课程号
)

2-2 多表查询

1、查询所有学生的学号、姓名、选课数、总成绩

SELECT s.`学号`,s.`姓名`,COUNT(DISTINCT(sc.成绩)) AS 选课数,SUM(sc.成绩) AS 平均成绩
FROM student AS s LEFT JOIN score AS sc
ON sc.`学号`=s.`学号`
GROUP BY s.学号

2-3 topN问题

1、分组取每组最大值：按课程号分组取成绩最大值所在行的数据

SELECT *
FROM score s1
WHERE `成绩`=(
	SELECT max(`成绩`)
	FROM score AS s2
	WHERE s1.`课程号`=s2.课程号
	GROUP BY 课程号
)
ORDER BY `课程号`

2、分组取每组最小值：按课程号分组取成绩最小值所在行的数据

SELECT *
FROM score s1
WHERE 成绩 =(
	SELECT min(`成绩`)
	FROM score s2
	WHERE s1.`课程号`=s2.`课程号`
	GROUP BY `课程号`
) 
ORDER BY `课程号`

3、按照课程号查询每门课程分数最高的成绩TOP2的信息

(SELECT * FROM score WHERE `课程号`='0001' ORDER BY 成绩 DESC LIMIT 2)
UNION
(SELECT * FROM score WHERE `课程号`='0002' ORDER BY 成绩 DESC LIMIT 2)
UNION
(SELECT * FROM score WHERE `课程号`='0003' ORDER BY 成绩 DESC LIMIT 2)

topN问题详解：

sql面试题：topN问题mp.weixin.qq.com

2-4 行列互换

原始数据：

原始数据

行列互换

SQL行列互换详解：

sql面试题：行列如何互换？mp.weixin.qq.com

2-5 提高SQL查询效率

1）从列的选取上限制

select子句中尽量避免使用*

需要使用哪些列的数据，在查询的时候指定哪些列的列名。

2）从行的选取上限制

使用limit子句限制返回的数据行数

3）避免全表扫描

尽量避免使用in和not in

in、not in会导致数据库进行全表搜索，增加运行时间。

尽量避免使用or

or 同样会导致数据库进行全表搜索。使用UNION代替OR

4）避免出现大规模运算

where子句比较符号的左侧避免使用表达式或函数

三、SQL面试经典45题

1、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

设每个科目百分比总和为100%

SELECT sc.CId AS 课程编号,
	c.Cname AS 课程名,
	CONCAT(ROUND(SUM(CASE WHEN (sc.score<=100 AND sc.score>=85) THEN 1 ELSE 0 END)/COUNT(1)*100,2),"%") AS '[100-85]',
	CONCAT(ROUND(SUM(CASE WHEN (sc.score<85   AND sc.score>=70) THEN 1 ELSE 0 END)/COUNT(1)*100,2),"%")   AS '(85-70]',
	CONCAT(ROUND(SUM(CASE WHEN (sc.score<70   AND sc.score>=60) THEN 1 ELSE 0 END)/COUNT(1)*100,2),"%")   AS '(70-60]',
	CONCAT(ROUND(SUM(CASE WHEN (sc.score<60   AND sc.score>0  ) THEN 1 ELSE 0 END)/COUNT(1)*100,2),"%")     AS '(60-0]'
FROM sc INNER JOIN course AS c  ON c.CId=sc.CId
GROUP BY sc.CId

2、查询学生的总成绩，并进行排名，总分重复时保留名次空缺

-- 按各科成绩进行排序，并显示排名， Score 重复时合并名次      (CASE
      WHEN @lastScore=a.总成绩 THEN @rank=NULL
      WHEN @lastScore:=a.总成绩 THEN @rank:=@rank+1
      END)AS 排名
FROM
	(SELECT  SId AS 学号,
		 SUM(score) AS 总成绩
		 FROM sc
		 GROUP BY SId
		 ORDER BY 总成绩 DESC
	) AS a
INNER JOIN
(SELECT @rank:=0,@lastscore:=null) AS b

参考文章

啦啦啦：SQL之常见面试题篇zhuanlan.zhihu.com

mysql45道练习题_哔哩哔哩 (゜-゜)つロ干杯~-bilibiliwww.bilibili.com

12.3 日整理