11、第11题
info 表
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写sql语句? win lose
2005-05-09 2 2
2005-05-10 1 2
答案：
(1) select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;
(2) select a.date, a.result as win, b.result as lose from (select date, count(result) as result from info where result = "win" group by date) as a join (select date, count(result) as result from info where result = "lose" group by date) as b
on a.date = b.date;12、第12题
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句
select courseid, coursename ,score ,if(score>=60, "pass","fail") as mark from course13、第13题
面试题：怎么把这样一个
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
查成这样一个结果
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 aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year14、第14题
学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)15、第15题
交易表结构为user_id(用户ID),order_id(订单ID),pay_time(付款时
间),order_amount(金额)
1. 写sql查询过去一个月付款用户量（提示：用户量需去重）最高的三天分别是哪几天？
2. 写sql查询昨天每个用户最后付款的订单ID及金额
select
date_format(pay_time,'%Y-%m-%d') days ,
count(distinct user_id)
from table
where pay_time>=date_sub(now(),interval 1 month) #过去一个月
group by date_format(pay_time,'%Y-%m-%d')
order by count(distinct user_id) desc
limit 3思路：求最高的三天，肯定是先排序，后limit. 先求出每天的付款用户量，既然每天，那肯定要按天分
组了；按照题目要求过滤条件有：1.过去一个月 2.付款用户（即要排除未付款的用户），另外求用户量
需要去重，题目中也有提示，因为存在同一个用户每天有多笔消费记录的情况；返回排在前三的付款用
户量及对应的时间（天）
select
a.user_id, a.order_amount
from
(select
user_id, order_amount,
row_number() over(partition by user_id order by pay_time desc) as rank
from table
where date_format(pay_time,"%Y-%m-%d")=date_sub(curdate(),interval 1 day) #昨天
) as
awhere rank=1
