目录
统计2019年期间国家法定节假日、休息日、工作日的订单笔数、订单总额
统计2019年期间每个季度的销售订单笔数、订单总额
-- 统计2019年期间每个季度的销售订单笔数、订单总额 (这里使用的 itcast_orders 表) 第一种方式
select jidu.mgjdu as mgjdu,
count(io.orderid) as number,
sum(io.totalmoney) as money,
year(io.paytime) as year,
month(io.paytime) as month
FROM
itcast_orders io ,
(
-- 计算第几季度
select distinct ( floor(substr(io.paytime,6,2)/3.1) + 1 ) as mgjdu from itcast_orders io where year(io.paytime) ='2019'
) jidu
GROUP BY jidu.mgjdu, year(io.paytime), month(io.paytime);
-- 统计2019年期间每个季度的销售订单笔数、订单总额 (这里使用的 itcast_order_goods io 表) 第二种方式
select jidu.mgjdu as mgjdu,
count(distinct io.orderid) as number,
sum(io.goodsprice*io.goodsnum) as money,
year(io.createtime) as year,
month(io.createtime) as month
FROM
itcast_order_goods io ,
(
-- 计算第几季度
select distinct ( floor(substr(io.createtime,6,2)/3.1) + 1 ) as mgjdu from itcast_order_goods io where year(io.createtime) ='2019'
) jidu
GROUP BY jidu.mgjdu, year(io.createtime), month(io.createtime);
-- 第三种 方式 (这里使用的 itcast_order_goods io 表)
select case
when substr(createtime,6,2)='01' then 'jd1'
when substr(createtime,6,2)='02' then 'jd1'
when substr(createtime,6,2)='03' then 'jd1'
when substr(createtime,6,2)='04' then 'jd2'
when substr(createtime,6,2)='05' then 'jd2'
when substr(createtime,6,2)='06' then 'jd2'
when substr(createtime,6,2)='07' then 'jd3'
when substr(createtime,6,2)='08' then 'jd3'
when substr(createtime,6,2)='09' then 'jd3'
else 'jd4'
end as quarter_name,
count(distinct io.orderid),
sum(io.goodsprice*io.goodsnum)
from
itcast_order_goods io
group by
case
when substr(createtime,6,2)='01' then 'jd1'
when substr(createtime,6,2)='02' then 'jd1'
when substr(createtime,6,2)='03' then 'jd1'
when substr(createtime,6,2)='04' then 'jd2'
when substr(createtime,6,2)='05' then 'jd2'
when substr(createtime,6,2)='06' then 'jd2'
when substr(createtime,6,2)='07' then 'jd3'
when substr(createtime,6,2)='08' then 'jd3'
when substr(createtime,6,2)='09' then 'jd3'
else 'jd4'
end;
结果图:
统计2019年期间每个月的销售订单笔数、订单总额
-- 统计2019年期间 每个月 的销售订单笔数、订单总额 第一种方式
SELECT count(distinct io.orderid) as number,
sum(io.goodsprice*io.goodsnum) as money,
year(io.createtime) as year,
month(io.createtime) as month
FROM
itcast_order_goods io where io.createtime between '2019-01' and '2020-01'
GROUP BY year(io.createtime) , month(io.createtime) ;
-- 统计2019年期间 每个月 的销售订单笔数、订单总额 第二 种方式
select case
when substr(createtime,6,2)='01' then '01'
when substr(createtime,6,2)='02' then '02'
when substr(createtime,6,2)='03' then '03'
when substr(createtime,6,2)='04' then '04'
when substr(createtime,6,2)='05' then '05'
when substr(createtime,6,2)='06' then '06'
when substr(createtime,6,2)='07' then '07'
when substr(createtime,6,2)='08' then '08'
when substr(createtime,6,2)='09' then '09'
when substr(createtime,6,2)='10' then '10'
when substr(createtime,6,2)='11' then '11'
else '12'
end as MonthName,
count(distinct io.orderid),
sum(io.goodsprice*io.goodsnum)
from
itcast_order_goods io
group by
case
when substr(createtime,6,2)='01' then '01'
when substr(createtime,6,2)='02' then '02'
when substr(createtime,6,2)='03' then '03'
when substr(createtime,6,2)='04' then '04'
when substr(createtime,6,2)='05' then '05'
when substr(createtime,6,2)='06' then '06'
when substr(createtime,6,2)='07' then '07'
when substr(createtime,6,2)='08' then '08'
when substr(createtime,6,2)='09' then '09'
when substr(createtime,6,2)='10' then '10'
when substr(createtime,6,2)='11' then '11'
else '12'
end;
结果图如下:
统计2019年期间每周一到周日的销售订单笔数、订单总额
-- 统计2019年期间 每周一到周日的 销售订单笔数、 订单总额 2019 9月8 日 到 10 日 8:星期一 , 9:星期二,10:星期三
SELECT
COUNT(distinct io.orderid) as xsb,
sum(io.goodsprice*io.goodsnum) as ddmoney,
dayofweek(io.createtime) as weekday,
month(io.createtime) as month,
day(io.createtime) as days
FROM
itcast_order_goods io
GROUP BY dayofweek(io.createtime),month(io.createtime), day(io.createtime);
结果图如下:
统计2019年期间国家法定节假日、休息日、工作日的订单笔数、订单总额
-- date 日期 统计2019年期间国家法定节假日、休息日、工作日的订单笔数、订单总额
select ri.sum sum,
ri.money moneyy,
date(ri.dayss),
da.day_week,
da.day_type,
da.day_desc
from
( SELECT
count(distinct io.orderid) sum,
sum(io.goodsprice*io.goodsnum) money,
dayofweek(io.createtime) as weekday,
month(io.createtime) as month,
date(io.createtime) as dayss
FROM
itcast_order_goods io
join `days_2019` days
on date(io.createtime) = date(days.day_time)
GROUP BY dayofweek(io.createtime),month(io.createtime), date(io.createtime)
) ri
join days_2019 da
on date(ri.dayss) = date(da.day_time) ;
结果图如下:
统计2019年期间每天有多少条数据
SELECT
COUNT(1) AS countNumber,
DATE_FORMAT(io.createtime,'yyyy-MM-dd') AS dateTime
FROM
itcast_order_goods io
GROUP BY DATE_FORMAT(io.createtime,'yyyy-MM-dd');
结果图如下:
需要字段 如下 :
统计2019年期间国家法定节假日、休息日、工作日
难点:
(这里要 判断 国家法定节假日、休息日、工作日 这些 节日 因为没有数据 所以 要搜索一下 国务院的标准 扒下sql语句
DROP TABLE IF EXISTS `days_2019`;
CREATE TABLE `days_2019` (
`day_time` date DEFAULT NULL COMMENT '日期',
`day_week` varchar(1) DEFAULT NULL COMMENT '星期',
`day_type` int(11) DEFAULT NULL COMMENT '0 为节假日,1为工作日, 2为周末',
`day_desc` varchar(30) DEFAULT NULL COMMENT '节假日描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// 建表语句
create table `days_2019`
( `day_time` string COMMENT '日期',
`day_week` string COMMENT '星期',
`day_type` int COMMENT '0 为节假日,1为工作日, 2为周末' ,
`day_desc` string COMMENT '节假日描述' ) ;
select * from days_2019;
-- ----------------------------
-- Records of days_2019
-- ----------------------------
INSERT INTO `days_2019` VALUES ('2019-01-01', '2', '0', '元旦');
INSERT INTO `days_2019` VALUES (