mysql语句总结

1. 统计男女比例与男女工资

SELECT gender,
count(id) number,
count(id)/(select count(id)from test4) num_pro, 
sum(high) sumup,
sum(high)/(select sum(high)from test4) high_pro
from test4
group by gender;

select * from test4
order by income desc;

2. 按照数学成绩排序

select * from test5
where sub='数学'
order by score; 

3. 对表行转列

   
   基础表

select*from
(select id,
sum(if(sub='数学',score,0)) math,
sum(if(sub='英语',score,0)) eng,
sum(if(sub='语文',score,0)) chi,
sum(score) as total
from test5
group by id
)as a
order by total desc

行转列结果

4. 倒置后找到英语分数最高的

select * from
(
select id,
sum(if(sub='数学',score,0)) math,
sum(if(sub='英语',score,0)) eng,
sum(if(sub='语文',score,0)) chi,
sum(score) as total
from test5
group by id
) as a
order by eng desc limit 1

5. 两个表操作，找到总分最高的两人的名字，id
   一个表id和名字；另一个表id 分数

select test4.id,test4.`name`,total
from test4,
(
select*from
(select id,
sum(if(sub='数学',score,0)) math,
sum(if(sub='英语',score,0)) eng,
sum(if(sub='语文',score,0)) chi,
sum(score) as total
from test5
group by id
)as a
order by total desc limit 2
)as b
where b.id=test4.id

6. 两个表
   一个表id, 注册日期
   另一个表id，付费金额

select count(distinct t2.id) as '付费人数',sum(t2.money) as '付费金额'
from (
select id from test1 where year(date1)>=2020
)t1
left join (
select id,money from test2
)t2
on t1.id=t2.id

7. 对日期倒置

SELECT  
id id,
max(CASE WHEN status = 's1' THEN  date ELSE null END )as sta1,
max(CASE WHEN status = 's2' THEN  date ELSE null END) as sta2
FROM test6
group by id

8. 次日留存率，3日留存率，7日留存率

select 
    date(reg_time) dt,
    count(distinct user_info.user_id) 新增用户数,
    sum(datediff(login_time,reg_time)=1) 次日留存用户数,
    sum(datediff(login_time,reg_time)=3) 三日留存用户数,
    sum(datediff(login_time,reg_time)=7) 七日留存用户数,
    sum(datediff(login_time,reg_time)=1)/count(distinct user_info.user_id) 次日留存率,
    sum(datediff(login_time,reg_time)=3)/count(distinct user_info.user_id) 三日留存率,
    sum(datediff(login_time,reg_time)=7)/count(distinct user_info.user_id) 七日留存率
from user_info left join login_log on user_info.user_id=login_log.user_id
group by date(reg_time);