select a.device_id,a.university, ifnull(b.question_cnt,0) as question_cnt, -- 无记录时显示0 ifnull(b.right_question_cnt,0) as right_question_cnt from (select device_id ,university from user_profile where university='复旦大学') as a left join (select device_id,count(device_id)as question_cnt,sum(case when resu...

select staff_tb.department, (round(sum(salary_tb.normal_salary-salary_tb.dock_salary)/count(staff_tb.staff_id),3)) as avg_salary from staff_tb join salary_tb on staff_tb.staff_id=salary_tb.staff_id where (salary_tb.normal_salary - salary_tb.dock_salary) between 4000 and 30000 group by staff_tb.depar...

select staff_tb.department, (round(sum(salary_tb.normal_salary-salary_tb.dock_salary)/count(staff_tb.staff_id),3)) as avg_salary from staff_tb join salary_tb on staff_tb.staff_id=salary_tb.staff_id where (salary_tb.normal_salary - salary_tb.dock_salary) between 4000 and 30000 group by staff_tb.depar...

select staff_tb.department, (round(sum(salary_tb.normal_salary-salary_tb.dock_salary)/count(staff_tb.staff_id),3)) as avg_salary from staff_tb join salary_tb on staff_tb.staff_id=salary_tb.staff_id where (salary_tb.normal_salary - salary_tb.dock_salary) between 4000 and 30000 group by staff_tb.depar...

select date(order_tb.order_time) as order_date,order_tb.user_id,count(order_tb.user_id) as order_nums,uservip_tb.vip from order_tb join uservip_tb where order_tb.user_id=uservip_tb.user_id group by date(order_tb.order_time),order_tb.user_id having order_nums>1 order by order_nums desc; 连续两次分组：对时间...

select order_tb.user_id, (sum(order_tb.order_price)+ uservip_tb. point) as point from order_tb join uservip_tb on order_tb.user_id=uservip_tb.user_id where order_tb.order_price>100 group by order_tb.user_id order by point desc;

select user_id, sum(floor(timestampdiff(minute,visit_time,leave_time)/10) )as point from visit_tb group by user_id order by point desc; 难点floor(timestampdiff(minute,visit_time,leave_time)/10)的用法

selectstaff_tb.staff_id,staff_tb.staff_name, concat(round((salary_tb.dock_salary/normal_salary)*100,1),'%') as dock_ratio from staff_tb join salary_tb on staff_tb.staff_id=salary_tb.staff_id where staff_tb.department='dep1' order by dock_ratio desc; 1.找到两表的连接条件字段；2.筛选出where 条件筛选出部门为 dep1 的员工；3.计算薪资扣...

select a.date ,CONCAT(ROUND((a.num1 / b.num2) * 100, 1), '%') as cr from (select date(order_time) as date,count(distinct (user_id)) as num1 from order_tb group by date(order_time)) as a join (select date(visit_time) as date,count(distinct (user_id)) as num2 from visit_tb group by date(visit_time)) a...

select uservip_tb.vip,COALESCE(sum(order_tb .order_price),0) as order_totalfrom order_tb right join uservip_tb on order_tb .user_id=uservip_tb .user_id group by uservip_tb.vip order by order_total desc;

select uservip_tb.vip,count(*) AS visit_nums,COUNT(DISTINCT visit_tb.user_id) AS visit_users from visit_tb join uservip_tb on visit_tb.user_id =uservip_tb.user_id group by uservip_tb.vip order by visit_nums desc;