有三张表 Consultant 表、BaseOrder 表、OrderDetail 表
Consultant:
BaseOrder:
OrderDetail:
建表以及插入数据:
(1)用SQL语句查出所有 SubsidiarylD 为28的订单,按照订单号(OrderlD)降序排序
select a.consultantid
,a.statusid
,a.subsidiaryid
,a.name
,b.orderid
,b.orderdate
from consultant a join baseorder b
on a.consultantid=b.consultantid
where subsidiaryid=28
order by orderid desc;
(2)用 SQL 语句查询出所有 11月份每个 ConsultantiD 的订单总额
select b.*,c.*
from BaseOrder b join OrderDetails c
on b.orderid=c.orderid
where substr(orderdate,4,2) like '11%';
这里在使用substr对日期进行截取之后,为什么后面要使用一次like '11%'而不直接写成where substr(orderdate,4,2) =11呢,因为直接使用substr截取出来的8月和9月会自动补个月出来,先对日期进行to_char的转换再使用substr得到的结果是一样的,最后都是有个月
可以直接使用另外一个用法,直接使用to_char(orderdate,'MM')就能截取到正确月份了。所以也可直接使用to_char('','MM')这个用法,直接把日期转换成字符类型并截取日期中的月份
select b.*,c.*
from BaseOrder b join OrderDetails c
on b.orderid=c.orderid
where to_char(orderdate,'MM')=11;
(3)在 Consultant 表加入一条记录,ConsultantlD=5,StatuslD=50,SubsidiaryiD=29.Name=Jone
这个题so easy:
insert into consultant values(5,50,29,'Jone');
(4)删除 Consultant 表中 Name 含有 Gary 的记录
so easy,too
delete from consultant
where name like '%Gary%';
(5)将 BaseOrder 表中 ConsultantD=2 的最新创建的订单的订单时间 (OrderDate) 改为当前日期
--最简单的就是这个方法,不对日期做任何处理:
UPDATE BaseOrder1
SET OrderDate = sysdate WHERE ConsultantID = 2;
--也可以修改成统一格式:
update BaseOrder1
SET OrderDate = to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
WHERE ConsultantID=2;
2、SQL进阶题目:使用SQL算出2022年11月总销售额的环比与同比增长率:
同比就是比去年同期,环比就是这个月比上个月
同比计算方式:同比增长率 =(今年本期数-去年同期数)/同期数X100%
环比计算方式:环比增长率=(同年本期数-同年上期数)/上期数X100%
订单表 Order
Order | ||
PRD ID | Nums | Sale_date |
C1001 | 12 | 2021-09-03 |
C1002 | 11 | 2021-10-29 |
C1003 | 6 | 2021-10-30 |
C1001 | 3 | 2021-11-21 |
C1002 | 4 | 2021-11-23 |
C1003 | 12 | 2022-10-01 |
C1001 | 5 | 2022-10-04 |
C1002 | 3 | 2022-11-07 |
C1003 | 24 | 2022-11-30 |
产品表 Product
Product | ||
PRD ID | Name | Price |
C1001 | 12 | 100 |
C1002 | 11 | 200 |
C1003 | 6 | 300 |
先建表:
create table Order1(
PRD_ID varchar(10)
,Nums number
,Sale_date date
);
insert into Order1 values('c1001',12,to_date('2021-09-03','yyyy-mm-dd'));
insert into Order1 values('c1002',11,to_date('2021-10-29','yyyy-mm-dd'));
insert into Order1 values('c1003',6,to_date('2021-10-30','yyyy-mm-dd'));
insert into Order1 values('c1001',3,to_date('2021-11-21','yyyy-mm-dd'));
insert into Order1 values('c1002',4,to_date('2021-11-23','yyyy-mm-dd'));
insert into Order1 values('c1003',12,to_date('2022-10-01','yyyy-mm-dd'));
insert into Order1 values('c1001',5,to_date('2022-10-04','yyyy-mm-dd'));
insert into Order1 values('c1002',3,to_date('2022-11-07','yyyy-mm-dd'));
insert into Order1 values('c1003',24,to_date('2022-11-30','yyyy-mm-dd'));
create table Product(
PRD_ID varchar(10)
,Name varchar(5)
,Price number
);
insert into Product values('c1001',12,100);
insert into Product values('c1002',11,200);
insert into Product values('c1003',6,300);
select * from Order2;
select * from Product;
with last_month_total_price as
(select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
from order2 a join Product b
on a.PRD_ID=b.PRD_ID
where to_char(a.sale_date,'yyyy-mm') = '2022-10'
)
,this_month_total_price as
(select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
from order2 a join Product b
on a.PRD_ID=b.PRD_ID
where to_char(a.sale_date,'yyyy-mm') = '2022-11')
,last_year_total_price as
(select a.PRD_ID PRD_ID,a.sale_date,a.nums nums,b.price,sum(nums*price) over() total_price
from order2 a join Product b
on a.PRD_ID=b.PRD_ID
where to_char(a.sale_date,'yyyy-mm') = '2021-11')
select round((this_month_total_price.total_price - last_month_total_price.total_price)/this_month_total_price.total_price,4)*100||'%'
同比
from this_month_total_price,last_month_total_price,last_year_total_price;