1.总结
1.1关键字
INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
ON 生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
WHERE 在临时表生成好后,再对临时表进行过滤的条件。条件不为真的就全部过滤掉。
HAVING :WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
ORDER BY : 排序,默认升序(ASC), 降序为(DESC)
LIMIT m,n 从m+1条开始,取n个数据
LIMIT n 从第0条开始,取n条数据
GROUP BY :用于结合聚合函数,根据一个或多个列对结果集进行分组。
DISTINCT: 去掉重复数据(常在排名上使用)
SELECT DISTINCT:效率不高,尽量用GROUP BY 代替
COUNT(DISTINCT *)
NOT IN 选出不在的记录
IN 选择包含在内的记录
EXISTS:sql 返回结果集为真
NOT EXISTS :sql 不返回结果集为真
IS NULL :记录为空的条件
EXPLAIN:获取对应的执行计划
1.2函数
max() :找最大值
min() :找最小值
count() :计算记录个数
sum():计算数据总数
avg() :平均值
length():计算长度,eg:substr(first_name,-2) //其中负数 - 在从字符串结尾的指定位置开始
group_concat(X,Y) 连接字段 X是要连接的字段,Y 是连接的符号,默认为逗号
1.3表操作
- 建表、主键、外键
CREATE TABLE XX(
……
PRIMARY KEY (id)
FOREIGN KEY (id) REFERENCES nn(id)
)
- 插入数据、从其他表插入、存在则忽略、删除记录、更新记录
INSERT INTO xx
VALUES (……),(……)
INSERT INTO xx
SELECT ……
UNION SELECT ……
INSERT INTO actor_name SELECT first_name, last_name FROM actor
INSERT OR IGNORE INTO
DELETE FROM xxx
WHERE xx
UPDATE xxx
SET ……
WHERE xx
- 创建索引(唯一、普通)、强制索引查询(SQLite中)
CREATE UNIQUE INDEX xxx ON actor(first_name);
CREATE INDEX xxx on actor(last_name);
INDEXED BY
- 新增表项、改表名、丢弃表
ALTER TABLE xx
add ……
ALTER TABLE 原表名
RENAME TO 新表名
DROP TABLE xxx;
- 创建操作后触发器
CREATE TRIGGER xx AFTER INSERT ON xxx
BEGIN
操作
END;
1.4技巧
- 处理排名问题常用建立复数表,通过比较关系得到排名。
例如:薪水按照salary进行按照1-N的排名
思路:得到有多少个s2.salary大于等于s1.salary
select count(distinct s2.salary)
where s1.salary <=s2.salary
group by s1.emp_no
上面案例省略细节,只取关键代码
2. 题解
注释习惯了用java的 // , 所以下面sql题解用//注释是不合理的
- 查找最晚入职员工的所有信息
SELECT *
from employees
where hire_date=(
select max(hire_date)
from employees
)
- 查找入职员工时间排名倒数第三的员工所有信息
LIMIT m,n 从m+1条开始,取n个数据
LIMIT n 从第0条开始,取n条数据
distinct: 去重
SELECT *
from employees
order by hire_date desc
limit 2,1
//考虑到可能会有同一天日期入职的
SELECT *
from employees
where hire_date=(
select distinct hire_date
from employees
order by hire_date desc
limit 2,1
)
- 查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no,以salaries表为主表进行查询,输出结果以salaries.emp_no升序排序
select salaries.* ,dept_manager.dept_no
from salaries,dept_manager on salaries.emp_no=dept_manager.emp_no //连接
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01' //过滤掉以前和离职的,只留下当前的薪水
//inner join 版本
select salaries.* ,dept_manager.dept_no
from salaries inner join dept_manager on salaries.emp_no=dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
- 查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
答案是按employees表中顺序输出的,所以使用内连接查询时,必须将employees表放在前面。
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees inner join dept_emp on dept_emp.emp_no=employees.emp_no
//或者使用left join 也可以
- 查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left join dept_emp //所有左边表信息 右边表不存在则置null
on dept_emp.emp_no=employees.emp_no
- 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序(请注意,一个员工可能有多次涨薪的情况)
select employees.emp_no,salaries.salary
from employees inner join salaries
on employees.emp_no=salaries.emp_no and employees.hire_date=salaries.from_date //要注意是入职时候开始的薪水情况,不然会把多次涨薪情况都打印出来
order by employees.emp_no desc
//直接用逗号隔开,并列查询两张表
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC
- 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
select emp_no,count(emp_no) AS t
from salaries
group by emp_no //使用count 时要用group by
having t>15 // group by 对应的条件查询是having 而不是 where或on
- 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc
//由于distinct效率不高,当性能要求高数据集大或多表 采用group by,并且尽量使用group by
select salary
from salaries
where to_date='9999-01-01' //由于并没有采用分组数据进行聚合函数运算,所以不用having
group by salary
order by salary desc
- 获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
select d.dept_no,d.emp_no,s.salary
from dept_manager as d left join salaries as s //这里用左连接更合适
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by salary //应付同薪水情况
- 获取所有非manager的员工emp_no
//解法1 not in
select emp_no
from employees
where emp_no not in (
select emp_no
from dept_manager
)
// 采用 left join 找出d.emp_no 为空
select e.emp_no
from employees as e left join dept_manager as d
on e.emp_no=d.emp_no
where d.emp_no is null
- 获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
select de.emp_no,dm.emp_no as manager_no
from dept_emp as de inner join dept_manager as dm //inner join 得到同个部门,排除不同部门信息
on de.dept_no=dm.dept_no //输出自己的经理 所以自己与经理的部门要相同
where dm.to_date='9999-01-01' and de.emp_no<> dm.emp_no //限制经理条件及不相等的条件<>和!=都可以
- 获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门升序排列。
select d.dept_no,d.emp_no,max(s.salary)
from dept_emp as d left join salaries as s
on d.emp_no=s.emp_no
where d.to_date = '9999-01-01' and s.to_date='9999-01-01'
group by d.dept_no
//该方法GROUP BY 默认取非聚合的第一条记录考虑进去,在测试用例中能通过,但实际上不可行
//正确答案过于复杂,暂时略去
- 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
select title,count(title) as t
from titles
group by title
having t>1
- 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略(即emp_no重复的title不计算,title对应的数目t不增加)。
select title,count(distinct emp_no) as t //在count中通过distinct将emp_no去重,计算title
from titles
group by title
having t>1
- 查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列(题目不能使用mod函数)
select *
from employees
where emp_no%2=1 and last_name!='Mary' //注意Mary 要加 ''
order by hire_date desc
- 统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。
select t.title,avg(s.salary)
from titles as t inner join salaries as s //连接起来再来计算平均值才是正确的
on t.emp_no=s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by title
- 获取当前(to_date=‘9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc //limit前先进行排序
limit 1,1
- 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,你可以不使用order by完成吗
思路:select嵌套 找两次max
select s.emp_no,max(s.salary),e.last_name,e.first_name
from salaries as s inner join employees as e
on s.emp_no=e.emp_no
where s.to_date='9999-01-01'and s.salary not in ( //not in 排除掉最大
select max(salary) //这里不能再用s.salary
from salaries
where s.to_date='9999-01-01'
)
- 查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name
from employees as e left join (dept_emp as de inner join departments as d)
on e.emp_no=de.emp_no and de.dept_no=d.dept_no
- 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
//仅使用于没有降薪
select max(salary)-min(salary) as growth
from salaries
where emp_no=10001
SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth
- 查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!=‘9999-01-01’,这样的数据不显示在查找结果里面)
select e.emp_no,c.salary-b.salary as growth
from employees as e inner join salaries as b
on e.emp_no=b.emp_no and b.from_date = e.hire_date
inner join salaries as c
on e.emp_no=c.emp_no and c.to_date='9999-01-01'
order by growth asc
- 统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
select d.dept_no,d.dept_name,count(s.salary) as sum
from departments as d inner join dept_emp as de on d.dept_no=de.dept_no
inner join salaries as s on de.emp_no= s.emp_no
group by d.dept_no //调了半天竟然是因为把group写成了order
- 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
思路:
复用salaries表比较排名
s1.salary <= s2.salary:标识输出s1.salary下,有多少个s2.salary大于等于s1.salary
比如s1.salary=94409时,有3个s2.salary(分别为94692,94409,94409)大于等于它,但由于94409重复,利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。
select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries as s1,salaries as s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary <=s2.salary
group by s1.emp_no
order by s1.salary desc , s1.emp_no asc //先薪水逆序,再部门正序
- 获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date=‘9999-01-01’
select de.dept_no,e.emp_no,s.salary
from dept_emp as de inner join employees as e on de.emp_no=e.emp_no
inner join salaries as s on s.emp_no=de.emp_no dept_no
where s.to_date='9999-01-01' and de.emp_no not in( //这里用的是emp_no来做排除 而不是用dept_no
select emp_no
from dept_manager
)
- 获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
select de.emp_no ,dm.emp_no as manager_no,s1.salary as emp_salary ,s2.salary as manager_salary //调很久是因为把emp_no写成maneger_no 找不到而出错
from dept_emp as de inner join salaries as s1 on s1.emp_no=de.emp_no,
dept_manager as dm inner join salaries as s2 on s2.emp_no=dm.emp_no
where de.dept_no=dm.dept_no and s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary>s2.salary
- 汇总各个部门当前员工的title类型的分配数目,即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count
select d.dept_no,d.dept_name,t.title,count(*)
from departments as d inner join dept_emp as de on d.dept_no=de.dept_no
inner join titles as t on t.emp_no= de.emp_no
where t.to_date = '9999-01-01' and de.to_date = '9999-01-01'
group by d.dept_no,t.title //需要同时对dept_no和title进行分组,因为不同部门里面还需要对不同的title 进行分组
-
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年,而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)
select s2.emp_no,s2.from_date, (s2.salary-s1.salary) as salary_growth
from salaries as s1 inner join salaries as s2 on s1.emp_no=s2.emp_no
where salary_growth>5000 and s2.from_date = s1.to_date
order by salary_growth desc
- 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id)),而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
SELECT c.name, COUNT(fc.film_id) FROM
(select category_id, COUNT(film_id) AS category_num FROM
film_category GROUP BY category_id HAVING count(film_id)>=5) AS cc,
film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
- 使用join查询方式找出没有分类的电影id以及名称
select f.film_id,f.title
from film as f left join film_category as fc on f.film_id=fc.film_id
where category_id is null
30.你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗
select f.title,f.description
from film as f inner join film_category as fc on f.film_id=fc.film_id
where fc.category_id=(
select category_id
from category
where name = 'Action'
)
- 获取select * from employees对应的执行计划
explain select * from employees
-
将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
(注:该数据库系统是sqllite,字符串拼接为 || 符号,不支持concat函数)
select (last_name||" "|| first_name) as Name
from employees
33.创建一个actor表,包含如下列信息(注:sqlite获取系统默认时间是datetime(‘now’,‘localtime’))
CREATE TABLE actor(
actor_id smallint(5) NOT NULL ,
first_name varchar(45) NOT NULL ,
last_name varchar(45)NOT NULL ,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')),
PRIMARY KEY (actor_id)
)
//方法一:利用VALUES(value1, value2, ...), (value1, value2, ...), ...(value1, value2, ...),
insert into actor
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
//方法2:利用 UNION SELECT 批量插入
insert into actor
select 1,'PENELOPE','GUINESS','2006-02-15 12:34:33'
union select 2,'NICK','WAHLBERG','2006-02-15 12:34:33'
insert or ignore into actor //插入,若存在则忽略
select '3','ED','CHASE','2006-02-15 12:34:33'
CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name SELECT first_name, last_name FROM actor
37.对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
(请先创建唯一索引,再创建普通索引)
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name)
针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
create view actor_name_view as //这里的as不能省略
select first_name as first_name_v,last_name as last_name_v from actor
- 针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
MYSQL中强制索引查询使用:FORCE INDEX(indexname);
SQLite中强制索引查询使用:INDEXED BY indexname;
select * from salaries indexed by idx_emp_no where emp_no=10005
- 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’0000-00-00 00:00:00’
alter table actor
add create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
41.构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
CREATE trigger audit_log after insert on employees_test
begin
insert into audit values (NEW.ID,NEW.NAME);
end;
- 删除emp_no重复的记录,只保留最小的id对应的记录。
delete from titles_test
where id not in (
select min(id)
from titles_test
group by emp_no
)
- 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
update titles_test
set to_date=NULL,from_date='2001-01-01'
where to_date='9999-01-01'
- 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
//方法一:全部替换
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
//方法二:部分替换
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5
- 将titles_test表名修改为titles_2017。
alter table titles_test
rename to titles_2017
- 在audit表上创建外键约束,其emp_no对应employees_test表的主键id。
思路:由于不支持alter 只能用弃表重建
drop table audit;
create table audit(
EMP_no INT not null,
create_date datetime not null,
foreign key (emp_no) references employees_test(ID)
);
- 存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?
select employees.* from employees,emp_v where employees.emp_no=emp_v.emp_no
- 请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
UPDATE salaries
SET salary = salary * 1.1
WHERE to_date='9999-01-01'
and emp_no in( //这里需要用in 因为不止一个值
SELECT emp_no FROM emp_bonus
)
select "select count(*) from"|| name|| ";" as cnts
from sqlite_master where type = 'table' //需要从系统表中找type 为'table'的字段
50.将employees表中的所有员工的last_name和first_name通过(’)连接起来。(不支持concat,请用||实现)
select last_name||"'"||first_name from employees
51.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
select length('10,A,B')-length(replace('10,A,B',",",""))
- 获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
select first_name
from employees
order by substr(first_name,-2) //负数 - 在从字符串结尾的指定位置开始
- 按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
聚合函数group_concat(X,Y),其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号。
select dept_no ,group_concat(emp_no) as employees
from dept_emp
group by dept_no
- 查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。
select avg(salary) as avg_salary
from salaries
where salary not in(select max(salary) from salaries where to_date = '9999-01-01' )
and salary not in(select min(salary) from salaries where to_date = '9999-01-01' )
and to_date = '9999-01-01'
- 分页查询employees表,每5行一页,返回第2页的数据
select * from employees limit 5,5
- 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received,没有分配奖金的员工不显示对应的bonus类型btype和received
select de.emp_no,de.dept_no,eb.btype,eb.received
from dept_emp as de left join emp_bonus as eb
on de.emp_no=eb.emp_no
- 使用含有关键字exists查找未分配具体部门的员工的所有信息。
在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录,也就是当 employees.emp_no=10011的时候。反之,把NOT去掉,则输出 employees.emp_no=10001~10010时的记录。
select * from employees
where not exists(
select emp_no
from dept_emp
where emp_no=employees.emp_no
)
-
同47题
-
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
select e.emp_no,e.first_name,e.last_name,b.btype ,s.salary,(s.salary*b.btype*0.1) as bonus
from emp_bonus as b inner join (employees as e inner join salaries as s on e.emp_no =s.emp_no )
on e.emp_no =b.emp_no
where s.to_date='9999-01-01'
60.按照salary的累计和running_total,其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和,其他以此类推。 具体结果如下Demo展示。。
select s1.emp_no,s1.salary,sum(s2.salary) as running_total
from salaries as s1,salaries as s2
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'and s1.emp_no>=s2.emp_no
group by s1.emp_no
- 对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
思路:有关排名的通常都是通过建立复数表来实现
select first_name
from employees as e1
where (
select count(*)
from employees as e2
where e2.first_name<=e1.first_name
)%2=1
select number
from grade
group by number
having count(number)>=3
select p1.id,p1.number,count(distinct p2.number) as rank
from passing_number as p1,passing_number as p2
where p1.number<=p2.number
group by p1.id
order by p1.number desc,p1.id
select p.id,p.name,t.content
from person as p left join task as t
on p.id=t.person_id
order by p.id
统计个数通过建复数表,做比较的结果限定个数来实现
select g1.id,l.name,g1.score
from grade as g1 inner join language as l
on g1.language_id=l.id
where (
select count(distinct g2.score)
from grade as g2
where g1.score<=g2.score and g1.language_id=g2.language_id
)<=2
order by l.name,g1.score desc,g1.id
select email.date, round(
sum(case email.type
when 'completed'
then 0 else 1 end)*1.0/count(email.type),3) as p
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)
group by email.date
order by email.date asc
重点在于用 group by分组
select max(date) as d
from login
group by user_id
select u.name as u_n,c.name as c_n,max(date) as d
from login as l inner join user as u on l.user_id=u.id
inner join client as c on c.id=l.client_id
group by l.user_id
order by u.name