CREATE TABLE `score` (
  `sid` int(10) NOT NULL,
  `username` varchar(10) NOT NULL,
  `subject` varchar(10) NOT NULL,
  `score` int(3) DEFAULT NULL,
  PRIMARY KEY (`sid`,`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**

编写的sql考虑到score为空的情况

**

计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

SELECT a.username,a.totalScore FROM 
(SELECT username,SUM(score) as totalScore FROM score GROUP BY sid) a 
ORDER BY totalScore DESC;

计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

SELECT a.sid,a.score,a.username,a.subject FROM score a 
JOIN (SELECT sid,MAX(score) as maxScore FROM score GROUP BY sid) b 
ON a.sid = b.sid AND a.score = b.maxScore;

计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）并查出平均成绩大于80的同学 （提示：having相当于过滤）

SELECT a.sid,a.sumScore/b.num AS avgScore,a.username FROM 
(SELECT sid,SUM(score) as sumScore,username FROM score GROUP BY sid,username) a 
JOIN (SELECT COUNT(*) AS num,sid FROM score GROUP BY sid) b 
ON a.sid = b.sid HAVING avgScore > 80;

SELECT sid,AVG(score),username FROM score GROUP BY sid,username; （当成绩为空时gg）

列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

SELECT a.sid,a.username,a.subject,a.score FROM score a 
JOIN (SELECT max(score) as maxScore,subject FROM score GROUP BY subject) b 
ON a.score=b.maxScore AND a.subject=b.`subject`;

列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

SELECT a.* FROM score a WHERE (SELECT COUNT(*) FROM score b 
WHERE b.subject = a.subject AND b.score > a.score) < 2 AND a.score IS NOT NULL
ORDER BY a.score DESC;

查看所有科目均超过60分的同学

SELECT username,sid FROM 
(SELECT username,sid FROM score where score > 60) a 
GROUP BY sid HAVING COUNT(*) = 3;

统计如下 学号 姓名 语文 数学 英语 总分 平均分

SELECT * FROM (SELECT sid AS 学号,username AS 姓名,
SUM(CASE WHEN subject = '语文' THEN score ELSE 0 END) as 语文,
SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) as 数学,
SUM(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS 英语,
SUM(score) AS 总分,SUM(score)/3 AS 平均分 FROM score 
GROUP BY sid) a ORDER BY 总分 DESC;

列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

SELECT sum(score)/(SELECT COUNT(*) FROM 
(SELECT DISTINCT sid FROM score GROUP BY sid) a) as avgScore, subject FROM score   
GROUP BY subject;

列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）排名前2名

select sid,username,score,
(select count(*) from score a where subject ='数学' and a.score > b.score)+1 as orders from score b 
where subject='数学'  order by score desc limit 0,2；

如果存在重复的数学成绩 则需要去数学成绩进行去重比较（上述假设排名为 1 1 3 4 下述排名 1 1 2 3）

select sid,username,score,
 (select count(distinct a.score) from score a where subject ='数学' and a.score > b.score)+1 as orders from score b 
where subject='数学' order by score desc limit 0,2；

统计如下： 课程 不及格 良 优 个数

SELECT subject,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS 不及格,
SUM(CASE WHEN score BETWEEN 60 AND 80 THEN 1 ELSE 0 END) AS 良,
SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) AS 优 FROM score 
GROUP BY subject;

某家SQL笔试题

CREATE TABLE branch (
branch_name varchar(20) NOT NULL,
branch_city varchar(20) DEFAULT NULL,
PRIMARY KEY (branch_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE account (
account_name varchar(20) NOT NULL,
branch_name varchar(20) DEFAULT NULL,
balance double(20,2) DEFAULT NULL,
PRIMARY KEY (account_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE customer (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
customer_name varchar(50) NOT NULL COMMENT ‘名称’,
customer_street varchar(20) NOT NULL COMMENT ‘街道’,
customer_city varchar(20) NOT NULL COMMENT ‘城市’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE depasitor (
account_name varchar(20) NOT NULL,
customer_name varchar(20) DEFAULT NULL,
PRIMARY KEY (account_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上海所有支行存款最多的支行信息

SELECT b.branch_name,b.branch_city,MAX(balance) FROM 
(SELECT branch_name,branch_city FROM branch WHERE branch_city = 'shanghai') as b 
JOIN (SELECT branch_name,SUM(balance) as balance FROM account 
GROUP BY branch_name) as a ON a.branch_name = b.branch_name;

用sql查询在上海所有支行都开过户的用户信息？（branch_city=shanghai）

SELECT * FROM customer WHERE customer_name in (
SELECT z.customer_name FROM 
(SELECT b.branch_name,d.customer_name FROM 
(SELECT branch_name FROM branch WHERE branch_city = 'shanghai') b
JOIN account a ON b.branch_name = a.branch_name
JOIN depasitor d ON d.account_name = a.account_name 
GROUP BY b.branch_name,d.customer_name) z 
GROUP BY customer_name HAVING COUNT(*) = (SELECT COUNT(*) FROM branch WHERE branch_city = 'shanghai')); 

用sql查询在杨浦支行只开过一个账户的用户信息？（branch_name=yangpu）

SELECT * FROM customer WHERE customer_name in (
SELECT d.customer_name FROM 
(SELECT branch_name FROM branch WHERE branch_name = 'yangpu') b 
JOIN account a ON a.branch_name = b.branch_name
JOIN depasitor d ON d.account_name = a.account_name 
GROUP BY customer_name HAVING COUNT(*) = 1);