SQL练习题（4），持续更新中

声明：所有题目均是百度搜索，再由自己整理出来的，并非自己出的题。

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name	course	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

解法一：

每门课都大于80分，也就是说学生的所有课的最小分数大于80分MIN（score）>80再根据名称进行排序，HAVING在分组的后的结果种进行筛选

SELECT NAME FROM `testscore` GROUP BY NAME HAVING MIN(score)>80;

解法二：

先查出每个学生的姓名和每门课程的最小分的结果集

再在查出的结果集的基础上查询姓名，并使用大于80分的条件进行筛选

SELECT NAME FROM (
SELECT NAME, MIN(score) AS msc FROM `testscore` GROUP BY NAME) AS t1 
WHERE t1.msc>80;

解法三：

先查出有任一门课程分数小于80分的学生姓名的结果集

再排除掉上述结果集里的姓名，便查出大于80分的学生姓名，需用DISTINCT去重

SELECT DISTINCT NAME FROM testscore WHERE NAME NOT IN (
SELECT NAME FROM `testscore` WHERE score<80);

解法四：

使用NOT EXISTS特性，不关心子查询中的结果，

如果子查询有返回记录，那么就排除当前记录，

如果子查询没有返回记录，那么就输出当前记录。

SELECT DISTINCT NAME FROM testscore t1 WHERE NOT EXISTS (
SELECT * FROM testscore t2 WHERE t1.name=t2.name AND t2.score<80);

解法五：

将score<80作为单独的一列，满足条件为1，不满足为0

再通过相加可以得到和，满足条件和=0也就是每一门分数都大于80才会得0.

SELECT NAME FROM `testscore` GROUP BY NAME HAVING SUM(score<80)=0;

解法六：

查出所有记录条数

查出大于80分的记录条数

再使用两个记录条数比对，查两个的并集

SELECT * FROM
(SELECT NAME,COUNT(1) AS c1 FROM `testscore` GROUP BY NAME) t1,
(SELECT NAME,COUNT(1) AS c2 FROM `testscore` WHERE score>80 GROUP BY NAME) t2
WHERE t1.name=t2.name AND t1.c1=t2.c2;

解法七：

1.与解法六思路类似，在分组的结果上使用每个学生的分数记录条数和大于80分的记录条数进行比较，相等则说明都每门课程大于80分。

SELECT NAME FROM `testscore` GROUP BY NAME HAVING COUNT(1)=SUM(IF (score>80,1,0));

解法八：

左连接查询，通过连接表查询出t2表中王五的数据为null

再根据条件筛选出t2表中名字为null的

SELECT DISTINCT t1.`name` FROM `testscore` AS t1

LEFT JOIN (SELECT * FROM `testscore` WHERE score<=80) AS t2

ON t1.name=t2.name

WHERE t2.name IS NULL;

（求最高分成绩的学生信息）思路

-- 先找出最高分，查出来的结MAX(score)=100

SELECT MAX(score) FROM `testscore`

-- 再找出所有分数等于最高分的成绩

SELECT * FROM `testscore` WHERE score=100

-- 然后两条语句结合 (不相关子查询)

SELECT * FROM `testscore` WHERE score=(SELECT MAX(score) FROM `testscore`);

（求 每个同学最高分的成绩）

解法一：

SELECT NAME,MAX(score) FROM `testscore` GROUP BY NAME;

解法二：

相关子查询:依赖外部查询的数据，外部查询每执行一次，子查询就执行一次

SELECT * FROM `testscore` t1 WHERE t1.`score`=(
SELECT MAX(score) FROM `testscore` t2 WHERE t2.`name`=t1.`name`);

-- （求大于平均分的成绩信息）

SELECT * FROM `testscore` WHERE score > (SELECT AVG(score) FROM `testscore`);

2. 现有学生表如下:

自动编号 学号 姓名 课程编号 课程名称 分数

1 2005001 张三 0001 数学 69

2 2005002 李四 0001 数学 89

3 2005001 张三 0001 数学 69

删除除了自动编号不同, 其他都相同的学生冗余信息

delete tablename where 自动编号 not in (

    select min( 自动编号)
    from tablename
    group by 学号, 姓名, 课程编号, 课程名称, 分数

)

 

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

表自连结先求出多种比赛组合的笛卡尔积，

再用小于排除重复的项

select a.name, b.name

from team a, team b

where a.name < b.name

 

4. 请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。

请注意：TestDB 中有很多科目，都有1~12月份的发生额。

AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。

数据库名：JcyAudit ，数据集：Select * from TestDB

select a.*

from TestDB a,

    (select Occmonth, max(DebitOccur) as Debit101ccur

    from TestDB

    where AccID='101'

    group by Occmonth) b

where a.Occmonth = b.Occmonth and a.DebitOccur > b.Debit101ccur

 

5. 把 a表 的数据查询出 b表的结果 （行转列）

a表

year	month	amount
1991	1	1.1
1991	2	1.2
1991	3	1.3
1991	4	1.4
1992	1	2.1
1992	2	2.2
1992	3	2.3
1992	4	2.4

b表

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

解法一：子查询

SELECT `year`, 
    (SELECT amount FROM amount m WHERE MONTH=1 AND m.year=amount.year) AS m1,
    (SELECT amount FROM amount m WHERE MONTH=2 AND m.year=amount.year) AS m2,
    (SELECT amount FROM amount m WHERE MONTH=3 AND m.year=amount.year) AS m3,
    (SELECT amount FROM amount m WHERE MONTH=4 AND m.year=amount.year) AS m4
FROM amount GROUP BY YEAR

解法二：CASE语句

SELECT `year`,
SUM(CASE WHEN `month`='1' THEN amount ELSE 0 END) m1,
SUM(CASE WHEN `month`='2' THEN amount ELSE 0 END) m2,
SUM(CASE WHEN `month`='3' THEN amount ELSE 0 END) m3,
SUM(CASE WHEN `month`='4' THEN amount ELSE 0 END) m4
FROM amount  GROUP BY `year` 

解法三：IF语句

SELECT `year`, 
SUM(IF(`month`='1',amount,0)) AS m1,
SUM(IF(`month`='2',amount,0)) AS m2,
SUM(IF(`month`='3',amount,0)) AS m3,
SUM(IF(`month`='4',amount,0)) AS m4
FROM amount GROUP BY `year`

6. 有表A，结构如下：

p_ID p_Num s_id

1 10 01

1 12 02

2 8 01

3 11 01

3 8 03

其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。

请用SQL语句实现将上表中的数据合并，合并后的数据为：

p_ID s1_id s2_id s3_id

1 10 12 0

2 8 0 0

3 11 0 8

其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

select p_id,

    sum(case when s_id=1 then p_num else 0 end) as s1_id,

    sum(case when s_id=2 then p_num else 0 end) as s2_id,

    sum(case when s_id=3 then p_num else 0 end) as s3_id

from myPro group by p_id

7.问题：请用一条SQL语句查询出每个科目平均分最高的班级名称

class表

classID	className
1	一班
2	二班
3	三班
4	四班

score表

studentID	name	classID	subject	grade
STD001	张三	1	数学	85
STD002	李四	2	语文	90
STD003	小红	1	数学	80
STD004	小明	3	语文	88
STD005	小花	3	语文	100
STD006	大黄	2	数学	90
STD007	大哥	2	数学	84

 SELECT  className,`subject`,MAX(average)

 FROM (

 SELECT `subject`, className, AVG(grade) AS average

 FROM score a, class b

 WHERE a.`classID`=b.`classID`

 GROUP BY a.classID,`subject`

) t1

GROUP BY `subject`

HAVING MAX(average);

 

8.请将下表图1数据查询到图2的结果 （列转行）

a表

Name	Chinese	Math	English
张三	85	92	87
李四	96	89	100
王五	91	83	98

  b表

name	course	score
张三	语文	85
张三	英语	87
张三	数学	92
李四	数学	89
李四	语文	96
李四	英语	100
王五	数学	83
王五	语文	91
王五	英语	98

SELECT * FROM (

SELECT NAME, '语文' AS course, Chinese AS score FROM asaa

UNION

SELECT NAME, '数学' AS course, math AS score FROM asaa

UNION

SELECT NAME, '英语' AS course, english AS score FROM asaa

) n

ORDER BY n.name,n.score

9.请将 a 表数据查询到 b 表的结果 （行转列）

a表

name	course	score
张三	语文	85
张三	英语	87
张三	数学	92
李四	数学	89
李四	语文	96
李四	英语	100
王五	数学	83
王五	语文	91
王五	英语	98

b表

Name	Chinese	Math	English
张三	85	92	87
李四	96	89	100
王五	91	83	98

-- 解法一：CASE语句
SELECT NAME,

SUM(CASE course WHEN '语文' THEN score ELSE 0 END) AS Chinese,

SUM(CASE course WHEN '数学' THEN score ELSE 0 END) AS Math,

SUM(CASE course WHEN '英语' THEN score ELSE 0 END) AS English

FROM score GROUP BY NAME;

-- 解法二：IF语句

SELECT NAME,

SUM(IF(course='语文',score,0)) AS Chinese,

SUM(IF(course='数学',score,0)) AS Math,

SUM(IF(course='英语',score,0)) AS English

FROM score GROUP BY NAME;