SQL题集(三)
2023-09-11 14:22:54 时间
1.将employees表中的所有员工的last_name和first_name通过(')连接起来
数据导入
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
employees表
问题:将employees表中的所有员工的last_name和first_name通过(')连接起来
-- 解法1
SELECT CONCAT_WS("'",last_name, first_name) AS name
FROM employees;
-- 解法2
SELECT CONCAT_WS('\'',last_name, first_name)AS name
FROM employees;结果展示:
2*.查找字符串中逗号出现的次数
数据导入
drop table if exists strings;
CREATE TABLE strings(
id int(5) NOT NULL PRIMARY KEY,
string varchar(45) NOT NULL
);
insert into strings values
(1, '10,A,B'),
(2, 'A,B,C,D'),
(3, 'A,11,B,C,D,E');strings表
id:序列号 string:存放的是字符串，且字符串中仅包含数字、字母和逗号类型的字符
问题:统计每个字符串中逗号出现的次数cnt
-- 解法1
SELECT id,(LENGTH(string)-LENGTH(REPLACE(string,',','')))AS cnt
FROM strings
GROUP BY id;
-- 解法2
SELECT id,(CHAR_LENGTH(string)-CHAR_LENGTH(REPLACE(string,',','')))AS cnt
FROM strings
GROUP BY id;结果展示:
3*.获取employees中的first_name
数据导入
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
employees表
问题:检索出employees表中的first_name，并按照first_name最后两个字母升序进行输出
SELECT first_name
FROM employees
ORDER BY SUBSTRING(first_name,-2,2) ASC;结果展示:
4.按照dept_no进行汇总
数据导入
drop table if exists `dept_emp` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');
dept_emp表
问题:按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
SELECT dept_no, GROUP_CONCAT(emp_no SEPARATOR ',')AS employees
FROM dept_emp
GROUP BY dept_no;结果展示:
5.平均工资
数据导入
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');salaries表
问题:查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary
-- 解法1
SELECT AVG(salary)AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary <> (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-1')
AND salary <> (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-1');
-- 解法2
SELECT AVG(salary)AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
AND salary NOT IN(SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-1')
AND salary NOT IN(SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-1');结果展示:
6.分页查询employees表
数据导入
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
employees表
问题:分页查询employees表，每5行一页，返回第2页的数据
SELECT *
FROM employees
LIMIT 5,5;结果展示:
7.出现三次及以上相同积分的情况
数据导入
drop table if exists grade;
CREATE TABLE `grade` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,111),
(2,333),
(3,111),
(4,111),
(5,333);
grade表
id:用户id，number:积分
SELECT number
FROM grade
GROUP BY number
HAVING COUNT(id) >= 3;结果展示:
8.给出employees表中排名为奇数行的first_name
数据导入
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
employees表
问题:对于employees表，输出first_name排名(按first_name升序排序)为奇数的first_name【在不打乱原序列顺序的情况下，输出first_name】
SELECT a.first_name
FROM employees AS a
INNER JOIN (SELECT first_name,
row_number() over(ORDER BY first_name ASC) AS rk
FROM employees
) AS b
ON a.first_name = b.first_name
WHERE b.rk % 2 != 0;结果展示:
9*.统计salary的累计和running_total
数据导入
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
salaries表(部分数据截图)
问题:按照salary的累计和running_total，其中running_total为前N个当前(to_date = '9999-01-01')员工的salary累计和(输出顺序: emp_no, salary, running_total)
注意是对员工当前的薪水求和，running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计
-- sum(<汇总列>) over(<排序列>) as 别名；
SELECT emp_no, salary,
SUM(salary) OVER (ORDER BY emp_no)AS running_total
FROM salaries
WHERE to_date = '9999-01-01';结果展示:
10*.获取有奖金的员工相关信息
数据导入
drop table if exists `employees`;
drop table if exists emp_bonus;
drop table if exists `salaries`;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `emp_bonus` (
`emp_no` int not null,
`recevied` datetime not null,
`btype` smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO emp_bonus VALUES
(10001, '2010-01-01',1),
(10002, '2010-10-01',2);
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
employees表(员工表)
emp_bonus表(员工奖金表)
salaries表(薪水表)[部分数据截图]
注意，其中bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 to_date='9999-01-01'表示当前薪水
问题:给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus(bonus结果保留一位小数，输出结果按emp_no升序排序)
SELECT a.emp_no, a.first_name, a.last_name, b.btype, c.salary,
(CASE WHEN b.btype = 1 THEN ROUND(0.1 * c.salary,1)
WHEN b.btype = 2 THEN ROUND(0.2 * c.salary,1)
ELSE ROUND(0.3 * c.salary,1) END)AS bonus
FROM employees AS a
INNER JOIN emp_bonus AS b
ON a.emp_no = b.emp_no
INNER JOIN salaries AS c
ON b.emp_no = c.emp_no
WHERE c.to_date = '9999-01-01'
ORDER BY emp_no ASC;结果展示:
11.刷题通过的题目排名
数据导入
drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);passing_number表(通过题目个数表)
id:用户id number:用户通过题目个数
问题: 根据passing_number表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列(输出结果顺序: id, number, t_rank)
SELECT id, number,
DENSE_RANK() OVER(ORDER BY number DESC)AS t_rank
FROM passing_number
ORDER BY t_rank ASC, id ASC;结果展示:
12.找到每个人的任务
数据导入
drop table if exists person;
drop table if exists task;
CREATE TABLE `person` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `task` (
`id` int(4) NOT NULL,
`person_id` int(4) NOT NULL,
`content` varchar(32) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO person VALUES
(1,'fh'),
(2,'tm');
INSERT INTO task VALUES
(1,2,'tm works well'),
(2,2,'tm works well');person表
task表(任务表)
问题:找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序，输出情况如下所示:
SELECT a.id, a.name, b.content
FROM person AS a
LEFT JOIN task AS b
ON a.id = b.person_id
ORDER BY a.id ASC;
13.考试分数(一)
数据导入
drop table if exists grade;
CREATE TABLE grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999);
grade表
问题:查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)
SELECT job, ROUND(AVG(score),3)AS`avg`
FROM grade
GROUP BY job
ORDER BY `avg` DESC;结果展示:
14.考试分数(二)
数据导入
drop table if exists grade;
CREATE TABLE grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);
grade表
问题:查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序
-- 解法1:子查询
SELECT a.id, a.job, a.score
FROM grade AS a
WHERE a.score > (SELECT AVG(score)
FROM grade AS b
WHERE a.job = b.job)
ORDER BY a.id ASC;
-- 解法2:窗口函数
SELECT a.id,a.job,a.score
FROM (SELECT *,AVG(score) OVER(PARTITION BY job) AS avg_score
FROM grade)AS a
WHERE a.score > a.avg_score
ORDER BY a.id ASC;
-- 解法3
SELECT a.id, a.job, a.score
FROM grade AS a
INNER JOIN(SELECT job, AVG(score)AS avg_score
FROM grade
GROUP BY job)AS b
ON a.job = b.job
WHERE a.score > b.avg_score
ORDER BY a.id ASC;
结果展示:
15*.考试分数(三)
数据导入
drop table if exists grade;
drop table if exists language;
CREATE TABLE `grade` (
`id` int(4) NOT NULL,
`language_id` int(4) NOT NULL,
`score` int(4) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `language` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,1,12000),
(2,1,13000),
(3,2,11000),
(4,2,10000),
(5,3,11000),
(6,1,11000),
(7,2,11000);
INSERT INTO language VALUES
(1,'C++'),
(2,'JAVA'),
(3,'Python');
language表(语言岗位表)
grade表(分数表)
id:用户id language_id:语言岗位id score:分数
问题:找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
-- 解法1
SELECT a.id, b.name, a.score
FROM grade AS a
INNER JOIN `language` AS b
ON a.language_id = b.id
INNER JOIN (SELECT id,
dense_rank() over(PARTITION BY language_id ORDER BY score DESC) AS rk
FROM grade
)AS c
ON a.id = c.id
WHERE c.rk <= 2
ORDER BY b.name ASC, a.score DESC, a.id ASC;
-- 解法2
SELECT t.id,t.name,t.score
FROM (SELECT g.id,g.language_id,g.score,lg.name,
dense_rank()over(PARTITION BY g.language_id ORDER BY g.score DESC)AS rank2
FROM grade AS g
LEFT JOIN `language` AS lg
ON lg.id = g.language_id )AS t
WHERE t.rank2 IN(1,2)
ORDER BY t.name ASC, t.score DESC, t.id ASC;结果展示:
相关文章
- 盘点 8 种最坑的 SQL 错误用法
- Sql Server 存储过程使用技巧
- Spark sql -- Spark sql中的窗口函数和对应的api
- SQL SERVER 作业浅析
- 学生信息管理系统——配置windows防火墙允许访问SQL Server 2008服务器
- thinkphp5打印生成的SQL语句【转】
- Centos7安装Sql server
- Sql Server substring(expression, start, length)函数
- SQL 180. 连续出现的数字
- 【ClickHouse SQL 极简教程】ClickHouse SQL之数据定义语言 DDL
- sql server 锁(lock)的基础及应用
- sql 语句中count()有条件的时候为什么要加上or null
- Oracle 常用的经典SQL查询
- 还在用Navicat破解版吗？资深DBA推荐的八款SQL工具，免费又简单！
- 解决Mysql报错：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de ————————————