1445. 苹果和桔子 1468. 计算税后工资 1479. 周内每天的销售情况

本文链接：https://blog.csdn.net/weixin_45646601/article/details/127454628

利用sum实现加或者减
sum(if(fruit=‘apples’,sold_num,-sold_num))
max(salary)over(partition by company_id)<1000 then salary

看日期是星期几三种函数：
weekday(date)---->0周一1周二2周三…6周日
dayofweek(date)–>1周日2周一3周二…7周六
dayname(date)—>出来的直接是英文MONDAY等

注意:
curdate()-----当前日期
date_sub(date,interval 2 day) — date日期的前两天日期 day，month，hour等必须注明

date_add(date,interval 2 day)—date日期加2
date_add(date,interval,-2 day)—date日期减2

【注意interval和day等缺一不可】

select curdate() ,date_add(curdate(),interval 2 day)
,date_add(curdate(),interval -2 day)
union
select curdate() ,date_sub(curdate(),interval 2 day)
,date_add(curdate(),interval -2 day);

在这里插入图片描述

1445. 苹果和桔子

在这里插入图片描述

select sale_date ,sum(if(fruit='apples',sold_num,-sold_num)) diff
from Sales
group by sale_date
order by sale_date asc

1468. 计算税后工资

在这里插入图片描述

# Write your MySQL query statement below
# select company_id,employee_id, employee_name, 
#     case
#     when salary <1000 then salary
#     when salary>=1000 and salary <10000 then 1000+(salary-1000)*0.24
#     when salary>=10000 then 1000+9000*0.24+(salary-10000)*0.49
#     end
#  as salary 
# from Salaries
select company_id,employee_id, employee_name, 
round(
    case
    when max(salary)over(partition by company_id)<1000 then salary
    when max(salary) over(partition by company_id) between 1000 and 10000
                 then (salary)*(1-0.24)
    else (salary)*(1-0.49)
    end,0)
 as salary 
from Salaries

1479. 周内每天的销售情况

在这里插入图片描述

# Write your MySQL query statement below
select item_category   CATEGORY,
        sum(if(weekday(order_date)=0,quantity,0)) 'MONDAY',
        sum(if(weekday(order_date)=1,quantity,0)) 'TUESDAY',
        sum(if(weekday(order_date)=2,quantity,0)) 'WEDNESDAY',
        sum(if(weekday(order_date)=3,quantity,0)) 'THURSDAY',
        sum(if(weekday(order_date)=4,quantity,0)) 'FRIDAY',
        sum(if(weekday(order_date)=5,quantity,0)) 'SATURDAY',
        sum(if(weekday(order_date)=6,quantity,0)) 'SUNDAY'
from Orders O right join Items I
on O.item_id = I.item_id
group by CATEGORY
order by CATEGORY