https://www.bilibili.com/video/BV1PU4y1p7jP?p=1
https://www.jianshu.com/p/0f165dcf9525
-
sql查询分组并将一列按多行展示
原表
要查成
select year,sum(m1) m1,sum(m2) m2,sum(m3) m3,sum(m4) m4 from (
SELECT year,
case when month=1 then amount end m1,
case when month=2 then amount end m2,
case when month=3 then amount end m3,
case when month=4 then amount end m4
FROM `test1`
)
tmp group by year;
利用Max(CASE ... WHEN ... THEN)语法来使其转为行:
SELECT year,
max(case month when '1' then amount end) m1,
max(case month when '2' then amount end) m2,
max(case month when '3' then amount end) m3,
max(case month when '4' then amount end) m4
from test1
group by year;
1、
select u.user_id,sex,age,count_7 ,count_14
from user_info u,
(
select user_id,count(order_id) count_7 from
sale_order where creat_time <=date_add('1997-01-12',interval 7 day )
and
creat_time >=date_add('1997-01-12',interval 0 day )
group by user_id) m,
(
select user_id,count(order_id) count_14 from
sale_order where creat_time <=date_add('1997-01-12',interval 14 day )
and
creat_time >=date_add('1997-01-12',interval 0 day )
group by user_id) n
where u.user_id =m.user_id and m.user_id=n.user_id and u.user_id = n.user_id;
2、
#复制sale_order表为sale_order_copy1
update sale_order_copy1 a1,sale_order_inc a2 set a1.user_id=a2.user_id,a1.creat_time = a2.creat_time
where a1.order_id =a2.order_id;
insert into sale_order_copy1(order_id,user_id,creat_time)
select order_id,user_id,creat_time from sale_order_inc where sale_order_inc.order_id
not in (select order_id from sale_order_copy1);
3、
#每个用户的总订单量
SELECT user_id,count(user_id) 总单量 FROM `sale_order` group by user_id order by 总单量 ;
#全部用户人数的中位数、上下四分位数
select round(count(distinct user_id)/4) 下四分位数,
round(count(distinct user_id)/2) as 订单量中位数,
round(count(distinct user_id)/4*3) as 上四分位数
from sale_order ;
#用户订单量上四分位数
select 总单量 from (SELECT user_id,count(user_id) 总单量 FROM `sale_order` group by user_id) as user_orders order by 总单量 limit 5893,1;
#用户订单量中位数
select 总单量 from (SELECT user_id,count(user_id) 总单量 FROM `sale_order` group by user_id) as user_orders order by 总单量 limit 11785,1;
#用户订单量下四分位数
select 总单量 from (SELECT user_id,count(user_id) 总单量 FROM `sale_order` group by user_id) as user_orders order by 总单量 limit 17678,1;
学生信息查询类
insert into student select 1,'刘一',18,'男' union all
select 2,'钱二',19,'女' union all
SELECT 4,'李四',18,'女' union all
select 5,'王五' ,17,'男' union all
select 6,'赵六' ,19,'女' ;
alter table teacher convert to character set utf8mb4;
insert into course select 1,'语文',1 union all
select 2,'数学',2 union all
select 3,'英语',3 union all
select 4,'物理',4;
INSERT into score
select 1,1,56 union all
select 1,2,78 union all
select 1,3,67 union all
select 1,4,58 union all
select 2,1,79 union all
SELECT 2,2,81 union all
select 2,3,92 union all
select 2,4,68 union all
select 3,1,91 union all
select 3,2,47 union all
select 3,3,88 union all
select 3,4,56 union all
select 4,2,88 union all
select 4,3,90 union all
select 4,4,93 union all
select 5,1,46 union all
select 5,3,78 union all
select 5,4,53 union all
select 6,1,35 union all
select 6,2,68 union all
SELECT 6,4,71
insert into teacher
select 1,'叶平' union all
select 2,'贺高' union all
select 3,'杨艳' union all
select 4,'周磊'
1、查询语文成绩大于数学成绩的学生学号。
select a.sid from (select sid,score from score where cid='1') a ,
(select sid,score from score where cid='2' ) b where a.score > b.score and a.sid=b.sid;
2、查询平均成绩大于60份的同学的学号和平均成绩
select sid,avg(score) from score group by sid having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select student.sid,student.sname,count(student.sid),sum(score.score) from student left join score on student.sid = score.sid group by student.sid,student.sname;
4、查询理姓李的老师的个数；
select count(tname) 姓李的老师个数 from teacher where tname like '%李%';
5、查询每门课程被选修的学生数；
SELECT cid,count(*) FROM score group by cid ;
6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT cid,avg(score) FROM score group by cid order by avg(score) asc,cid desc;
7、查询所有课程成绩小于60分的同学的学号、姓名；
#即排除掉只要有课程分数大于60的学生
select sid,sname from student where sid not in (select distinct sid from score where score >60 );
8、删除学习“叶平”老师课的sc表记录；
select * from score where cid in
(select cid from course where tid in
(select tid from teacher where tname='叶平'));
