SQL面试题:畅销商品分析
某互联网公司数据分析岗位SQL面试题,要求计算每个卖家销量最高的商品。
-- 商家卖货记录表
CREATE TABLE t5 (seller_id int, buyer_id int, item_id int, num int);
INSERT INTO t5 VALUES (1, 11, 1, 100), (1, 12, 1, 200), (1, 12, 2, 300);
INSERT INTO t5 VALUES (2, 11, 1, 100), (2, 12, 3, 200);
-- 计算每个卖家销量最高的商品
SELECT seller_id,item_id,total FROM (
SELECT seller_id,item_id,SUM(num) AS total,RANK() over(PARTITION BY seller_id ORDER BY SUM(num) DESC) AS rk FROM t5 GROUP BY seller_id,item_id) s where s.rk=1;
数据分析岗位SQL面试题:自建商品销量占比
某互联网公司数据分析岗位SQL面试题,要求计算每个商家的自建商品销量占总销量的比例。
-- 商家卖货记录表
CREATE TABLE t4 (seller_id integer, buyer_id integer, item_id integer, order_cnt integer, price numeric, source_type tinyint);
INSERT INTO t4 VALUES (1, 11, 1, 5, 9.9, 1), (1, 12, 2, 15, 16.9, 0);
INSERT INTO t4 VALUES (2, 13, 3, 10, 89.9, 0), (2, 14, 4, 11, 1999, 0);
-- 商家自建商品销售量占比
SELECT seller_id, SUM(CASE source_type WHEN 1 THEN order_cnt ELSE 0 END)/SUM(order_cnt) AS ratio FROM t4 GROUP BY seller_id;
数据分析岗位SQL面试题:累计销售金额
某互联网公司数据分析岗位SQL面试题,要求计算每个用户首次销售日期后 30 天内累计销售金额。
CREATE TABLE t3 (user_id integer, sell_day date, amount NUMERIC);
INSERT INTO t3 VALUES (1, '2021-01-01', 100), (1, '2021-01-30', 100), (1, '2021-02-01', 100);
INSERT INTO t3 VALUES (2, '2021-01-01', 200), (2, '2021-01-11', 200), (2, '2021-01-12', 200);
-- 用户id 首次销售日期 30天内累计销售金额
SELECT s.user_id, s.first_day, SUM(t3.amount) AS total
FROM
(SELECT user_id, MIN(sell_day) AS first_day FROM t3 GROUP BY user_id) s
JOIN t3 ON (t3.user_id = s.user_id AND t3.sell_day BETWEEN s.first_day AND s.first_day + INTERVAL '30' DAY)
GROUP BY s.user_id, s.first_day;
某互联网公司SQL面试题:计算直播间的人气值
某互联网(直播带货)公司数据分析师岗位SQL面试题解析,这一题要求计算直播间人气值ACU。ACU 为平均同时在线人数(Average concurrent users),计算方式为:观众侧观看时长/某场直播的开播时长,没有人观看的时候显示为 0。
-- 开播记录表
CREATE TABLE t1 (author_id integer, live_id integer, live_duration integer);
INSERT INTO t1 VALUES (1, 1, 60), (2, 2, 120), (3, 3, 60);
-- 观看记录表
CREATE TABLE t2 (user_id integer, live_id integer, watching_duration integer);
INSERT INTO t2 VALUES (11, 1, 60), (12, 1, 30), (13, 1, 60);
INSERT INTO t2 VALUES (12, 2, 30), (14, 2, 90);
SELECT t1.author_id, t1.live_id, COALESCE(SUM(t2.watching_duration)/t1.live_duration, 0) AS acu
FROM t1
LEFT JOIN t2 ON (t1.live_id = t2.live_id)
GROUP BY t1.author_id, t1.live_id;
SQL案例分析:地铁换乘线路查询
介绍了SQL递归查询(WITH)的原理,利用递归获取北京查询地铁换乘路线,例如从“王府井”到“积水潭”。
-- 创建地铁线路表
CREATE TABLE bj_subway(
station_id INT NOT NULL PRIMARY KEY,
line_name VARCHAR(20) NOT NULL,
station_name VARCHAR(50) NOT NULL,
next_station VARCHAR(50) NOT NULL,
direction VARCHAR(50) NOT NULL
);
-- 初始化数据,目前只有1号线、2号线和8号线的数据
INSERT INTO bj_subway VALUES (1,'1号线','苹果园','古城','苹果园—四惠东');
INSERT INTO bj_subway VALUES (2,'1号线','古城','八角游乐园','苹果园—四惠东');
INSERT INTO bj_subway VALUES (3,'1号线','八角游乐园','八宝山','苹果园—四惠东');
INSERT INTO bj_subway VALUES (4,'1号线','八宝山','玉泉路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (5,'1号线','玉泉路','五棵松','苹果园—四惠东');
INSERT INTO bj_subway VALUES (6,'1号线','五棵松','万寿路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (7,'1号线','万寿路','公主坟','苹果园—四惠东');
INSERT INTO bj_subway VALUES (8,'1号线','公主坟','军事博物馆','苹果园—四惠东');
INSERT INTO bj_subway VALUES (9,'1号线','军事博物馆','木樨地','苹果园—四惠东');
INSERT INTO bj_subway VALUES (10,'1号线','木樨地','南礼士路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (11,'1号线','南礼士路','复兴门','苹果园—四惠东');
INSERT INTO bj_subway VALUES (12,'1号线','复兴门','西单','苹果园—四惠东');
INSERT INTO bj_subway VALUES (13,'1号线','西单','天安门西','苹果园—四惠东');
INSERT INTO bj_subway VALUES (14,'1号线','天安门西','天安门东','苹果园—四惠东');
INSERT INTO bj_subway VALUES (15,'1号线','天安门东','王府井','苹果园—四惠东');
INSERT INTO bj_subway VALUES (16,'1号线','王府井','东单','苹果园—四惠东');
INSERT INTO bj_subway VALUES (17,'1号线','东单','建国门','苹果园—四惠东');
INSERT INTO bj_subway VALUES (18,'1号线','建国门','永安里','苹果园—四惠东');
INSERT INTO bj_subway VALUES (19,'1号线','永安里','国贸','苹果园—四惠东');
INSERT INTO bj_subway VALUES (20,'1号线','国贸','大望路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (21,'1号线','大望路','四惠','苹果园—四惠东');
INSERT INTO bj_subway VALUES (22,'1号线','四惠','四惠东','苹果园—四惠东');
INSERT INTO bj_subway VALUES (23,'1号线','四惠东','四惠','四惠东—苹果园');
INSERT INTO bj_subway VALUES (24,'1号线','四惠','大望路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (25,'1号线','大望路','国贸','四惠东—苹果园');
INSERT INTO bj_subway VALUES (26,'1号线','国贸','永安里','四惠东—苹果园');
INSERT INTO bj_subway VALUES (27,'1号线','永安里','建国门','四惠东—苹果园');
INSERT INTO bj_subway VALUES (28,'1号线','建国门','东单','四惠东—苹果园');
INSERT INTO bj_subway VALUES (29,'1号线','东单','王府井','四惠东—苹果园');
INSERT INTO bj_subway VALUES (30,'1号线','王府井','天安门东','四惠东—苹果园');
INSERT INTO bj_subway VALUES (31,'1号线','天安门东','天安门西','四惠东—苹果园');
INSERT INTO bj_subway VALUES (32,'1号线','天安门西','西单','四惠东—苹果园');
INSERT INTO bj_subway VALUES (33,'1号线','西单','复兴门','四惠东—苹果园');
INSERT INTO bj_subway VALUES (34,'1号线','复兴门','南礼士路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (35,'1号线','南礼士路','木樨地','四惠东—苹果园');
INSERT INTO bj_subway VALUES (36,'1号线','木樨地','军事博物馆','四惠东—苹果园');
INSERT INTO bj_subway VALUES (37,'1号线','军事博物馆','公主坟','四惠东—苹果园');
INSERT INTO bj_subway VALUES (38,'1号线','公主坟','万寿路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (39,'1号线','万寿路','五棵松','四惠东—苹果园');
INSERT INTO bj_subway VALUES (40,'1号线','五棵松','玉泉路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (41,'1号线','玉泉路','八宝山','四惠东—苹果园');
INSERT INTO bj_subway VALUES (42,'1号线','八宝山','八角游乐园','四惠东—苹果园');
INSERT INTO bj_subway VALUES (43,'1号线','八角游乐园','古城','四惠东—苹果园');
INSERT INTO bj_subway VALUES (44,'1号线','古城','苹果园','四惠东—苹果园');
INSERT INTO bj_subway VALUES (45,'2号线','西直门','积水潭','外环');
INSERT INTO bj_subway VALUES (46,'2号线','积水潭','鼓楼大街','外环');
INSERT INTO bj_subway VALUES (47,'2号线','鼓楼大街','安定门','外环');
INSERT INTO bj_subway VALUES (48,'2号线','安定门','雍和宫','外环');
INSERT INTO bj_subway VALUES (49,'2号线','雍和宫','东直门','外环');
INSERT INTO bj_subway VALUES (50,'2号线','东直门','东四十条','外环');
INSERT INTO bj_subway VALUES (51,'2号线','东四十条','朝阳门','外环');
INSERT INTO bj_subway VALUES (52,'2号线','朝阳门','建国门','外环');
INSERT INTO bj_subway VALUES (53,'2号线','建国门','北京站','外环');
INSERT INTO bj_subway VALUES (54,'2号线','北京站','崇文门','外环');
INSERT INTO bj_subway VALUES (55,'2号线','崇文门','前门','外环');
INSERT INTO bj_subway VALUES (56,'2号线','前门','和平门','外环');
INSERT INTO bj_subway VALUES (57,'2号线','和平门','宣武门','外环');
INSERT INTO bj_subway VALUES (58,'2号线','宣武门','长椿街','外环');
INSERT INTO bj_subway VALUES (59,'2号线','长椿街','复兴门','外环');
INSERT INTO bj_subway VALUES (60,'2号线','复兴门','阜成门','外环');
INSERT INTO bj_subway VALUES (61,'2号线','阜成门','车公庄','外环');
INSERT INTO bj_subway VALUES (62,'2号线','车公庄','西直门','外环');
INSERT INTO bj_subway VALUES (63,'2号线','车公庄','阜成门','内环');
INSERT INTO bj_subway VALUES (64,'2号线','阜成门','复兴门','内环');
INSERT INTO bj_subway VALUES (65,'2号线','复兴门','长椿街','内环');
INSERT INTO bj_subway VALUES (66,'2号线','长椿街','宣武门','内环');
INSERT INTO bj_subway VALUES (67,'2号线','宣武门','和平门','内环');
INSERT INTO bj_subway VALUES (68,'2号线','和平门','前门','内环');
INSERT INTO bj_subway VALUES (69,'2号线','前门','崇文门','内环');
INSERT INTO bj_subway VALUES (70,'2号线','崇文门','北京站','内环');
INSERT INTO bj_subway VALUES (71,'2号线','北京站','建国门','内环');
INSERT INTO bj_subway VALUES (72,'2号线','建国门','朝阳门','内环');
INSERT INTO bj_subway VALUES (73,'2号线','朝阳门','东四十条','内环');
INSERT INTO bj_subway VALUES (74,'2号线','东四十条','东直门','内环');
INSERT INTO bj_subway VALUES (75,'2号线','东直门','雍和宫','内环');
INSERT INTO bj_subway VALUES (76,'2号线','雍和宫','安定门','内环');
INSERT INTO bj_subway VALUES (77,'2号线','安定门','鼓楼大街','内环');
INSERT INTO bj_subway VALUES (78,'2号线','鼓楼大街','积水潭','内环');
INSERT INTO bj_subway VALUES (79,'2号线','积水潭','西直门','内环');
INSERT INTO bj_subway VALUES (80,'2号线','西直门','车公庄','外环');
INSERT INTO bj_subway VALUES (81, '8号线', '朱辛庄', '育知路', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (82, '8号线', '育知路', '平西府', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (83, '8号线', '平西府', '回龙观东大街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (84, '8号线', '回龙观东大街', '霍营', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (85, '8号线', '霍营', '育新', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (86, '8号线', '育新', '西小口', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (87, '8号线', '西小口', '永泰庄', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (88, '8号线', '永泰庄', '林萃桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (89, '8号线', '林萃桥', '森林公园南门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (90, '8号线', '森林公园南门', '奥林匹克公园', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (91, '8号线', '奥林匹克公园', '奥体中心', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (92, '8号线', '奥体中心', '北土城', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (93, '8号线', '北土城', '安华桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (94, '8号线', '安华桥', '安德里北街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (95, '8号线', '安德里北街', '鼓楼大街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (96, '8号线', '鼓楼大街', '什刹海', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (97, '8号线', '什刹海', '南锣鼓巷', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (98, '8号线', '南锣鼓巷', '中国美术馆', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (99, '8号线', '中国美术馆', '金鱼胡同', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (100, '8号线', '金鱼胡同', '王府井', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (101, '8号线', '王府井', '前门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (102, '8号线', '前门', '珠市口', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (103, '8号线', '珠市口', '天桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (104, '8号线', '天桥', '永定门外', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (105, '8号线', '永定门外', '木樨园', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (106, '8号线', '木樨园', '海户屯', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (107, '8号线', '海户屯', '大红门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (108, '8号线', '大红门', '大红门南', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (109, '8号线', '大红门南', '和义', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (110, '8号线', '和义', '东高地', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (111, '8号线', '东高地', '火箭万源', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (112, '8号线', '火箭万源', '五福堂', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (113, '8号线', '五福堂', '德茂', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (114, '8号线', '德茂', '瀛海', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (115, '8号线', '瀛海', '德茂', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (116, '8号线', '德茂', '五福堂', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (117, '8号线', '五福堂', '火箭万源', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (118, '8号线', '火箭万源', '东高地', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (119, '8号线', '东高地', '和义', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (120, '8号线', '和义', '大红门南', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (121, '8号线', '大红门南', '大红门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (122, '8号线', '大红门', '海户屯', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (123, '8号线', '海户屯', '木樨园', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (124, '8号线', '木樨园', '永定门外', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (125, '8号线', '永定门外', '天桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (126, '8号线', '天桥', '珠市口', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (127, '8号线', '珠市口', '前门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (128, '8号线', '前门', '王府井', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (129, '8号线', '王府井', '金鱼胡同', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (130, '8号线', '金鱼胡同', '中国美术馆', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (131, '8号线', '中国美术馆', '南锣鼓巷', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (132, '8号线', '南锣鼓巷', '什刹海', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (133, '8号线', '什刹海', '鼓楼大街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (134, '8号线', '鼓楼大街', '安德里北街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (135, '8号线', '安德里北街', '安华桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (136, '8号线', '安华桥', '北土城', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (137, '8号线', '北土城', '奥体中心', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (138, '8号线', '奥体中心', '奥林匹克公园', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (139, '8号线', '奥林匹克公园', '森林公园南门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (140, '8号线', '森林公园南门', '林萃桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (141, '8号线', '林萃桥', '永泰庄', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (142, '8号线', '永泰庄', '西小口', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (143, '8号线', '西小口', '育新', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (144, '8号线', '育新', '霍营', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (145, '8号线', '霍营', '回龙观东大街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (146, '8号线', '回龙观东大街', '平西府', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (147, '8号线', '平西府', '育知路', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (148, '8号线', '育知路', '朱辛庄', '瀛海-朱辛庄');
with recursive temp(start_station,stop_station,stops,paths) as (
select station_name,next_station,1 stops,cast(concat(line_name,station_name,'->',line_name,next_station) as char(1000)) paths
from bj_subway where station_name='王府井'
union all
select t.stop_station,s.next_station,stops+1,concat(paths,'->',s.line_name,s.next_station)
from temp t join bj_subway s on (t.stop_station=s.station_name and instr(paths,s.next_station)=0)
)
select * from temp where stop_station='积水潭' order by stops limit 3;
SQL案例分析:银行可疑支付交易监控
通过SQL窗口函数实现银行等金融机构可疑支付交易监控,包括短期累积大额转账,相同收付款人频繁转账交易。
-- 创建交易流水表
CREATE TABLE transfer_log
( log_id INTEGER NOT NULL PRIMARY KEY,
log_ts TIMESTAMP NOT NULL,
from_user VARCHAR(50) NOT NULL,
to_user VARCHAR(50),
type VARCHAR(10) NOT NULL,
amount NUMERIC(10) NOT NULL
);
-- 初始化数据
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2019-01-02 10:31:40','62221230000000',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2019-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2019-01-03 08:14:29','62221234567890','62226666666666','转账',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2019-01-05 13:55:38','62221234567890','62226666666666','转账',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2019-01-07 20:00:31','62221234567890','62227777777777','转账',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2019-01-09 17:28:07','62221234567890','62227777777777','转账',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2019-01-10 07:46:02','62221234567890','62227777777777','转账',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2019-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2019-01-12 07:10:01','62221234567890','62228888888881','转账',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2019-01-12 07:11:12','62221234567890','62228888888882','转账',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2019-01-12 07:12:36','62221234567890','62228888888883','转账',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2019-01-12 07:13:55','62221234567890','62228888888884','转账',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2019-01-12 07:14:24','62221234567890','62228888888885','转账',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2019-01-21 12:11:16','62221234567890','62228888888885','转账',70000);
-- 找出5天之内转账超过100万的账户
select * from (
select *,
sum(amount) over (partition by from_user order by log_ts
range between interval '5' day preceding and current row ) total
from transfer_log where type='转账'
) temp where total>=1000000;
-- 找出相同收付款人5天之内转账超过3次的账户
select *
from (
select *,
count(*) over (partition by from_user,to_user order by log_ts
range between interval '5' day preceding and current row ) times
from transfer_log where type='转账'
)tmep where times>=3;
SQL案例分析:微信好友关系
利用SQL递归查询语句(WITH)分析社交网络(微信等)中的好友关系,包括推荐好友。关系链分析等。
-- 创建示例表
create table t_user(user_id int primary key, user_name varchar(50) not null);
insert into t_user values(1, '刘一');
insert into t_user values(2, '陈二');
insert into t_user values(3, '张三');
insert into t_user values(4, '李四');
insert into t_user values(5, '王五');
insert into t_user values(6, '赵六');
insert into t_user values(7, '孙七');
insert into t_user values(8, '周八');
insert into t_user values(9, '吴九');
create table t_friend(
user_id int not null,
friend_id int not null,
created_time timestamp not null,
primary key (user_id, friend_id)
);
insert into t_friend values(1, 2, current_timestamp);
insert into t_friend values(2, 1, current_timestamp);
insert into t_friend values(1, 3, current_timestamp);
insert into t_friend values(3, 1, current_timestamp);
insert into t_friend values(1, 4, current_timestamp);
insert into t_friend values(4, 1, current_timestamp);
insert into t_friend values(1, 7, current_timestamp);
insert into t_friend values(7, 1, current_timestamp);
insert into t_friend values(1, 8, current_timestamp);
insert into t_friend values(8, 1, current_timestamp);
insert into t_friend values(2, 3, current_timestamp);
insert into t_friend values(3, 2, current_timestamp);
insert into t_friend values(2, 5, current_timestamp);
insert into t_friend values(5, 2, current_timestamp);
insert into t_friend values(3, 4, current_timestamp);
insert into t_friend values(4, 3, current_timestamp);
insert into t_friend values(4, 6, current_timestamp);
insert into t_friend values(6, 4, current_timestamp);
insert into t_friend values(5, 8, current_timestamp);
insert into t_friend values(8, 5, current_timestamp);
insert into t_friend values(7, 8, current_timestamp);
insert into t_friend values(8, 7, current_timestamp);
-- 查找好友列表
select f.user_id,f.friend_id
from t_friend f
join t_user u on f.user_id = u.user_id
where f.user_id=1;
-- 查找用户1和3的共同好友
with f1(user_id,user_name) as (
select u.user_id,u.user_name
from t_friend f
join t_user u on u.user_id = f.friend_id
where f.user_id=1
),
f2(user_id,user_name) as (
select u.user_id,u.user_name
from t_friend f
join t_user u on u.user_id = f.friend_id
where f.user_id=3
)
select f1.*
from f1 join f2 on f1.user_id=f2.user_id;
-- 好友推荐,给用户2推荐好友
with f1(user_id,user_name) as ( -- f1:用户2的好友
select u.user_id,u.user_name
from t_friend f
join t_user u on u.user_id = f.friend_id
where f.user_id=2
),
fof(user_id,user_name) as ( -- fof:好友的好友
select u.user_id,u.user_name
from t_friend f
join t_user u on u.user_id = f.friend_id
join f1 on f1.user_id = f.user_id
where f.friend_id != 2 -- 排除自己
and f.friend_id not in (select f1.user_id from f1) -- 排除f1的直接好友
)
select user_id,user_name,count(*) from fof -- count:共同好友的数量
group by user_id,user_name order by count(*) desc;
-- 用户6的关系网,
with recursive temp(user_id,friend_id,hops,paths) as (
select user_id,friend_id,0 hops,concat(',',user_id,',',friend_id) paths from t_friend where user_id = 6
union all
select t.user_id,f.friend_id,hops+1,concat(paths,',',f.friend_id) from temp t
join t_friend f on (t.friend_id = f.user_id and instr(paths, concat(',',f.friend_id,','))=0)
where hops <= 6
)
select user_id,friend_id,hops,substr(paths,2) from temp where friend_id=7;
-- 用户的平均距离
with recursive temp(user_id,friend_id,hops,paths) as (
select user_id,friend_id,0 hops,concat(',',user_id,',',friend_id) paths from t_friend
union all
select t.user_id,f.friend_id,hops+1,concat(paths,',',f.friend_id) from temp t
join t_friend f on (t.friend_id = f.user_id and instr(paths, concat(',',f.friend_id,','))=0)
),
mh as (
select user_id,friend_id,min(hops) min_hops from temp group by user_id,friend_id
)
select avg(min_hops)
from mh;
SQL面试题:索引和性能优化
SQL面试题,主要考察索引的原理和性能优化,使用EXPLIAN查询执行计划。
索引字段不能使用任何计算,不能使用公式、函数,否则索引会失效
命中索引,无性能问题
复合索引遵循最左匹配原则,针对本例可以在创建索引时col2放在col1前面,即可都命中索引
未命中索引,like查询的时候%放在后面才能命中索引
命中索引,且满足索引覆盖,但是using index比using where性能高,using where需要回表
SQL案例分析:京东、亚马逊销量排行榜
介绍如何利用SQL窗口函数RANK和LAG实现电商平台(京东、亚马逊)的销量排行榜以及销量飙升榜。
-- 创建示例表
create table products(
product_id integer not null primary key,
product_name varchar(100) not null unique,
product_subcategory varchar(100) not null,
product_category varchar(100) not null
);
insert into products values(1, 'iPhone 11', '手机', '手机通讯');
insert into products values(2, 'HUAWEI P40', '手机', '手机通讯');
insert into products values(3, '小米10', '手机', '手机通讯');
insert into products values(4, 'OPPO Reno4', '手机', '手机通讯');
insert into products values(5, 'vivo Y70s', '手机', '手机通讯');
insert into products values(6, '海尔BCD-216STPT', '冰箱', '大家电');
insert into products values(7, '康佳BCD-155C2GBU', '冰箱', '大家电');
insert into products values(8, '容声BCD-529WD11HP', '冰箱', '大家电');
insert into products values(9, '美的BCD-213TM(E)', '冰箱', '大家电');
insert into products values(10, '格力BCD-230WETCL', '冰箱', '大家电');
insert into products values(11, '格力KFR-35GW', '空调', '大家电');
insert into products values(12, '美的KFR-35GW', '空调', '大家电');
insert into products values(13, 'TCLKFRd-26GW', '空调', '大家电');
insert into products values(14, '奥克斯KFR-35GW', '空调', '大家电');
insert into products values(15, '海尔KFR-35GW', '空调', '大家电');
create table sales(
product_id integer not null,
sale_time timestamp not null,
quantity integer not null
);
-- 生成模拟销量数据
insert into sales
with recursive s(product_id, sale_time, quantity) as (
select product_id, '2022-04-01 00:00:00', floor(10*rand(0)) from products
union all
select product_id, sale_time + interval 1 minute, floor(10*rand(0))
from s
where sale_time < '2022-04-01 10:00:00'
)
select * from s;
# 按照产品的分类,计算2022-04-01 09:00:00到2022-04-01 09:59:59一小时的销量排名
-- MySQL,其他数据库需要替换date_format函数
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%Y%m%d%H'), sum(quantity)
from sales
where sale_time between '2022-04-01 09:00:00' and '2022-04-01 09:59:59'
group by product_id, date_format(sale_time, '%Y%m%d%H')
),
hourly_rank as(
select product_category, product_subcategory, product_name, quantity,
rank() over (partition by ymdh, product_category order by quantity desc) as rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
)
select *
from hourly_rank;
# 按照产品子类排名
-- MySQL,其他数据库需要替换date_format函数
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%Y%m%d%H'), sum(quantity)
from sales
where sale_time between '2022-04-01 09:00:00' and '2022-04-01 09:59:59'
group by product_id, date_format(sale_time, '%Y%m%d%H')
),
hourly_rank as(
select product_category, product_subcategory, product_name, quantity,
rank() over (partition by ymdh, product_category, product_subcategory order by quantity desc) as sub_rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
)
select *
from hourly_rank;
# 按照产品分类的飙升榜
-- MySQL,其他数据库需要替换date_format函数和ifnull函数
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%y%m%d%H'), sum(quantity)
from sales
where sale_time between '2022-04-01 08:00:00' and '2022-04-01 09:59:59'
group by product_id, date_format(sale_time, '%y%m%d%H')
),
hourly_rank as(
select ymdh, product_category, product_subcategory, product_name,
rank() over (partition by ymdh, product_category order by quantity desc) as rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
),
rank_gain as(
select product_category, product_subcategory, product_name,
rk, lag(rk, 1) over (partition by product_category, product_name order by ymdh) pre_rk,
100 * (ifnull(lag(rk, 1) over (partition by product_category, product_name order by ymdh), 9999) - rk)
/rk as gain
from hourly_rank
),
top_gain as(
select *, rank() over (partition by product_category order by gain desc) gain_rk
from rank_gain
where pre_rk is not null
)
select product_category, product_subcategory, product_name, pre_rk, rk, concat(gain,'%') gain, gain_rk
from top_gain;
SQL案例分析:同比、环比以及复合增长率
使用SQL窗口函数解决财务、销售报表中的同比增长率、环比增长率以及复合增长率问题。
-- 创建销量表sales_monthly
-- product表示产品名称,ym表示年月,amount表示销售金额(元)
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);
-- 环比增长率
SELECT product AS "产品", ym "年月", amount "本期销量",
LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym) "上期销量",
((amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym))/
LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)) * 100
AS "环比增长率(%)"
FROM sales_monthly
ORDER BY product, ym;
-- 同比增长率
SELECT product AS "产品", ym "年月", amount "本期销量",
LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym) "去年同期销量",
((amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym))/
LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) * 100
AS "同比增长率(%)"
FROM sales_monthly
ORDER BY product, ym;
-- 月均复合增长率
WITH s(product, ym, amount, first_amount, num) AS (
SELECT product, ym, amount,
FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY ym),
ROW_NUMBER() OVER (PARTITION BY product ORDER BY ym)
FROM sales_monthly
)
SELECT product AS "产品", ym "年月", amount "销量",
(POWER(1.0*amount/first_amount, 1.0/NULLIF(num-1, 0)) - 1) * 100
AS "月均复合增长率(%)"
FROM s
ORDER BY product, ym;
SQL案例分析:保护个人信息,隐藏敏感数据
介绍如何利用 SQL 字符串函数将用户姓名、手机号、身份证号以及银行卡号等的部分内容显示为星号(*),从而实现信息的隐藏,保护信息安全。
-- 创建示例表
CREATE TABLE users
( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(50) NOT NULL
, phone VARCHAR(20) NOT NULL
, id_card VARCHAR(500) NOT NULL
);
INSERT INTO users VALUES (1, '张三', '13512345678', '320101200206014057');
INSERT INTO users VALUES (2, '李一飞', '+8613512340000', '320101199901011212');
INSERT INTO users VALUES (3, '诸葛不亮', '+861013512341234', '32010119880202567X');
-- MySQL
# 隐藏姓名
SELECT name "隐藏之前",
CASE char_length(name)
WHEN 2 THEN concat('*', substr(name, 2, 1))
WHEN 3 THEN concat(substr(name, 1, char_length(name)-2), '*', substr(name, -1, 1))
ELSE concat(substr(name, 1, char_length(name)-2), '**')
END "隐藏之后"
FROM users;
# 隐藏手机号
SELECT phone "隐藏之前",
insert(phone, char_length(phone)-7, 4, '****') "隐藏之后"
FROM users;
# 隐藏身份证号
SELECT id_card "隐藏之前",
insert(id_card, 7, 8, '*******') "隐藏之后"
FROM users;
SQL案例分析:字符串的合并与拆分
介绍如何使用SQL聚合函数group_concat/string_agg/listagg将多行字符串合并成一行数据,以及使用递归查询(WITH)将一行字符串拆分成多行数据。
-- 创建 3 个示例表
CREATE TABLE department
( dept_id INTEGER NOT NULL PRIMARY KEY
, dept_name VARCHAR(50) NOT NULL
) ;
CREATE TABLE job
( job_id INTEGER NOT NULL PRIMARY KEY
, job_title VARCHAR(50) NOT NULL
) ;
CREATE TABLE employee
( emp_id INTEGER NOT NULL PRIMARY KEY
, emp_name VARCHAR(50) NOT NULL
, sex VARCHAR(10) NOT NULL
, dept_id INTEGER NOT NULL
, manager INTEGER
, hire_date DATE NOT NULL
, job_id INTEGER NOT NULL
, salary NUMERIC(8,2) NOT NULL
, bonus NUMERIC(8,2)
, email VARCHAR(100) NOT NULL
, CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
, CONSTRAINT ck_emp_salary CHECK (salary > 0)
, CONSTRAINT uk_emp_email UNIQUE (email)
, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
, CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
, CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
) ;
CREATE INDEX idx_emp_name ON employee(emp_name);
CREATE INDEX idx_emp_dept ON employee(dept_id);
CREATE INDEX idx_emp_job ON employee(job_id);
CREATE INDEX idx_emp_manager ON employee(manager);
-- 生成 MySQL 初始化数据
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部');
INSERT INTO department(dept_id, dept_name) VALUES (2, '人力资源部');
INSERT INTO department(dept_id, dept_name) VALUES (3, '财务部');
INSERT INTO department(dept_id, dept_name) VALUES (4, '研发部');
INSERT INTO department(dept_id, dept_name) VALUES (5, '销售部');
INSERT INTO department(dept_id, dept_name) VALUES (6, '保卫部');
INSERT INTO job(job_id, job_title) VALUES (1, '总经理');
INSERT INTO job(job_id, job_title) VALUES (2, '副总经理');
INSERT INTO job(job_id, job_title) VALUES (3, '人力资源总监');
INSERT INTO job(job_id, job_title) VALUES (4, '人力资源专员');
INSERT INTO job(job_id, job_title) VALUES (5, '财务经理');
INSERT INTO job(job_id, job_title) VALUES (6, '会计');
INSERT INTO job(job_id, job_title) VALUES (7, '开发经理');
INSERT INTO job(job_id, job_title) VALUES (8, '程序员');
INSERT INTO job(job_id, job_title) VALUES (9, '销售经理');
INSERT INTO job(job_id, job_title) VALUES (10, '销售人员');
INSERT INTO employee VALUES (1, '刘备', '男', 1, NULL, DATE('2000-01-01'), 1, 30000, 10000, 'liubei@shuguo.com');
INSERT INTO employee VALUES (2, '关羽', '男', 1, 1, DATE('2000-01-01'), 2, 26000, 10000, 'guanyu@shuguo.com');
INSERT INTO employee VALUES (3, '张飞', '男', 1, 1, DATE('2000-01-01'), 2, 24000, 10000, 'zhangfei@shuguo.com');
INSERT INTO employee VALUES (4, '诸葛亮', '男', 2, 1, DATE('2006-03-15'), 3, 24000, 8000, 'zhugeliang@shuguo.com');
INSERT INTO employee VALUES (5, '黄忠', '男', 2, 4, DATE('2008-10-25'), 4, 8000, NULL, 'huangzhong@shuguo.com');
INSERT INTO employee VALUES (6, '魏延', '男', 2, 4, DATE('2007-04-01'), 4, 7500, NULL, 'weiyan@shuguo.com');
INSERT INTO employee VALUES (7, '孙尚香', '女', 3, 1, DATE('2002-08-08'), 5, 12000, 5000, 'sunshangxiang@shuguo.com');
INSERT INTO employee VALUES (8, '孙丫鬟', '女', 3, 7, DATE('2002-08-08'), 6, 6000, NULL, 'sunyahuan@shuguo.com');
INSERT INTO employee VALUES (9, '赵云', '男', 4, 1, DATE('2005-12-19'), 7, 15000, 6000, 'zhaoyun@shuguo.com');
INSERT INTO employee VALUES (10, '廖化', '男', 4, 9, DATE('2009-02-17'), 8, 6500, NULL, 'liaohua@shuguo.com');
INSERT INTO employee VALUES (11, '关平', '男', 4, 9, DATE('2011-07-24'), 8, 6800, NULL, 'guanping@shuguo.com');
INSERT INTO employee VALUES (12, '赵氏', '女', 4, 9, DATE('2011-11-10'), 8, 6600, NULL, 'zhaoshi@shuguo.com');
INSERT INTO employee VALUES (13, '关兴', '男', 4, 9, DATE('2011-07-30'), 8, 7000, NULL, 'guanxing@shuguo.com');
INSERT INTO employee VALUES (14, '张苞', '男', 4, 9, DATE('2012-05-31'), 8, 6500, NULL, 'zhangbao@shuguo.com');
INSERT INTO employee VALUES (15, '赵统', '男', 4, 9, DATE('2012-05-03'), 8, 6000, NULL, 'zhaotong@shuguo.com');
INSERT INTO employee VALUES (16, '周仓', '男', 4, 9, DATE('2010-02-20'), 8, 8000, NULL, 'zhoucang@shuguo.com');
INSERT INTO employee VALUES (17, '马岱', '男', 4, 9, DATE('2014-09-16'), 8, 5800, NULL, 'madai@shuguo.com');
INSERT INTO employee VALUES (18, '法正', '男', 5, 2, DATE('2017-04-09'), 9, 10000, 5000, 'fazheng@shuguo.com');
INSERT INTO employee VALUES (19, '庞统', '男', 5, 18, DATE('2017-06-06'), 10, 4100, 2000, 'pangtong@shuguo.com');
INSERT INTO employee VALUES (20, '蒋琬', '男', 5, 18, DATE('2018-01-28'), 10, 4000, 1500, 'jiangwan@shuguo.com');
INSERT INTO employee VALUES (21, '黄权', '男', 5, 18, DATE('2018-03-14'), 10, 4200, NULL, 'huangquan@shuguo.com');
INSERT INTO employee VALUES (22, '糜竺', '男', 5, 18, DATE('2018-03-27'), 10, 4300, NULL, 'mizhu@shuguo.com');
INSERT INTO employee VALUES (23, '邓芝', '男', 5, 18, DATE('2018-11-11'), 10, 4000, NULL, 'dengzhi@shuguo.com');
INSERT INTO employee VALUES (24, '简雍', '男', 5, 18, DATE('2019-05-11'), 10, 4800, NULL, 'jianyong@shuguo.com');
INSERT INTO employee VALUES (25, '孙乾', '男', 5, 18, DATE('2018-10-09'), 10, 4700, NULL, 'sunqian@shuguo.com');
# 将每个部门中的多个员工姓名合并成一行数据,多个姓名之间使用分号进行分隔
SELECT d.dept_name, GROUP_CONCAT(e.emp_name)
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name;
SELECT d.dept_name, GROUP_CONCAT(e.emp_name SEPARATOR ';')
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name;
# 对于每个部门中的员工,按照入职日期进行排序,入职日期相同按照工号进行排序
SELECT d.dept_name, GROUP_CONCAT(e.emp_name ORDER BY e.hire_date, e.emp_id SEPARATOR ';')
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name;
# 使用DISTINCT排除每个分组中的重复数据
SELECT d.dept_name, GROUP_CONCAT(DISTINCT sex)
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY dept_name;
# 创建示例表合数据
CREATE TABLE movies(id int primary key, name varchar(50), class varchar(200));
INSERT INTO movies VALUES (1, '千与千寻', '动画、剧情、奇幻');
INSERT INTO movies VALUES (2, '阿甘正传', '剧情、爱情');
INSERT INTO movies VALUES (3, '唐伯虎点秋香', '喜剧、古装、爱情’);
# 拆分字符串
WITH RECURSIVE t(id, name, sub, str) AS (
SELECT id, name, substr(concat(class,'、'), 1, instr(concat(class,'、'), '、')-1), substr(concat(class,'、'), instr(concat(class,'、'), '、')+1)
FROM movies
UNION ALL
SELECT id, name,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
FROM t WHERE instr(str, '、')>0
)
SELECT id, name, sub, str
FROM t
ORDER BY id;
SQL案例分析:分页查询,你写对了吗?
分页查询是指为了改善前端用户的体验和系统性能,将查询结果分批返回和展示。分页查询常用的两种方式:OFFSET分页,利用SQL标准OFFSET FETCH或者LIMIT OFFSET子句指定偏移量和返回的行数,性能随着偏移量的增加明显下降。Keyset分页,利用每次返回的记录集查找下一次的数据,性能不受数据量和偏移量的影响。可以实现页面无限滚动效果。
-- 创建示例表
CREATE TABLE users(
id integer PRIMARY KEY,
name varchar(50) NOT NULL,
pswd varchar(50) NOT NULL,
email varchar(50),
create_time timestamp NOT NULL,
notes varchar(200)
);
-- 生成示例数据
-- MySQL语法
INSERT INTO users(id, name, pswd, email,create_time)
WITH RECURSIVE t(id, name, pswd, email,create_time) AS (
SELECT 1, CAST(concat('user', 1) AS char(50)), 'e10adc3949ba59abbe56e057f20f883e', CAST(concat('user',1,'@test.com') AS char(50)), '2020-01-01 00:00:00'
UNION ALL
SELECT id+1, concat('user', id+1), pswd, concat('user',id+1,'@test.com'), create_time+ INTERVAL mod(id,2) MINUTE
FROM t WHERE id<1000000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */* FROM t;
-- 创建索引
CREATE INDEX idx_user_ct ON users(create_time);
-- OFFSET分页
SELECT count(*) FROM users;
EXPLAIN
SELECT *
FROM users
ORDER BY create_time, id
LIMIT 20 offset 100000;
-- KEYSET分页
EXPLAIN
SELECT *
FROM users
WHERE create_time>='2020-11-01 00:10:00' and id>20
ORDER BY create_time, id
LIMIT 20;
SQL面试题:基于扫码记录查找密接人员
如何使用SQL语句基于扫码信息获取用户活动轨迹,基于轨迹交集获取时空伴随者(密接人员)。
-- 创建示例表和数据
CREATE TABLE trail(
uid varchar(11) NOT NULL,
area varchar(10) NOT NULL,
scan_time timestamp);
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 09:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 10:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 11:00:00');
INSERT INTO trail VALUES ('13011111111', 'A002', '2022-05-01 11:05:00');
INSERT INTO trail VALUES ('13011111111', 'A002', '2022-05-01 13:00:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 13:15:00');
INSERT INTO trail VALUES ('13011111111', 'A001', '2022-05-01 14:00:00');
INSERT INTO trail VALUES ('13022222222', 'A001', '2022-05-01 10:30:00');
INSERT INTO trail VALUES ('13022222222', 'A001', '2022-05-01 12:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 11:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 12:00:00');
INSERT INTO trail VALUES ('13033333333', 'A001', '2022-05-01 13:00:00');
-- 问题一:如何找出用户在每个区域的停留开始时间和结束时间?
WITH tmp AS (
SELECT uid, area, scan_time,
-- num1 - num2 相同,表示在同一个小区
ROW_NUMBER() over(PARTITION BY uid ORDER BY scan_time) num1,
ROW_NUMBER() over(PARTITION BY uid,area ORDER BY scan_time) num2,
rank() OVER (PARTITION BY uid ORDER BY scan_time) - rank() OVER (PARTITION BY uid, area ORDER BY scan_time) diff
FROM trail)
SELECT uid, area, min(scan_time) start_time, max(scan_time) end_time
FROM tmp
GROUP BY uid, area, diff
ORDER BY uid, start_time;
-- 问题二:假如某个用户核酸检查为阳性,找出他的伴随人员?
WITH tmp AS (
SELECT uid, area, scan_time,
rank() OVER (PARTITION BY uid ORDER BY scan_time) - rank() OVER (PARTITION BY uid, area ORDER BY scan_time) diff
FROM trail),
tmp2 AS (
SELECT uid, area, min(scan_time) start_time, max(scan_time) end_time
FROM tmp
GROUP BY uid, area, diff
HAVING min(scan_time) + INTERVAL 30 MINUTE <= max(scan_time)
)
SELECT *
FROM tmp2 u1
JOIN tmp2 u2
ON (u1.uid <> u2.uid AND u1.area = u2.area
AND u1.start_time + INTERVAL 10 MINUTE <= u2.end_time
AND u2.start_time + INTERVAL 10 MINUTE <= u1.end_time)
WHERE u1.uid = '13011111111';
SQL面试题:连续登录问题
互联网大厂经典SQL面试题,连续登录、连续购买等。
-- 创建示例表
CREATE TABLE t_login(uid int, login_time timestamp);
INSERT INTO t_login VALUES (1, '2022-01-01 08:05:11');
INSERT INTO t_login VALUES (2, '2022-01-01 10:00:00');
INSERT INTO t_login VALUES (3, '2022-01-01 12:13:14');
INSERT INTO t_login VALUES (1, '2022-01-01 19:30:00');
INSERT INTO t_login VALUES (1, '2022-01-02 07:59:30');
INSERT INTO t_login VALUES (2, '2022-01-02 14:00:00');
INSERT INTO t_login VALUES (2, '2022-01-03 11:15:00');
INSERT INTO t_login VALUES (3, '2022-01-03 16:00:00');
INSERT INTO t_login VALUES (1, '2022-01-04 07:20:00');
INSERT INTO t_login VALUES (2, '2022-01-04 07:45:00');
INSERT INTO t_login VALUES (3, '2022-01-04 10:30:30');
INSERT INTO t_login VALUES (1, '2022-01-05 13:00:00');
INSERT INTO t_login VALUES (1, '2022-01-06 17:18:19');
INSERT INTO t_login VALUES (1, '2022-01-07 20:00:00');
INSERT INTO t_login VALUES (2, '2022-01-07 21:00:00');
-- 如何通过SQL查询找出2022年1月连续登录3天以上的用户?
-- 自连接查询
SELECT t1.uid,t1.ymd,t2.ymd,t3.ymd FROM
(SELECT DISTINCT uid,date(login_time) ymd FROM t_login WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59:59') t1
JOIN (SELECT DISTINCT uid,date(login_time) ymd FROM t_login WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59:59') t2 on (t1.uid=t2.uid and DATEDIFF(t2.ymd,t1.ymd)=1)
JOIN (SELECT DISTINCT uid, date(login_time) ymd FROM t_login WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND '2022-01-31 23:59:59') t3 ON (t2.uid = t3.uid AND datediff(t3.ymd, t2.ymd)=1);
-- 窗口函数ROW_NUMBER
with t1 AS (
SELECT DISTINCT uid,date(login_time) ymd FROM t_login WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59:59'),
t2 AS (
SELECT uid, ymd, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ymd) num,
DATE_SUB(ymd,INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ymd) DAY) diff
FROM t1
)
SELECT uid,count(*),min(ymd),max(ymd),group_concat(ymd) FROM t2 GROUP BY uid,diff HAVING count(*)>=3;
-- 窗口函数LAG
WITH t1 AS (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'),
t2 AS (
SELECT uid, ymd,
datediff(ymd, lag(ymd, 2) OVER (PARTITION BY uid ORDER BY ymd)) diff
FROM t1)
SELECT uid,date_sub(ymd,INTERVAL 2 day) min_date, ymd max_date, diff
FROM t2
WHERE diff = 2;
SQL案例分析:一条查询语句的执行过程
从客户端建立连接开始,到解析器进行语法、语义分析和权限检查,然后优化器生成执行计划,执行器调用存储引擎获取物理文件中的数据,最后返回客户端。同时数据库为了提高性能,还提供了查询计划缓存和热点数据缓存。
SQL面试题:用户留存率分析
某一天新增用户在之后的第N天仍然登录的比例,称为第N日留存率。使用SQL分析留存率一般有两种方法:多表连接和窗口函数。
-- 示例表
DROP TABLE t_user;
CREATE TABLE t_user(
id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
register_time DATETIME NOT NULL
);
SET SESSION cte_max_recursion_depth=9999999;
INSERT INTO t_user(user_name, register_time)
WITH RECURSIVE t AS (
SELECT 1 n, '2022-01-01 00:00:00' d
UNION ALL
SELECT n+1, d + INTERVAL '1' MINUTE
FROM t
WHERE n<10000
)
SELECT concat('user', n), d FROM t;
SELECT date(register_time), count(*) FROM t_user GROUP BY date(register_time);
DROP TABLE t_user_login;
CREATE TABLE t_user_login(
id INT AUTO_INCREMENT PRIMARY KEY,
uid INT NOT NULL,
login_time DATETIME NOT NULL
);
SET SESSION cte_max_recursion_depth=9999999;
INSERT INTO t_user_login(uid, login_time)
WITH RECURSIVE t AS (
SELECT 1 n, rand()*10000 id, '2022-01-01 00:00:00' d
UNION ALL
SELECT n+1, rand()*10000, d + INTERVAL CEIL(n/3000) second
FROM t
WHERE n<500000
)
SELECT CEIL(id), d FROM t;
-- 多表连接
SELECT date(u.register_time),
100*count(DISTINCT ul1.uid)/count(DISTINCT u.id) rr1,
100*count(DISTINCT ul2.uid)/count(DISTINCT u.id) rr3,
100*count(DISTINCT ul3.uid)/count(DISTINCT u.id) rr7,
100*count(DISTINCT ul4.uid)/count(DISTINCT u.id) rr30
FROM t_user u
LEFT JOIN t_user_login ul1 ON (ul1.uid = u.id AND date(ul1.login_time) = date(u.register_time) + INTERVAL '1' DAY)
LEFT JOIN t_user_login ul2 ON (ul2.uid = u.id AND date(ul2.login_time) = date(u.register_time) + INTERVAL '3' DAY)
LEFT JOIN t_user_login ul3 ON (ul3.uid = u.id AND date(ul3.login_time) = date(u.register_time) + INTERVAL '7' DAY)
LEFT JOIN t_user_login ul4 ON (ul4.uid = u.id AND date(ul4.login_time) = date(u.register_time) + INTERVAL '30' DAY)
GROUP BY date(u.register_time);
-- 窗口函数
WITH t1 AS (
SELECT u.id, u.user_name, date(u.register_time) reg_date, date(l.login_time) login_date,
DENSE_RANK() OVER (PARTITION BY date(u.register_time) ORDER BY u.id) daily_reg,
DENSE_RANK() OVER (PARTITION BY date(u.register_time), date(l.login_time) ORDER BY l.uid) daily_login
FROM t_user u
LEFT JOIN t_user_login l
ON (l.uid = u.id AND date(l.login_time) BETWEEN date(u.register_time) + INTERVAL '1' DAY AND date(u.register_time) + INTERVAL '30' DAY)
),
t2 AS (
SELECT reg_date, max(daily_reg) daily_reg, login_date, max(daily_login) daily_login
FROM t1
GROUP BY reg_date, login_date)
SELECT reg_date, max(daily_reg),
100*max(CASE WHEN login_date = reg_date + INTERVAL '1' DAY THEN daily_login END)/max(daily_reg) rr1,
100*max(CASE WHEN login_date = reg_date + INTERVAL '3' DAY THEN daily_login END)/max(daily_reg) rr3,
100*max(CASE WHEN login_date = reg_date + INTERVAL '7' DAY THEN daily_login END)/max(daily_reg) rr7,
100*max(CASE WHEN login_date = reg_date + INTERVAL '30' DAY THEN daily_login END)/max(daily_reg) rr30
FROM t2
GROUP BY reg_date;
SQL案例分析:年会抽奖程序
分享一个使用SQL实现抽奖的程序,涉及MySQL随机数函数以及存储过程。
CREATE TABLE luck_emp(emp_id int, emp_name varchar(50), prize varchar(10));
delimiter $$
CREATE PROCEDURE luck_draw(IN p_prize varchar(10), IN p_num int)
BEGIN
INSERT INTO luck_emp (emp_id, emp_name, prize)
SELECT emp_id, emp_name, p_prize
FROM employee e
WHERE emp_id NOT IN (SELECT emp_id FROM luck_emp)
ORDER BY RAND()
LIMIT p_num;
SELECT * FROM luck_emp;
END$$
delimiter ;
CALL luck_draw('三等奖', 3);
CALL luck_draw('二等奖', 2);
CALL luck_draw('一等奖', 1);
SQL案例分析:直播间最大在线人数
使用MySQL窗口函数分析直播间最大在线人数。
-- 示例表和数据
CREATE TABLE t_live(live_id int, user_id int, oper_time datetime, oper_type varchar(10));
INSERT INTO t_live(live_id, user_id, oper_time, oper_type)
VALUES (1, 1, '2023-01-01 19:00:00', 'IN'),
(1, 2, '2023-01-01 19:15:00', 'IN'),
(1, 3, '2023-01-01 19:20:00', 'IN'),
(1, 1, '2023-01-01 19:30:00', 'OUT'),
(1, 4, '2023-01-01 19:35:00', 'IN'),
(1, 5, '2023-01-01 19:40:00', 'IN'),
(1, 3, '2023-01-01 19:40:00', 'OUT'),
(1, 2, '2023-01-01 19:50:00', 'OUT');
INSERT INTO t_live(live_id, user_id, oper_time, oper_type)
VALUES (2, 11, '2023-01-01 19:01:00', 'IN'),
(2, 12, '2023-01-01 19:05:00', 'IN'),
(2, 13, '2023-01-01 19:10:00', 'IN'),
(2, 14, '2023-01-01 19:20:00', 'IN'),
(2, 13, '2023-01-01 19:40:00', 'OUT'),
(2, 15, '2023-01-01 19:45:00', 'IN'),
(2, 16, '2023-01-01 19:46:00', 'OUT');
with tmp as (
select live_id, user_id, oper_time, oper_type,
case oper_type when 'IN' then 1 when 'OUT' then -1 else 0 end flag
from t_live),
tmp2 as (
select live_id, user_id, oper_time, oper_type, flag,
sum(flag) over(partition by live_id order by oper_time) total
from tmp),
tmp3 as (
select live_id, user_id, oper_time, oper_type, flag, total,
rank() over(partition by live_id order by total desc) rk
from tmp2)
select distinct live_id, total, oper_time from tmp3 where rk=1;
SQL面试题:连续客流高峰
使用SQL连接查询和窗口函数分析连续客流高峰。
-- 示例表和数据
CREATE TABLE visitor(
id INTEGER NOT NULL AUTO_INCREMENT,
log_date DATE NOT NULL,
num INTEGER NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO visitor(log_date, num)
VALUES ('2022-01-01', 8500),
('2022-01-02', 10000),
('2022-01-03', 11000),
('2022-01-04', 7000),
('2022-01-05', 10000),
('2022-01-06', 12000),
('2022-01-07', 11000),
('2022-01-08', 12000),
('2022-01-09', 6000),
('2022-01-10', 6500);
-- 编写查询语句,找出客流量高峰对应的日期和流量,客流高峰是指前后连续3天流量大于等于一万。
-- 多表连接查询
select * from visitor d1, visitor d2, visitor d3
where d1.num>=10000 and d2.num>=10000 and d3.num>=10000
and d1.log_date = d2.log_date - INTERVAL 1 DAY
and d2.log_date = d3.log_date - INTERVAL 2 DAY;
-- 窗口函数
select * from (
select
lag(log_date) over (partition by null order by log_date) yesterday,
lag(num) over (partition by null order by log_date) yesterday_num,
log_date today, num today_num,
lead(log_date) over (partition by null order by log_date) tomorrow,
lead(num) over (partition by null order by log_date) tomorrow_num
from visitor
) t
where t.yesterday_num>=10000 and t.today_num>=10000 and t.tomorrow_num>=10000
SQL案例分析:生成柱状图
你会用SQL创建柱状图吗?
-- 员工表创建脚本
-- https://github.com/dongxuyang1985/thinking_in_sql
-- 水平柱状图
-- MySQL/MariaDB
SELECT d.dept_name "部门名称",
count(e.emp_id) "员工数量",
repeat('▇', count(e.emp_id)) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;
-- Microsoft SQL Server
SELECT d.dept_name "部门名称",
count(e.emp_id) "员工数量",
replicate('▇', count(e.emp_id)) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;
-- PostgreSQL
SELECT d.dept_name "部门名称",
count(e.emp_id) "员工数量",
repeat('▇', count(e.emp_id)::integer) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;
-- Oracle
SELECT d.dept_name "部门名称",
count(e.emp_id) "员工数量",
lpad('▇', count(e.emp_id), '▇') "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;
-- SQLite
SELECT d.dept_name "部门名称",
count(e.emp_id) "员工数量",
replace(hex(zeroblob(count(e.emp_id))), '00', '█') "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;
-- 垂直柱状图
-- MySQL、Microsoft SQL Server以及SQLite
WITH d AS (
SELECT row_number() OVER (PARTITION BY dept_id ORDER BY emp_id) rn,
CASE WHEN dept_id=1 THEN '█' ELSE '' END dept1,
CASE WHEN dept_id=2 THEN '█' ELSE '' END dept2,
CASE WHEN dept_id=3 THEN '█' ELSE '' END dept3,
CASE WHEN dept_id=4 THEN '█' ELSE '' END dept4,
CASE WHEN dept_id=5 THEN '█' ELSE '' END dept5,
CASE WHEN dept_id=6 THEN '█' ELSE '' END dept6
FROM employee
)
SELECT max(dept1) "行政管理部",
max(dept2) "人力资源部",
max(dept3) "财务部",
max(dept4) "研发部",
max(dept5) "销售部",
max(dept6) "保卫部"
FROM d
GROUP BY rn
ORDER BY 1, 2, 3, 4, 5, 6;
-- Oracle、PostgreSQL
WITH d AS (
SELECT row_number() OVER (PARTITION BY dept_id ORDER BY emp_id) rn,
CASE WHEN dept_id=1 THEN '█' ELSE '' END dept1,
CASE WHEN dept_id=2 THEN '█' ELSE '' END dept2,
CASE WHEN dept_id=3 THEN '█' ELSE '' END dept3,
CASE WHEN dept_id=4 THEN '█' ELSE '' END dept4,
CASE WHEN dept_id=5 THEN '█' ELSE '' END dept5,
CASE WHEN dept_id=6 THEN '█' ELSE '' END dept6
FROM employee
)
SELECT max(dept1) "行政管理部",
max(dept2) "人力资源部",
max(dept3) "财务部",
max(dept4) "研发部",
max(dept5) "销售部",
max(dept6) "保卫部"
FROM d
GROUP BY rn
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC;
SQL案例分析:删除重复数据
以MySQL为例,使用SQL查询并删除表中的重复记录。
-- 查找并删除重复记录
-- MySQL 实现
drop table if exists people;
create table people (
id int auto_increment primary key,
name varchar(50) not null,
email varchar(100) not null
);
insert into people(name, email)
values ('张三', 'zhangsan@test.com'),
('李四', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('李斯', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('王五', 'wangwu@test.com');
-- 查找单个字段中的重复数据
select email, count(email)
from people
group by email
having count(email) > 1;
-- 查看完整的重复数据
select *
from people
where email in (
select email
from people
group by email
having count(email) > 1)
order by email;
select p.*
from people p
join (
select email
from people
group by email
having count(email) > 1
) d on p.email = d.email
order by email;
-- 查找多个字段中的重复数据
select *
from people
where (name, email) in (
select name, email
from people
group by name, email
having count(1) > 1)
order by email;
select distinct p.*
from people p
join people d on p.name = d.name and p.email = d.email
where p.id <> d.id
order by email;
-- 删除重复数据
-- 使用 DELETE FROM 删除重复数据
delete p
from people p
join people d on p.email = d.email and p.id < d.id;
-- 利用子查询删除重复数据
delete
from people
where id not in (
select id
from (select max(id) id
from people
group by email) t
);
-- 利用窗口函数删除重复数据
delete
from people
where id in (
select id
from (
select id,
row_number() over (partition by email order by id desc) as row_num
from people) d
where row_num > 1);
SQL案例分析:生成日历
以 MySQL 为例,通过 SQL 语句创建日历表,涉及日期函数、WITH 语句、CASE 表达式、汇总函数的使用。
with recursive d(ymd) AS (
select SUBDATE(CURRENT_DATE + 1,DAYOFMONTH(CURRENT_DATE))
UNION ALL
select ADDDATE(ymd, 1) from d where ymd < LAST_DAY(CURRENT_DATE)
),
d2 AS(
select ymd, WEEK(ymd, 1) wk, DAYOFMONTH(ymd) dm, DAYOFWEEK(ymd) dw from d
)
select
min(case dw when 2 then dm end) AS '星期一',
min(case dw when 3 then dm end) AS '星期二',
min(case dw when 4 then dm end) AS '星期三',
min(case dw when 5 then dm end) AS '星期四',
min(case dw when 6 then dm end) AS '星期五',
min(case dw when 7 then dm end) AS '星期六',
min(case dw when 1 then dm end) AS '星期天'
from d2 GROUP BY wk
SQL数据分析:均值、众数、中位数
SQL实现数据分析中的描述性统计:均值、众数、中位数。
-- 均值
select avg(salary) from employee;
-- 去掉最高和最低的均值(结尾平均)
select (sum(salary) - max(salary) - min(salary)) / (count(*) - 2) from employee;
-- 众数 ROUND(X, -3)四舍五入,以千为单位,保留3位整数
WITH tmp AS(
select ROUND(salary, -3) salary, count(*) num from employee
GROUP BY 1)
select * from tmp
where num = (select max(num) from tmp);
-- 中位数
WITH tmp AS(
select salary,
ROW_NUMBER() over(PARTITION BY null order by salary) rn,
count(*) over() total
from employee
)
select avg(salary) from tmp where rn between total/2 and (total/2 +1);
SQL案例分析:微信群红包
通过SQL递归查询实现微信群红包功能。
-- 红包总金额100元, 共计10个红包,每个人至少0.01元,乘以系数0.5可以防止某个人的红包金额过大
WITH RECURSIVE t AS (
SELECT 1 n, round(rand()*(100-10*0.01)*0.5,2) amount, 100-10*0.01 balance
UNION ALL
SELECT n+1,CASE WHEN n<9 THEN round(rand()*(balance-amount)*0.5,2) ELSE balance-amount END amount, balance-amount
FROM t
WHERE n<10
)
SELECT n,amount+0.01 FROM t;
SQL案例分析:树形结构中的叶子节点
本次案例涉及递归查询WITH语句,以及LATERAL子查询的使用。
WITH recursive t AS(
select emp_id, emp_name, emp_name as path from employee where manager is null
union all
select e.emp_id, e.emp_name, concat(path, '->', e.emp_name) from employee e
join t on t.emp_id = e.manager
)
select t.*, case when s.emp_id is null then 'Y' else 'N' end is_leaf from t
LEFT JOIN lateral (select emp_id from employee sub where sub.manager=t.emp_id limit 1) s on 1=1;
SQL案例分析:员工考勤记录
介绍如何通过交叉连接和左外连接分析员工的缺勤记录,使用MySQL数据库演示相关代码。
-- 员工考勤记录
-- 创建日历表calendar
CREATE TABLE calendar(
id INTEGER NOT NULL PRIMARY KEY, -- 日历编号
calendar_date DATE NOT NULL UNIQUE, -- 日历日期
calendar_year INTEGER NOT NULL, -- 日历年
calendar_month INTEGER NOT NULL, -- 日历月
calendar_day INTEGER NOT NULL, -- 日历日
is_work_day VARCHAR(1) DEFAULT 'Y' NOT NULL -- 是否工作日
);
-- 创建考勤记录表attendance
CREATE TABLE attendance(
id INTEGER NOT NULL PRIMARY KEY, -- 考勤记录编号
check_date DATE NOT NULL, -- 考勤日期
emp_id INTEGER NOT NULL, -- 员工编号
clock_in TIMESTAMP, -- 上班打卡时间
clock_out TIMESTAMP, -- 下班打卡时间
CONSTRAINT uk_attendance UNIQUE (check_date, emp_id)
);
-- 生成测试数据
-- Oracle 需要执行以下 ALTER 语句
-- ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
INSERT INTO calendar VALUES (1,'2021-01-01',2021,1,1,'N');
INSERT INTO calendar VALUES (2,'2021-01-02',2021,1,2,'N');
INSERT INTO calendar VALUES (3,'2021-01-03',2021,1,3,'N');
INSERT INTO calendar VALUES (4,'2021-01-04',2021,1,4,'Y');
INSERT INTO calendar VALUES (5,'2021-01-05',2021,1,5,'Y');
INSERT INTO calendar VALUES (6,'2021-01-06',2021,1,6,'Y');
INSERT INTO calendar VALUES (7,'2021-01-07',2021,1,7,'Y');
INSERT INTO calendar VALUES (8,'2021-01-08',2021,1,8,'Y');
INSERT INTO calendar VALUES (9,'2021-01-09',2021,1,9,'N');
INSERT INTO calendar VALUES (10,'2021-01-10',2021,1,10,'N');
INSERT INTO calendar VALUES (11,'2021-01-11',2021,1,11,'Y');
INSERT INTO calendar VALUES (12,'2021-01-12',2021,1,12,'Y');
INSERT INTO calendar VALUES (13,'2021-01-13',2021,1,13,'Y');
INSERT INTO calendar VALUES (14,'2021-01-14',2021,1,14,'Y');
INSERT INTO calendar VALUES (15,'2021-01-15',2021,1,15,'Y');
INSERT INTO calendar VALUES (16,'2021-01-16',2021,1,16,'N');
INSERT INTO calendar VALUES (17,'2021-01-17',2021,1,17,'N');
INSERT INTO calendar VALUES (18,'2021-01-18',2021,1,18,'Y');
INSERT INTO calendar VALUES (19,'2021-01-19',2021,1,19,'Y');
INSERT INTO calendar VALUES (20,'2021-01-20',2021,1,20,'Y');
INSERT INTO calendar VALUES (21,'2021-01-21',2021,1,21,'Y');
INSERT INTO calendar VALUES (22,'2021-01-22',2021,1,22,'Y');
INSERT INTO calendar VALUES (23,'2021-01-23',2021,1,23,'N');
INSERT INTO calendar VALUES (24,'2021-01-24',2021,1,24,'N');
INSERT INTO calendar VALUES (25,'2021-01-25',2021,1,25,'Y');
INSERT INTO calendar VALUES (26,'2021-01-26',2021,1,26,'Y');
INSERT INTO calendar VALUES (27,'2021-01-27',2021,1,27,'Y');
INSERT INTO calendar VALUES (28,'2021-01-28',2021,1,28,'Y');
INSERT INTO calendar VALUES (29,'2021-01-29',2021,1,29,'Y');
INSERT INTO calendar VALUES (30,'2021-01-30',2021,1,30,'N');
INSERT INTO calendar VALUES (31,'2021-01-31',2021,1,31,'N');
INSERT INTO attendance VALUES (1,'2021-01-04',1,'2021-01-04 08:34:02.374','2021-01-04 18:33:11.842');
INSERT INTO attendance VALUES (2,'2021-01-04',2,'2021-01-04 08:10:31.367','2021-01-04 19:11:59.19');
INSERT INTO attendance VALUES (3,'2021-01-04',3,'2021-01-04 08:54:08.807','2021-01-04 18:27:21.348');
INSERT INTO attendance VALUES (4,'2021-01-04',4,'2021-01-04 08:27:55.39','2021-01-04 18:10:16.862');
INSERT INTO attendance VALUES (5,'2021-01-04',5,'2021-01-04 08:39:34.557','2021-01-04 18:36:10.973');
INSERT INTO attendance VALUES (6,'2021-01-04',6,'2021-01-04 08:32:34.859','2021-01-04 19:29:04.401');
INSERT INTO attendance VALUES (7,'2021-01-04',7,'2021-01-04 08:22:57.576','2021-01-04 19:25:37.615');
INSERT INTO attendance VALUES (8,'2021-01-04',8,'2021-01-04 08:45:56.07','2021-01-04 18:46:10.026');
INSERT INTO attendance VALUES (9,'2021-01-04',9,'2021-01-04 08:13:23.886','2021-01-04 19:24:29.827');
INSERT INTO attendance VALUES (10,'2021-01-04',10,'2021-01-04 08:45:31.543','2021-01-04 18:21:02.158');
INSERT INTO attendance VALUES (11,'2021-01-04',11,'2021-01-04 08:35:28.413','2021-01-04 18:11:34.613');
INSERT INTO attendance VALUES (12,'2021-01-04',12,'2021-01-04 08:03:29.148','2021-01-04 18:50:04.368');
INSERT INTO attendance VALUES (13,'2021-01-04',13,'2021-01-04 08:02:13.397','2021-01-04 19:27:37.883');
INSERT INTO attendance VALUES (14,'2021-01-04',14,'2021-01-04 08:26:13.715','2021-01-04 18:23:04.015');
INSERT INTO attendance VALUES (15,'2021-01-04',15,'2021-01-04 08:13:55.707','2021-01-04 19:29:21.996');
INSERT INTO attendance VALUES (16,'2021-01-04',16,'2021-01-04 08:15:23.972','2021-01-04 18:34:22.918');
INSERT INTO attendance VALUES (17,'2021-01-04',17,'2021-01-04 08:04:11.176','2021-01-04 18:20:36.873');
INSERT INTO attendance VALUES (18,'2021-01-04',18,'2021-01-04 08:41:42.695','2021-01-04 19:18:09.091');
INSERT INTO attendance VALUES (19,'2021-01-04',19,'2021-01-04 08:34:25.891','2021-01-04 18:09:18.634');
INSERT INTO attendance VALUES (20,'2021-01-04',20,'2021-01-04 08:31:51.219','2021-01-04 18:33:13.3');
INSERT INTO attendance VALUES (21,'2021-01-04',21,'2021-01-04 08:18:28.941','2021-01-04 19:22:39.781');
INSERT INTO attendance VALUES (22,'2021-01-04',22,'2021-01-04 08:04:17.153','2021-01-04 18:47:44.909');
INSERT INTO attendance VALUES (23,'2021-01-04',23,'2021-01-04 08:46:59.726','2021-01-04 18:59:09.632');
INSERT INTO attendance VALUES (24,'2021-01-04',24,'2021-01-04 08:18:05.634','2021-01-04 18:51:09.465');
INSERT INTO attendance VALUES (25,'2021-01-04',25,'2021-01-04 08:45:10.735','2021-01-04 18:42:57.763');
INSERT INTO attendance VALUES (26,'2021-01-05',1,'2021-01-05 08:35:51.082','2021-01-05 18:01:37.954');
INSERT INTO attendance VALUES (27,'2021-01-05',2,'2021-01-05 08:01:41.689','2021-01-05 18:25:56.633');
INSERT INTO attendance VALUES (28,'2021-01-05',3,'2021-01-05 08:08:05.755','2021-01-05 19:07:14.298');
INSERT INTO attendance VALUES (29,'2021-01-05',4,'2021-01-05 08:10:07.258','2021-01-05 18:15:52.768');
INSERT INTO attendance VALUES (30,'2021-01-05',5,'2021-01-05 08:47:56.777','2021-01-05 18:39:51.89');
INSERT INTO attendance VALUES (31,'2021-01-05',6,'2021-01-05 08:55:39.959','2021-01-05 18:58:30.74');
INSERT INTO attendance VALUES (32,'2021-01-05',7,'2021-01-05 08:56:55.547','2021-01-05 18:14:43.76');
INSERT INTO attendance VALUES (33,'2021-01-05',8,'2021-01-05 08:21:23.269','2021-01-05 18:54:58.262');
INSERT INTO attendance VALUES (34,'2021-01-05',9,'2021-01-05 08:13:38.294','2021-01-05 18:47:59.95');
INSERT INTO attendance VALUES (35,'2021-01-05',10,'2021-01-05 08:22:34.44','2021-01-05 18:08:05.129');
INSERT INTO attendance VALUES (36,'2021-01-05',11,'2021-01-05 08:57:59.632','2021-01-05 19:02:17.866');
INSERT INTO attendance VALUES (37,'2021-01-05',12,'2021-01-05 08:32:48.528','2021-01-05 19:17:10.785');
INSERT INTO attendance VALUES (38,'2021-01-05',13,'2021-01-05 08:19:42.181','2021-01-05 19:15:32.31');
INSERT INTO attendance VALUES (39,'2021-01-05',14,'2021-01-05 08:41:21.615','2021-01-05 18:20:57.274');
INSERT INTO attendance VALUES (40,'2021-01-05',15,'2021-01-05 08:32:28.488','2021-01-05 18:25:49.258');
INSERT INTO attendance VALUES (41,'2021-01-05',16,'2021-01-05 08:36:44.328','2021-01-05 19:03:23.056');
INSERT INTO attendance VALUES (42,'2021-01-05',17,'2021-01-05 08:26:13.336','2021-01-05 19:19:58.026');
INSERT INTO attendance VALUES (43,'2021-01-05',18,'2021-01-05 08:05:03.891','2021-01-05 18:08:00.424');
INSERT INTO attendance VALUES (44,'2021-01-05',19,'2021-01-05 08:29:07.198','2021-01-05 18:46:56.679');
INSERT INTO attendance VALUES (45,'2021-01-05',20,'2021-01-05 08:48:28.622','2021-01-05 18:05:38.832');
INSERT INTO attendance VALUES (46,'2021-01-05',21,'2021-01-05 08:48:12.368','2021-01-05 19:13:39.761');
INSERT INTO attendance VALUES (47,'2021-01-05',22,'2021-01-05 08:37:37.291','2021-01-05 18:01:37.542');
INSERT INTO attendance VALUES (48,'2021-01-05',23,'2021-01-05 08:11:57.007','2021-01-05 19:18:04.795');
INSERT INTO attendance VALUES (49,'2021-01-05',25,'2021-01-05 08:37:47.171','2021-01-05 18:04:13.12');
INSERT INTO attendance VALUES (50,'2021-01-06',1,'2021-01-06 08:45:16.057','2021-01-06 18:18:08.261');
INSERT INTO attendance VALUES (51,'2021-01-06',2,'2021-01-06 08:21:41.834','2021-01-06 18:51:13.415');
INSERT INTO attendance VALUES (52,'2021-01-06',3,'2021-01-06 08:25:45.385','2021-01-06 19:19:22.325');
INSERT INTO attendance VALUES (53,'2021-01-06',4,'2021-01-06 08:49:05.149','2021-01-06 18:42:16.572');
INSERT INTO attendance VALUES (54,'2021-01-06',5,'2021-01-06 08:00:53.742','2021-01-06 18:45:52.32');
INSERT INTO attendance VALUES (55,'2021-01-06',6,'2021-01-06 08:24:45.381','2021-01-06 18:43:21.977');
INSERT INTO attendance VALUES (56,'2021-01-06',7,'2021-01-06 08:42:03.426','2021-01-06 18:18:21.11');
INSERT INTO attendance VALUES (57,'2021-01-06',8,'2021-01-06 08:58:40.471','2021-01-06 18:20:25.922');
INSERT INTO attendance VALUES (58,'2021-01-06',9,'2021-01-06 08:36:45.124','2021-01-06 18:56:28.7');
INSERT INTO attendance VALUES (59,'2021-01-06',10,'2021-01-06 08:54:35.033','2021-01-06 19:23:13.231');
INSERT INTO attendance VALUES (60,'2021-01-06',11,'2021-01-06 08:05:28.988','2021-01-06 19:14:58.245');
INSERT INTO attendance VALUES (61,'2021-01-06',12,'2021-01-06 08:22:27.423','2021-01-06 18:52:13.498');
INSERT INTO attendance VALUES (62,'2021-01-06',13,'2021-01-06 08:59:35.511','2021-01-06 19:26:11.478');
INSERT INTO attendance VALUES (63,'2021-01-06',14,'2021-01-06 08:02:04.065','2021-01-06 18:21:37.454');
INSERT INTO attendance VALUES (64,'2021-01-06',15,'2021-01-06 08:38:40.04','2021-01-06 19:04:11.615');
INSERT INTO attendance VALUES (65,'2021-01-06',16,'2021-01-06 08:40:40.106','2021-01-06 18:23:46.659');
INSERT INTO attendance VALUES (66,'2021-01-06',17,'2021-01-06 08:03:32.269','2021-01-06 18:53:30.189');
INSERT INTO attendance VALUES (67,'2021-01-06',18,'2021-01-06 08:01:11.163','2021-01-06 19:29:14.124');
INSERT INTO attendance VALUES (68,'2021-01-06',19,'2021-01-06 08:16:01.192','2021-01-06 18:19:11.921');
INSERT INTO attendance VALUES (69,'2021-01-06',20,'2021-01-06 08:41:32.83','2021-01-06 18:55:59.573');
INSERT INTO attendance VALUES (70,'2021-01-06',21,'2021-01-06 08:19:59.225','2021-01-06 18:48:33.704');
INSERT INTO attendance VALUES (71,'2021-01-06',22,'2021-01-06 08:29:26.286','2021-01-06 18:53:50.085');
INSERT INTO attendance VALUES (72,'2021-01-06',23,'2021-01-06 08:14:30.847','2021-01-06 19:15:48.219');
INSERT INTO attendance VALUES (73,'2021-01-06',24,'2021-01-06 08:29:50.292','2021-01-06 18:42:53.553');
INSERT INTO attendance VALUES (74,'2021-01-06',25,'2021-01-06 08:11:57.989','2021-01-06 18:17:05.313');
INSERT INTO attendance VALUES (75,'2021-01-07',1,'2021-01-07 08:50:21.479','2021-01-07 18:37:00.492');
INSERT INTO attendance VALUES (76,'2021-01-07',2,'2021-01-07 08:49:47.402','2021-01-07 18:53:27.009');
INSERT INTO attendance VALUES (77,'2021-01-07',3,'2021-01-07 08:51:43.025','2021-01-07 19:20:06.793');
INSERT INTO attendance VALUES (78,'2021-01-07',4,'2021-01-07 08:22:49.782','2021-01-07 18:29:53.198');
INSERT INTO attendance VALUES (79,'2021-01-07',5,'2021-01-07 08:36:08.252','2021-01-07 18:02:12.076');
INSERT INTO attendance VALUES (80,'2021-01-07',6,'2021-01-07 08:43:20.858','2021-01-07 19:02:14.235');
INSERT INTO attendance VALUES (81,'2021-01-07',7,'2021-01-07 08:18:01.76','2021-01-07 18:32:02.165');
INSERT INTO attendance VALUES (82,'2021-01-07',8,'2021-01-07 08:40:24.227','2021-01-07 18:25:56.654');
INSERT INTO attendance VALUES (83,'2021-01-07',9,'2021-01-07 08:16:49.757','2021-01-07 18:20:01.265');
INSERT INTO attendance VALUES (84,'2021-01-07',10,'2021-01-07 08:43:35.134','2021-01-07 18:35:03.231');
INSERT INTO attendance VALUES (85,'2021-01-07',11,'2021-01-07 08:50:44.161','2021-01-07 18:07:04.51');
INSERT INTO attendance VALUES (86,'2021-01-07',12,'2021-01-07 08:35:39.053','2021-01-07 18:06:55.982');
INSERT INTO attendance VALUES (87,'2021-01-07',13,'2021-01-07 08:45:47.697','2021-01-07 18:56:41.041');
INSERT INTO attendance VALUES (88,'2021-01-07',14,'2021-01-07 08:13:20.802','2021-01-07 18:29:31.599');
INSERT INTO attendance VALUES (89,'2021-01-07',15,'2021-01-07 08:30:44.08','2021-01-07 18:44:22.748');
INSERT INTO attendance VALUES (90,'2021-01-07',16,'2021-01-07 08:32:57.825','2021-01-07 19:21:25.719');
INSERT INTO attendance VALUES (91,'2021-01-07',17,'2021-01-07 08:33:26.599','2021-01-07 18:10:13.527');
INSERT INTO attendance VALUES (92,'2021-01-07',18,'2021-01-07 08:15:17.918','2021-01-07 18:14:36.04');
INSERT INTO attendance VALUES (93,'2021-01-07',19,'2021-01-07 08:59:15.325','2021-01-07 18:57:17.367');
INSERT INTO attendance VALUES (94,'2021-01-07',20,'2021-01-07 08:18:40.677','2021-01-07 18:51:35.671');
INSERT INTO attendance VALUES (95,'2021-01-07',21,'2021-01-07 08:30:34.002','2021-01-07 18:45:32.63');
INSERT INTO attendance VALUES (96,'2021-01-07',22,'2021-01-07 08:50:42.612','2021-01-07 18:31:16.777');
INSERT INTO attendance VALUES (97,'2021-01-07',23,'2021-01-07 08:33:49.24','2021-01-07 18:39:07.772');
INSERT INTO attendance VALUES (98,'2021-01-07',24,'2021-01-07 08:26:40.186','2021-01-07 18:13:55.498');
INSERT INTO attendance VALUES (99,'2021-01-07',25,'2021-01-07 08:01:30.599','2021-01-07 18:28:23.797');
INSERT INTO attendance VALUES (100,'2021-01-08',1,'2021-01-10 08:00:02.771','2021-01-10 18:45:02.915');
INSERT INTO attendance VALUES (101,'2021-01-08',2,'2021-01-10 08:18:19.509','2021-01-10 18:52:45.721');
INSERT INTO attendance VALUES (102,'2021-01-08',3,'2021-01-10 08:21:39.68','2021-01-10 18:56:59.892');
INSERT INTO attendance VALUES (103,'2021-01-08',4,'2021-01-10 08:46:22.282','2021-01-10 18:40:05.496');
INSERT INTO attendance VALUES (104,'2021-01-08',5,'2021-01-10 08:40:10.87','2021-01-10 18:20:43.218');
INSERT INTO attendance VALUES (105,'2021-01-08',6,'2021-01-10 08:02:08.985','2021-01-10 18:17:35.714');
INSERT INTO attendance VALUES (106,'2021-01-08',7,'2021-01-10 08:56:40.194','2021-01-10 18:15:42.569');
INSERT INTO attendance VALUES (107,'2021-01-08',8,'2021-01-10 08:20:48.469','2021-01-10 18:49:15.59');
INSERT INTO attendance VALUES (108,'2021-01-08',9,'2021-01-10 08:17:14.243','2021-01-10 18:29:35.534');
INSERT INTO attendance VALUES (109,'2021-01-08',10,'2021-01-10 08:18:16.177','2021-01-10 18:15:41.696');
INSERT INTO attendance VALUES (110,'2021-01-08',11,'2021-01-10 08:29:09.5','2021-01-10 19:11:33.418');
INSERT INTO attendance VALUES (111,'2021-01-08',12,'2021-01-10 08:21:22.473','2021-01-10 19:17:31.9');
INSERT INTO attendance VALUES (112,'2021-01-08',13,'2021-01-10 08:20:29.483','2021-01-10 18:16:44.212');
INSERT INTO attendance VALUES (113,'2021-01-08',14,'2021-01-10 08:51:19.459','2021-01-10 19:16:58.311');
INSERT INTO attendance VALUES (114,'2021-01-08',15,'2021-01-10 08:32:46.01','2021-01-10 18:25:57.469');
INSERT INTO attendance VALUES (115,'2021-01-08',16,'2021-01-10 08:05:26.84','2021-01-10 18:17:32.939');
INSERT INTO attendance VALUES (116,'2021-01-08',17,'2021-01-10 08:19:07.136','2021-01-10 18:42:38.108');
INSERT INTO attendance VALUES (117,'2021-01-08',18,'2021-01-10 08:11:44.637','2021-01-10 19:08:20.217');
INSERT INTO attendance VALUES (118,'2021-01-08',19,'2021-01-10 08:15:48.439','2021-01-10 18:18:31.299');
INSERT INTO attendance VALUES (119,'2021-01-08',20,'2021-01-10 08:40:23.455','2021-01-10 18:48:22.836');
INSERT INTO attendance VALUES (120,'2021-01-08',21,'2021-01-10 08:30:28.016','2021-01-10 18:07:37.526');
INSERT INTO attendance VALUES (121,'2021-01-08',22,'2021-01-10 08:08:44.252','2021-01-10 18:18:22.9');
INSERT INTO attendance VALUES (122,'2021-01-08',23,'2021-01-10 08:40:58.678','2021-01-10 18:26:24.982');
INSERT INTO attendance VALUES (123,'2021-01-08',24,'2021-01-10 08:13:22.774','2021-01-10 18:13:21.348');
INSERT INTO attendance VALUES (124,'2021-01-08',25,'2021-01-10 08:21:54.26','2021-01-10 18:46:37.627');
INSERT INTO attendance VALUES (125,'2021-01-11',1,'2021-01-11 08:57:00.945','2021-01-11 18:09:27.372');
INSERT INTO attendance VALUES (126,'2021-01-11',2,'2021-01-11 08:34:49.107','2021-01-11 18:27:08.029');
INSERT INTO attendance VALUES (127,'2021-01-11',3,'2021-01-11 08:40:24.455','2021-01-11 18:37:44.055');
INSERT INTO attendance VALUES (128,'2021-01-11',4,'2021-01-11 08:10:23.142','2021-01-11 18:58:14.284');
INSERT INTO attendance VALUES (129,'2021-01-11',5,'2021-01-11 08:50:29.087','2021-01-11 18:16:10.282');
INSERT INTO attendance VALUES (130,'2021-01-11',6,'2021-01-11 08:13:32.339','2021-01-11 19:28:45.072');
INSERT INTO attendance VALUES (131,'2021-01-11',7,'2021-01-11 08:13:12.828','2021-01-11 18:20:18.741');
INSERT INTO attendance VALUES (132,'2021-01-11',8,'2021-01-11 08:20:59.383','2021-01-11 19:18:06.405');
INSERT INTO attendance VALUES (133,'2021-01-11',9,'2021-01-11 08:16:47.614','2021-01-11 19:24:18.563');
INSERT INTO attendance VALUES (134,'2021-01-11',10,'2021-01-11 08:13:50.422','2021-01-11 18:37:31.179');
INSERT INTO attendance VALUES (135,'2021-01-11',12,'2021-01-11 08:21:39.938','2021-01-11 18:40:20.338');
INSERT INTO attendance VALUES (136,'2021-01-11',13,'2021-01-11 08:47:20.85','2021-01-11 19:18:33.5');
INSERT INTO attendance VALUES (137,'2021-01-11',14,'2021-01-11 08:44:57.965','2021-01-11 19:11:51.117');
INSERT INTO attendance VALUES (138,'2021-01-11',15,'2021-01-11 08:15:54.76','2021-01-11 18:10:47.549');
INSERT INTO attendance VALUES (139,'2021-01-11',16,'2021-01-11 08:22:36.486','2021-01-11 18:02:34.481');
INSERT INTO attendance VALUES (140,'2021-01-11',17,'2021-01-11 08:45:06.179','2021-01-11 19:15:33.943');
INSERT INTO attendance VALUES (141,'2021-01-11',18,'2021-01-11 08:41:05.886','2021-01-11 19:18:29.987');
INSERT INTO attendance VALUES (142,'2021-01-11',19,'2021-01-11 08:11:54.073','2021-01-11 18:36:03.986');
INSERT INTO attendance VALUES (143,'2021-01-11',20,'2021-01-11 08:04:17.52','2021-01-11 18:59:36.99');
INSERT INTO attendance VALUES (144,'2021-01-11',21,'2021-01-11 08:45:17.274','2021-01-11 19:14:09.068');
INSERT INTO attendance VALUES (145,'2021-01-11',22,'2021-01-11 08:08:40.692','2021-01-11 18:19:27.634');
INSERT INTO attendance VALUES (146,'2021-01-11',23,'2021-01-11 08:29:31.58','2021-01-11 18:57:01.788');
INSERT INTO attendance VALUES (147,'2021-01-11',24,'2021-01-11 08:46:41.935','2021-01-11 18:39:44.221');
INSERT INTO attendance VALUES (148,'2021-01-11',25,'2021-01-11 08:29:31.073','2021-01-11 19:09:03.324');
INSERT INTO attendance VALUES (149,'2021-01-12',1,'2021-01-12 08:27:51.502','2021-01-12 18:00:14.981');
INSERT INTO attendance VALUES (150,'2021-01-12',2,'2021-01-12 08:19:02.394','2021-01-12 19:09:36.293');
INSERT INTO attendance VALUES (151,'2021-01-12',3,'2021-01-12 08:10:16.364','2021-01-12 18:45:00.56');
INSERT INTO attendance VALUES (152,'2021-01-12',4,'2021-01-12 08:44:01.316','2021-01-12 18:38:02.932');
INSERT INTO attendance VALUES (153,'2021-01-12',5,'2021-01-12 08:35:28.988','2021-01-12 18:42:53.776');
INSERT INTO attendance VALUES (154,'2021-01-12',6,'2021-01-12 08:38:16.505','2021-01-12 18:38:35.161');
INSERT INTO attendance VALUES (155,'2021-01-12',7,'2021-01-12 08:47:55.921','2021-01-12 18:24:38.188');
INSERT INTO attendance VALUES (156,'2021-01-12',8,'2021-01-12 08:33:50.104','2021-01-12 18:25:24.031');
INSERT INTO attendance VALUES (157,'2021-01-12',9,'2021-01-12 08:20:02.087','2021-01-12 18:47:02.544');
INSERT INTO attendance VALUES (158,'2021-01-12',10,'2021-01-12 08:22:52.628','2021-01-12 18:48:35.72');
INSERT INTO attendance VALUES (159,'2021-01-12',11,'2021-01-12 08:46:45.411','2021-01-12 19:29:10.451');
INSERT INTO attendance VALUES (160,'2021-01-12',12,'2021-01-12 08:27:02.773','2021-01-12 18:26:24.641');
INSERT INTO attendance VALUES (161,'2021-01-12',13,'2021-01-12 08:14:36.183','2021-01-12 18:35:29.542');
INSERT INTO attendance VALUES (162,'2021-01-12',14,'2021-01-12 08:15:23.594','2021-01-12 18:50:17.252');
INSERT INTO attendance VALUES (163,'2021-01-12',15,'2021-01-12 08:46:11.919','2021-01-12 19:01:21.258');
INSERT INTO attendance VALUES (164,'2021-01-12',16,'2021-01-12 08:54:53.972','2021-01-12 19:05:57.831');
INSERT INTO attendance VALUES (165,'2021-01-12',17,'2021-01-12 08:55:01.843','2021-01-12 18:36:39.325');
INSERT INTO attendance VALUES (166,'2021-01-12',18,'2021-01-12 08:21:36.517','2021-01-12 19:18:04.027');
INSERT INTO attendance VALUES (167,'2021-01-12',19,'2021-01-12 08:53:07.749','2021-01-12 19:08:21.163');
INSERT INTO attendance VALUES (168,'2021-01-12',20,'2021-01-12 08:08:22.908','2021-01-12 19:23:16.957');
INSERT INTO attendance VALUES (169,'2021-01-12',21,'2021-01-12 08:00:35.768','2021-01-12 18:19:50.04');
INSERT INTO attendance VALUES (170,'2021-01-12',22,'2021-01-12 08:15:22.994','2021-01-12 18:10:19.162');
INSERT INTO attendance VALUES (171,'2021-01-12',23,'2021-01-12 08:49:14.259','2021-01-12 19:02:49.075');
INSERT INTO attendance VALUES (172,'2021-01-12',24,'2021-01-12 08:47:34.259','2021-01-12 19:29:53.1');
INSERT INTO attendance VALUES (173,'2021-01-12',25,'2021-01-12 08:33:29.381','2021-01-12 18:18:34.01');
INSERT INTO attendance VALUES (174,'2021-01-13',1,'2021-01-13 08:43:23.796','2021-01-13 18:13:41.737');
INSERT INTO attendance VALUES (175,'2021-01-13',2,'2021-01-13 08:20:30.036','2021-01-13 18:47:04.963');
INSERT INTO attendance VALUES (176,'2021-01-13',3,'2021-01-13 08:47:38.705','2021-01-13 18:14:49.647');
INSERT INTO attendance VALUES (177,'2021-01-13',4,'2021-01-13 08:28:40.134','2021-01-13 18:19:05.091');
INSERT INTO attendance VALUES (178,'2021-01-13',5,'2021-01-13 08:59:29.427','2021-01-13 18:09:09.892');
INSERT INTO attendance VALUES (179,'2021-01-13',6,'2021-01-13 08:08:23.63','2021-01-13 18:29:27.237');
INSERT INTO attendance VALUES (180,'2021-01-13',7,'2021-01-13 08:02:56.181','2021-01-13 18:47:24.366');
INSERT INTO attendance VALUES (181,'2021-01-13',8,'2021-01-13 08:45:29.247','2021-01-13 18:47:56.194');
INSERT INTO attendance VALUES (182,'2021-01-13',9,'2021-01-13 08:25:34.331','2021-01-13 18:01:13.014');
INSERT INTO attendance VALUES (183,'2021-01-13',10,'2021-01-13 08:21:22.966','2021-01-13 18:53:30.803');
INSERT INTO attendance VALUES (184,'2021-01-13',11,'2021-01-13 08:50:14.01','2021-01-13 18:31:13.346');
INSERT INTO attendance VALUES (185,'2021-01-13',12,'2021-01-13 08:49:50.759','2021-01-13 18:44:15.652');
INSERT INTO attendance VALUES (186,'2021-01-13',13,'2021-01-13 08:38:14.802','2021-01-13 18:49:45.39');
INSERT INTO attendance VALUES (187,'2021-01-13',14,'2021-01-13 08:05:23.458','2021-01-13 18:52:04.165');
INSERT INTO attendance VALUES (188,'2021-01-13',15,'2021-01-13 08:45:41.343','2021-01-13 18:33:32.467');
INSERT INTO attendance VALUES (189,'2021-01-13',16,'2021-01-13 08:34:28.19',NULL);
INSERT INTO attendance VALUES (190,'2021-01-13',17,'2021-01-13 08:59:36.18','2021-01-13 19:16:57.773');
INSERT INTO attendance VALUES (191,'2021-01-13',18,'2021-01-13 08:29:18.256','2021-01-13 19:02:20.427');
INSERT INTO attendance VALUES (192,'2021-01-13',19,'2021-01-13 08:55:57.874','2021-01-13 18:07:46.404');
INSERT INTO attendance VALUES (193,'2021-01-13',20,'2021-01-13 08:00:40.237','2021-01-13 19:18:27.254');
INSERT INTO attendance VALUES (194,'2021-01-13',21,'2021-01-13 08:26:29.17','2021-01-13 19:27:35.004');
INSERT INTO attendance VALUES (195,'2021-01-13',22,'2021-01-13 08:10:22.418','2021-01-13 19:02:01.405');
INSERT INTO attendance VALUES (196,'2021-01-13',23,'2021-01-13 08:41:06.836','2021-01-13 19:19:19.324');
INSERT INTO attendance VALUES (197,'2021-01-13',24,'2021-01-13 08:06:11.435','2021-01-13 19:16:40.933');
INSERT INTO attendance VALUES (198,'2021-01-13',25,'2021-01-13 08:04:20.755','2021-01-13 19:16:32.221');
INSERT INTO attendance VALUES (199,'2021-01-14',1,'2021-01-14 08:07:59.481','2021-01-14 19:20:30.471');
INSERT INTO attendance VALUES (200,'2021-01-14',2,'2021-01-14 08:08:51.482','2021-01-14 19:00:19.871');
INSERT INTO attendance VALUES (201,'2021-01-14',3,'2021-01-14 08:50:55.823','2021-01-14 18:19:43.245');
INSERT INTO attendance VALUES (202,'2021-01-14',4,'2021-01-14 08:04:31.95','2021-01-14 19:25:35.633');
INSERT INTO attendance VALUES (203,'2021-01-14',5,'2021-01-14 08:18:24.478','2021-01-14 18:26:27.144');
INSERT INTO attendance VALUES (204,'2021-01-14',6,'2021-01-14 08:53:06.897','2021-01-14 18:34:56.045');
INSERT INTO attendance VALUES (205,'2021-01-14',7,'2021-01-14 08:45:47.688','2021-01-14 18:19:23.142');
INSERT INTO attendance VALUES (206,'2021-01-14',8,'2021-01-14 08:43:56.96','2021-01-14 19:13:44.021');
INSERT INTO attendance VALUES (207,'2021-01-14',9,'2021-01-14 08:02:43.684','2021-01-14 18:40:28.59');
INSERT INTO attendance VALUES (208,'2021-01-14',10,'2021-01-14 08:27:14.309','2021-01-14 18:32:01.006');
INSERT INTO attendance VALUES (209,'2021-01-14',11,'2021-01-14 08:06:10.046','2021-01-14 18:13:47.834');
INSERT INTO attendance VALUES (210,'2021-01-14',12,'2021-01-14 08:06:50.674','2021-01-14 19:23:18.101');
INSERT INTO attendance VALUES (211,'2021-01-14',13,'2021-01-14 08:22:49.264','2021-01-14 18:00:08.114');
INSERT INTO attendance VALUES (212,'2021-01-14',14,'2021-01-14 08:47:11.363','2021-01-14 19:09:29');
INSERT INTO attendance VALUES (213,'2021-01-14',15,'2021-01-14 08:42:08.476','2021-01-14 18:33:51.564');
INSERT INTO attendance VALUES (214,'2021-01-14',16,'2021-01-14 08:36:32.193','2021-01-14 18:01:56.477');
INSERT INTO attendance VALUES (215,'2021-01-14',17,'2021-01-14 08:00:30.151','2021-01-14 18:19:56.862');
INSERT INTO attendance VALUES (216,'2021-01-14',18,'2021-01-14 08:45:17.062','2021-01-14 18:09:06.86');
INSERT INTO attendance VALUES (217,'2021-01-14',19,'2021-01-14 08:53:36.769','2021-01-14 18:24:13.077');
INSERT INTO attendance VALUES (218,'2021-01-14',20,'2021-01-14 08:38:43.412','2021-01-14 19:08:55.625');
INSERT INTO attendance VALUES (219,'2021-01-14',21,'2021-01-14 08:17:08.493','2021-01-14 18:17:22.369');
INSERT INTO attendance VALUES (220,'2021-01-14',22,'2021-01-14 08:27:09.634','2021-01-14 18:12:07.415');
INSERT INTO attendance VALUES (221,'2021-01-14',23,'2021-01-14 08:33:48.734','2021-01-14 18:14:46.697');
INSERT INTO attendance VALUES (222,'2021-01-14',25,'2021-01-14 08:47:36.708','2021-01-14 18:30:12.789');
INSERT INTO attendance VALUES (223,'2021-01-15',1,'2021-01-17 08:02:42.128','2021-01-17 18:46:14.552');
INSERT INTO attendance VALUES (224,'2021-01-15',2,'2021-01-17 08:54:53.476','2021-01-17 19:11:14.248');
INSERT INTO attendance VALUES (225,'2021-01-15',3,'2021-01-17 08:11:58.053','2021-01-17 19:10:57.193');
INSERT INTO attendance VALUES (226,'2021-01-15',4,'2021-01-17 08:52:07.071','2021-01-17 18:15:17.231');
INSERT INTO attendance VALUES (227,'2021-01-15',5,'2021-01-17 08:45:36.246','2021-01-17 18:58:46.544');
INSERT INTO attendance VALUES (228,'2021-01-15',6,'2021-01-17 08:26:13.283','2021-01-17 19:18:27.052');
INSERT INTO attendance VALUES (229,'2021-01-15',7,'2021-01-17 08:43:35.881','2021-01-17 19:23:08.928');
INSERT INTO attendance VALUES (230,'2021-01-15',8,'2021-01-17 08:30:45.652','2021-01-17 18:40:07.791');
INSERT INTO attendance VALUES (231,'2021-01-15',9,'2021-01-17 08:41:25.357','2021-01-17 18:12:00.807');
INSERT INTO attendance VALUES (232,'2021-01-15',10,'2021-01-17 08:36:30.922','2021-01-17 18:06:35.671');
INSERT INTO attendance VALUES (233,'2021-01-15',11,'2021-01-17 08:09:19.852','2021-01-17 18:07:15.58');
INSERT INTO attendance VALUES (234,'2021-01-15',12,'2021-01-17 08:04:49.945','2021-01-17 18:30:37.762');
INSERT INTO attendance VALUES (235,'2021-01-15',13,'2021-01-17 08:12:35.816','2021-01-17 18:07:47.409');
INSERT INTO attendance VALUES (236,'2021-01-15',14,'2021-01-17 08:35:05.776','2021-01-17 18:35:49.562');
INSERT INTO attendance VALUES (237,'2021-01-15',15,'2021-01-17 08:27:33.535','2021-01-17 19:22:18.533');
INSERT INTO attendance VALUES (238,'2021-01-15',16,'2021-01-17 08:27:22.46','2021-01-17 18:01:47.09');
INSERT INTO attendance VALUES (239,'2021-01-15',17,'2021-01-17 08:31:46.171','2021-01-17 18:10:02.381');
INSERT INTO attendance VALUES (240,'2021-01-15',18,'2021-01-17 08:47:49.853','2021-01-17 19:08:02.054');
INSERT INTO attendance VALUES (241,'2021-01-15',19,'2021-01-17 08:58:11.641','2021-01-17 19:16:21.587');
INSERT INTO attendance VALUES (242,'2021-01-15',20,'2021-01-17 08:37:30.106','2021-01-17 18:40:13.636');
INSERT INTO attendance VALUES (243,'2021-01-15',21,'2021-01-17 08:49:04.39','2021-01-17 18:25:28.872');
INSERT INTO attendance VALUES (244,'2021-01-15',22,'2021-01-17 08:03:35.362','2021-01-17 19:23:58.923');
INSERT INTO attendance VALUES (245,'2021-01-15',23,'2021-01-17 08:26:51.55','2021-01-17 19:04:26.618');
INSERT INTO attendance VALUES (246,'2021-01-15',24,'2021-01-17 08:59:31.266','2021-01-17 19:17:57.896');
INSERT INTO attendance VALUES (247,'2021-01-15',25,'2021-01-17 08:38:15.573','2021-01-17 18:30:50.77');
INSERT INTO attendance VALUES (248,'2021-01-18',1,'2021-01-18 08:16:34.346','2021-01-18 19:09:37.434');
INSERT INTO attendance VALUES (249,'2021-01-18',2,'2021-01-18 08:05:46.094','2021-01-18 19:05:13.224');
INSERT INTO attendance VALUES (250,'2021-01-18',3,'2021-01-18 08:09:20.54','2021-01-18 19:06:03.062');
INSERT INTO attendance VALUES (251,'2021-01-18',4,'2021-01-18 08:54:25.52','2021-01-18 18:08:27.417');
INSERT INTO attendance VALUES (252,'2021-01-18',5,'2021-01-18 08:48:34.576','2021-01-18 18:51:26.464');
INSERT INTO attendance VALUES (253,'2021-01-18',6,'2021-01-18 08:09:17.372','2021-01-18 19:15:19.557');
INSERT INTO attendance VALUES (254,'2021-01-18',7,'2021-01-18 08:27:41.563','2021-01-18 18:00:46.344');
INSERT INTO attendance VALUES (255,'2021-01-18',8,'2021-01-18 08:27:32.673','2021-01-18 19:06:17.304');
INSERT INTO attendance VALUES (256,'2021-01-18',9,'2021-01-18 08:24:14.802','2021-01-18 18:21:38.937');
INSERT INTO attendance VALUES (257,'2021-01-18',10,'2021-01-18 08:58:08.091','2021-01-18 18:19:59.503');
INSERT INTO attendance VALUES (258,'2021-01-18',11,'2021-01-18 08:44:49.043','2021-01-18 18:24:26.578');
INSERT INTO attendance VALUES (259,'2021-01-18',12,'2021-01-18 08:01:48.749','2021-01-18 19:01:20.965');
INSERT INTO attendance VALUES (260,'2021-01-18',13,'2021-01-18 08:43:43.79','2021-01-18 18:34:43.636');
INSERT INTO attendance VALUES (261,'2021-01-18',14,'2021-01-18 08:04:58.969','2021-01-18 18:48:15.643');
INSERT INTO attendance VALUES (262,'2021-01-18',15,'2021-01-18 08:47:45.409','2021-01-18 18:55:09.921');
INSERT INTO attendance VALUES (263,'2021-01-18',16,'2021-01-18 08:13:08.23','2021-01-18 18:42:17.513');
INSERT INTO attendance VALUES (264,'2021-01-18',17,'2021-01-18 08:40:16.287','2021-01-18 18:10:44.433');
INSERT INTO attendance VALUES (265,'2021-01-18',18,'2021-01-18 08:32:50.525','2021-01-18 18:25:50.616');
INSERT INTO attendance VALUES (266,'2021-01-18',19,'2021-01-18 08:57:09.037','2021-01-18 18:07:00.528');
INSERT INTO attendance VALUES (267,'2021-01-18',20,'2021-01-18 08:16:41.43','2021-01-18 18:07:39.176');
INSERT INTO attendance VALUES (268,'2021-01-18',21,'2021-01-18 08:08:02.001','2021-01-18 18:53:34.578');
INSERT INTO attendance VALUES (269,'2021-01-18',22,'2021-01-18 08:30:21.643','2021-01-18 18:20:35.109');
INSERT INTO attendance VALUES (270,'2021-01-18',23,'2021-01-18 08:10:33.122','2021-01-18 18:43:09.41');
INSERT INTO attendance VALUES (271,'2021-01-18',24,'2021-01-18 08:20:42.283','2021-01-18 19:02:29.152');
INSERT INTO attendance VALUES (272,'2021-01-18',25,'2021-01-18 08:34:11.047','2021-01-18 18:29:25.774');
INSERT INTO attendance VALUES (273,'2021-01-19',1,'2021-01-19 08:25:28.501','2021-01-19 18:49:01.66');
INSERT INTO attendance VALUES (274,'2021-01-19',2,'2021-01-19 08:59:12.962','2021-01-19 18:07:20.364');
INSERT INTO attendance VALUES (275,'2021-01-19',3,'2021-01-19 08:40:43.225','2021-01-19 19:13:10.255');
INSERT INTO attendance VALUES (276,'2021-01-19',4,'2021-01-19 08:56:14.976','2021-01-19 19:15:42.426');
INSERT INTO attendance VALUES (277,'2021-01-19',5,'2021-01-19 08:41:14.261','2021-01-19 18:56:20.343');
INSERT INTO attendance VALUES (278,'2021-01-19',6,'2021-01-19 08:29:15.149','2021-01-19 18:37:08.121');
INSERT INTO attendance VALUES (279,'2021-01-19',7,'2021-01-19 08:29:08.546','2021-01-19 19:21:37.378');
INSERT INTO attendance VALUES (280,'2021-01-19',8,'2021-01-19 08:01:58.721','2021-01-19 18:15:46.187');
INSERT INTO attendance VALUES (281,'2021-01-19',9,'2021-01-19 08:43:07.912','2021-01-19 19:06:25.244');
INSERT INTO attendance VALUES (282,'2021-01-19',10,'2021-01-19 08:44:04.067','2021-01-19 18:52:02.272');
INSERT INTO attendance VALUES (283,'2021-01-19',11,'2021-01-19 08:49:45.181','2021-01-19 18:48:51.065');
INSERT INTO attendance VALUES (284,'2021-01-19',12,'2021-01-19 08:08:21.273','2021-01-19 18:21:41.899');
INSERT INTO attendance VALUES (285,'2021-01-19',13,'2021-01-19 08:13:33.426','2021-01-19 18:07:12.624');
INSERT INTO attendance VALUES (286,'2021-01-19',14,'2021-01-19 08:32:59.061','2021-01-19 18:36:10.041');
INSERT INTO attendance VALUES (287,'2021-01-19',15,'2021-01-19 08:55:29.278','2021-01-19 19:03:23.164');
INSERT INTO attendance VALUES (288,'2021-01-19',16,'2021-01-19 08:07:16.274','2021-01-19 18:46:19.226');
INSERT INTO attendance VALUES (289,'2021-01-19',17,'2021-01-19 08:33:27.976','2021-01-19 18:11:22.581');
INSERT INTO attendance VALUES (290,'2021-01-19',18,'2021-01-19 08:18:43.662','2021-01-19 18:19:20.388');
INSERT INTO attendance VALUES (291,'2021-01-19',19,'2021-01-19 08:08:54.687','2021-01-19 18:38:49.117');
INSERT INTO attendance VALUES (292,'2021-01-19',20,'2021-01-19 08:37:58.737','2021-01-19 18:36:12.992');
INSERT INTO attendance VALUES (293,'2021-01-19',21,'2021-01-19 08:35:49.17','2021-01-19 19:08:58.04');
INSERT INTO attendance VALUES (294,'2021-01-19',22,'2021-01-19 08:57:43.637','2021-01-19 18:57:40.046');
INSERT INTO attendance VALUES (295,'2021-01-19',23,'2021-01-19 08:24:31.346','2021-01-19 18:46:03.475');
INSERT INTO attendance VALUES (296,'2021-01-19',24,'2021-01-19 08:07:30.903','2021-01-19 18:48:01.626');
INSERT INTO attendance VALUES (297,'2021-01-19',25,'2021-01-19 08:14:16.894','2021-01-19 17:44:08.288');
INSERT INTO attendance VALUES (298,'2021-01-20',1,'2021-01-20 08:51:31.401','2021-01-20 18:52:07.814');
INSERT INTO attendance VALUES (299,'2021-01-20',2,'2021-01-20 08:54:56.248','2021-01-20 18:26:17.163');
INSERT INTO attendance VALUES (300,'2021-01-20',3,'2021-01-20 08:30:17.563','2021-01-20 18:33:42.914');
INSERT INTO attendance VALUES (301,'2021-01-20',4,'2021-01-20 08:13:46.752','2021-01-20 19:12:17.123');
INSERT INTO attendance VALUES (302,'2021-01-20',5,'2021-01-20 08:31:58.528','2021-01-20 18:08:52.041');
INSERT INTO attendance VALUES (303,'2021-01-20',6,'2021-01-20 08:06:24.153','2021-01-20 18:09:10.27');
INSERT INTO attendance VALUES (304,'2021-01-20',7,'2021-01-20 08:45:44.866','2021-01-20 18:10:44.643');
INSERT INTO attendance VALUES (305,'2021-01-20',8,'2021-01-20 08:27:25.846','2021-01-20 18:55:50.361');
INSERT INTO attendance VALUES (306,'2021-01-20',9,'2021-01-20 08:02:13.827','2021-01-20 19:01:16.397');
INSERT INTO attendance VALUES (307,'2021-01-20',11,'2021-01-20 08:27:36.029','2021-01-20 18:22:57.277');
INSERT INTO attendance VALUES (308,'2021-01-20',12,'2021-01-20 08:26:44.205','2021-01-20 19:17:15.389');
INSERT INTO attendance VALUES (309,'2021-01-20',13,'2021-01-20 08:41:45.317','2021-01-20 19:19:20.827');
INSERT INTO attendance VALUES (310,'2021-01-20',14,'2021-01-20 08:55:35.26','2021-01-20 18:52:33.774');
INSERT INTO attendance VALUES (311,'2021-01-20',15,'2021-01-20 08:18:52.994','2021-01-20 19:09:16.844');
INSERT INTO attendance VALUES (312,'2021-01-20',16,'2021-01-20 08:00:08.471','2021-01-20 18:27:44.56');
INSERT INTO attendance VALUES (313,'2021-01-20',17,'2021-01-20 08:37:13.012','2021-01-20 18:27:35.32');
INSERT INTO attendance VALUES (314,'2021-01-20',18,'2021-01-20 08:06:56.989','2021-01-20 18:20:40.162');
INSERT INTO attendance VALUES (315,'2021-01-20',19,'2021-01-20 08:09:56.278','2021-01-20 18:54:41.805');
INSERT INTO attendance VALUES (316,'2021-01-20',20,'2021-01-20 08:53:18.546','2021-01-20 18:58:44.935');
INSERT INTO attendance VALUES (317,'2021-01-20',21,'2021-01-20 08:29:27.845','2021-01-20 19:13:51.709');
INSERT INTO attendance VALUES (318,'2021-01-20',22,'2021-01-20 08:34:03.379','2021-01-20 18:01:36.449');
INSERT INTO attendance VALUES (319,'2021-01-20',23,'2021-01-20 08:35:35.802','2021-01-20 19:22:49.519');
INSERT INTO attendance VALUES (320,'2021-01-20',24,'2021-01-20 08:40:29.945','2021-01-20 18:14:22.879');
INSERT INTO attendance VALUES (321,'2021-01-20',25,'2021-01-20 08:51:38.347','2021-01-20 18:44:12.118');
INSERT INTO attendance VALUES (322,'2021-01-21',1,'2021-01-21 08:26:17.141','2021-01-21 19:18:33.398');
INSERT INTO attendance VALUES (323,'2021-01-21',2,'2021-01-21 08:02:47.04','2021-01-21 19:14:40.596');
INSERT INTO attendance VALUES (324,'2021-01-21',3,'2021-01-21 08:44:09.648','2021-01-21 18:03:11.092');
INSERT INTO attendance VALUES (325,'2021-01-21',4,'2021-01-21 08:34:49.976','2021-01-21 18:46:11.472');
INSERT INTO attendance VALUES (326,'2021-01-21',5,'2021-01-21 08:58:57.719','2021-01-21 18:19:40.749');
INSERT INTO attendance VALUES (327,'2021-01-21',6,'2021-01-21 08:59:46.46','2021-01-21 18:01:29.84');
INSERT INTO attendance VALUES (328,'2021-01-21',7,'2021-01-21 08:41:13.902','2021-01-21 19:29:31.416');
INSERT INTO attendance VALUES (329,'2021-01-21',8,'2021-01-21 08:40:45.502','2021-01-21 19:26:36.045');
INSERT INTO attendance VALUES (330,'2021-01-21',9,'2021-01-21 08:45:14.186','2021-01-21 18:09:45.343');
INSERT INTO attendance VALUES (331,'2021-01-21',10,'2021-01-21 08:14:55.705','2021-01-21 18:14:18.067');
INSERT INTO attendance VALUES (332,'2021-01-21',11,'2021-01-21 08:58:39.466','2021-01-21 19:01:57.757');
INSERT INTO attendance VALUES (333,'2021-01-21',12,'2021-01-21 08:31:19.823','2021-01-21 18:40:24.29');
INSERT INTO attendance VALUES (334,'2021-01-21',13,'2021-01-21 08:04:22.294','2021-01-21 18:27:29.311');
INSERT INTO attendance VALUES (335,'2021-01-21',14,'2021-01-21 08:52:19.82','2021-01-21 18:36:49.144');
INSERT INTO attendance VALUES (336,'2021-01-21',15,'2021-01-21 08:32:43.374','2021-01-21 18:37:01.038');
INSERT INTO attendance VALUES (337,'2021-01-21',16,'2021-01-21 08:29:02.991','2021-01-21 18:53:05.063');
INSERT INTO attendance VALUES (338,'2021-01-21',17,'2021-01-21 08:02:52.773','2021-01-21 18:13:18.915');
INSERT INTO attendance VALUES (339,'2021-01-21',18,'2021-01-21 08:17:56.704','2021-01-21 18:11:24.56');
INSERT INTO attendance VALUES (340,'2021-01-21',19,'2021-01-21 08:38:14.923','2021-01-21 19:25:30.515');
INSERT INTO attendance VALUES (341,'2021-01-21',20,'2021-01-21 08:28:35.504','2021-01-21 18:43:43.162');
INSERT INTO attendance VALUES (342,'2021-01-21',21,'2021-01-21 08:12:19.521','2021-01-21 18:23:11.568');
INSERT INTO attendance VALUES (343,'2021-01-21',22,'2021-01-21 08:15:38.917','2021-01-21 18:04:44.178');
INSERT INTO attendance VALUES (344,'2021-01-21',23,'2021-01-21 08:19:13.815','2021-01-21 19:02:37.045');
INSERT INTO attendance VALUES (345,'2021-01-21',24,'2021-01-21 08:50:13.863','2021-01-21 18:29:30.941');
INSERT INTO attendance VALUES (346,'2021-01-21',25,'2021-01-21 08:20:52.983','2021-01-21 19:09:09.996');
INSERT INTO attendance VALUES (347,'2021-01-22',1,'2021-01-24 08:06:02.827','2021-01-24 18:44:46.866');
INSERT INTO attendance VALUES (348,'2021-01-22',2,'2021-01-24 08:27:04.464','2021-01-24 18:07:35.345');
INSERT INTO attendance VALUES (349,'2021-01-22',3,'2021-01-24 08:59:45.619','2021-01-24 18:52:46.549');
INSERT INTO attendance VALUES (350,'2021-01-22',4,'2021-01-24 08:06:31.34','2021-01-24 18:30:42.987');
INSERT INTO attendance VALUES (351,'2021-01-22',6,'2021-01-24 08:04:44.137','2021-01-24 19:20:01.897');
INSERT INTO attendance VALUES (352,'2021-01-22',7,'2021-01-24 08:07:25.052','2021-01-24 18:30:12.54');
INSERT INTO attendance VALUES (353,'2021-01-22',8,'2021-01-24 08:49:54.642','2021-01-24 18:40:24.375');
INSERT INTO attendance VALUES (354,'2021-01-22',9,'2021-01-24 08:16:58.017','2021-01-24 18:01:49.276');
INSERT INTO attendance VALUES (355,'2021-01-22',10,'2021-01-24 08:04:06.154','2021-01-24 18:09:04.816');
INSERT INTO attendance VALUES (356,'2021-01-22',11,'2021-01-24 08:12:37.81','2021-01-24 18:07:26.785');
INSERT INTO attendance VALUES (357,'2021-01-22',12,'2021-01-24 08:41:50.654','2021-01-24 18:40:15.909');
INSERT INTO attendance VALUES (358,'2021-01-22',13,'2021-01-24 08:00:18.838','2021-01-24 18:06:23.075');
INSERT INTO attendance VALUES (359,'2021-01-22',14,'2021-01-24 08:47:35.245','2021-01-24 19:11:39.584');
INSERT INTO attendance VALUES (360,'2021-01-22',15,'2021-01-24 08:10:52.873','2021-01-24 18:23:40.417');
INSERT INTO attendance VALUES (361,'2021-01-22',16,'2021-01-24 08:53:28.193','2021-01-24 18:17:40.485');
INSERT INTO attendance VALUES (362,'2021-01-22',17,'2021-01-24 08:28:21.949','2021-01-24 19:17:20.413');
INSERT INTO attendance VALUES (363,'2021-01-22',18,'2021-01-24 08:13:45.505','2021-01-24 18:55:59.713');
INSERT INTO attendance VALUES (364,'2021-01-22',19,'2021-01-24 08:30:31.205','2021-01-24 18:26:53.145');
INSERT INTO attendance VALUES (365,'2021-01-22',20,'2021-01-24 08:31:06.486','2021-01-24 18:14:34.155');
INSERT INTO attendance VALUES (366,'2021-01-22',21,'2021-01-24 08:44:12.079','2021-01-24 19:02:14.997');
INSERT INTO attendance VALUES (367,'2021-01-22',22,'2021-01-24 08:58:19.406','2021-01-24 19:17:30.086');
INSERT INTO attendance VALUES (368,'2021-01-22',23,'2021-01-24 08:39:54.341','2021-01-24 18:56:22.637');
INSERT INTO attendance VALUES (369,'2021-01-22',24,'2021-01-24 08:56:45.162','2021-01-24 18:20:50.701');
INSERT INTO attendance VALUES (370,'2021-01-22',25,'2021-01-24 08:01:51.153','2021-01-24 18:44:01.389');
INSERT INTO attendance VALUES (371,'2021-01-25',1,'2021-01-25 08:32:50.814','2021-01-25 18:11:23.635');
INSERT INTO attendance VALUES (372,'2021-01-25',2,'2021-01-25 08:38:20.045','2021-01-25 18:39:58.9');
INSERT INTO attendance VALUES (373,'2021-01-25',3,'2021-01-25 08:50:47.599','2021-01-25 19:20:47.878');
INSERT INTO attendance VALUES (374,'2021-01-25',4,'2021-01-25 08:01:25.458','2021-01-25 19:27:06.771');
INSERT INTO attendance VALUES (375,'2021-01-25',5,'2021-01-25 08:00:38.663','2021-01-25 18:27:57.132');
INSERT INTO attendance VALUES (376,'2021-01-25',6,'2021-01-25 08:05:53.58','2021-01-25 18:18:20.163');
INSERT INTO attendance VALUES (377,'2021-01-25',7,'2021-01-25 08:54:08.497','2021-01-25 18:40:11.88');
INSERT INTO attendance VALUES (378,'2021-01-25',8,'2021-01-25 08:30:22.027','2021-01-25 18:13:14.728');
INSERT INTO attendance VALUES (379,'2021-01-25',9,'2021-01-25 08:47:43.074','2021-01-25 18:19:12.591');
INSERT INTO attendance VALUES (380,'2021-01-25',10,'2021-01-25 08:19:19.497','2021-01-25 18:37:24.22');
INSERT INTO attendance VALUES (381,'2021-01-25',11,'2021-01-25 08:48:58.995','2021-01-25 19:16:28.08');
INSERT INTO attendance VALUES (382,'2021-01-25',12,'2021-01-25 08:31:04.96','2021-01-25 19:03:47.611');
INSERT INTO attendance VALUES (383,'2021-01-25',13,'2021-01-25 08:31:59.328','2021-01-25 18:20:34.174');
INSERT INTO attendance VALUES (384,'2021-01-25',14,'2021-01-25 08:33:50.062','2021-01-25 18:12:19.164');
INSERT INTO attendance VALUES (385,'2021-01-25',15,'2021-01-25 08:24:16.453','2021-01-25 18:31:21.01');
INSERT INTO attendance VALUES (386,'2021-01-25',16,'2021-01-25 08:45:49.814','2021-01-25 19:01:17.027');
INSERT INTO attendance VALUES (387,'2021-01-25',17,'2021-01-25 08:11:41.202','2021-01-25 18:05:41.218');
INSERT INTO attendance VALUES (388,'2021-01-25',18,'2021-01-25 08:06:33.17','2021-01-25 18:07:37.936');
INSERT INTO attendance VALUES (389,'2021-01-25',19,'2021-01-25 08:39:14.535','2021-01-25 18:27:02.232');
INSERT INTO attendance VALUES (390,'2021-01-25',20,'2021-01-25 08:49:16.421','2021-01-25 19:06:31.339');
INSERT INTO attendance VALUES (391,'2021-01-25',21,'2021-01-25 08:30:08.083','2021-01-25 19:02:18.963');
INSERT INTO attendance VALUES (392,'2021-01-25',22,'2021-01-25 08:00:32.55','2021-01-25 19:29:11.454');
INSERT INTO attendance VALUES (393,'2021-01-25',23,'2021-01-25 08:45:58.22','2021-01-25 18:54:50.924');
INSERT INTO attendance VALUES (394,'2021-01-25',24,'2021-01-25 08:21:36.782','2021-01-25 18:03:42.203');
INSERT INTO attendance VALUES (395,'2021-01-25',25,'2021-01-25 08:57:49.783','2021-01-25 18:30:53.051');
INSERT INTO attendance VALUES (396,'2021-01-26',1,'2021-01-26 08:46:54.221','2021-01-26 18:59:04.564');
INSERT INTO attendance VALUES (397,'2021-01-26',2,'2021-01-26 08:10:46.522','2021-01-26 19:05:11.068');
INSERT INTO attendance VALUES (398,'2021-01-26',3,'2021-01-26 08:53:01.13','2021-01-26 19:03:30.963');
INSERT INTO attendance VALUES (399,'2021-01-26',4,'2021-01-26 08:25:45.799','2021-01-26 19:05:54.718');
INSERT INTO attendance VALUES (400,'2021-01-26',5,'2021-01-26 08:03:29.67','2021-01-26 18:15:16.382');
INSERT INTO attendance VALUES (401,'2021-01-26',6,'2021-01-26 08:18:10.938','2021-01-26 18:27:56.984');
INSERT INTO attendance VALUES (402,'2021-01-26',7,'2021-01-26 08:34:20.8','2021-01-26 18:34:27.461');
INSERT INTO attendance VALUES (403,'2021-01-26',8,'2021-01-26 08:26:33.19','2021-01-26 18:15:59.188');
INSERT INTO attendance VALUES (404,'2021-01-26',9,'2021-01-26 08:29:11.146','2021-01-26 19:23:29.364');
INSERT INTO attendance VALUES (405,'2021-01-26',10,'2021-01-26 08:17:39.389','2021-01-26 18:54:46.657');
INSERT INTO attendance VALUES (406,'2021-01-26',11,'2021-01-26 08:25:53.776','2021-01-26 18:03:58.764');
INSERT INTO attendance VALUES (407,'2021-01-26',12,'2021-01-26 08:18:56.531','2021-01-26 19:10:37.079');
INSERT INTO attendance VALUES (408,'2021-01-26',13,'2021-01-26 08:10:32.341','2021-01-26 18:41:17.145');
INSERT INTO attendance VALUES (409,'2021-01-26',14,'2021-01-26 08:15:09.085','2021-01-26 18:36:57.258');
INSERT INTO attendance VALUES (410,'2021-01-26',15,'2021-01-26 08:19:54.738','2021-01-26 18:16:30.039');
INSERT INTO attendance VALUES (411,'2021-01-26',16,'2021-01-26 08:11:11.467','2021-01-26 19:26:56.628');
INSERT INTO attendance VALUES (412,'2021-01-26',17,'2021-01-26 08:39:24.967','2021-01-26 18:15:15.393');
INSERT INTO attendance VALUES (413,'2021-01-26',18,'2021-01-26 08:18:26.856','2021-01-26 18:31:21.422');
INSERT INTO attendance VALUES (414,'2021-01-26',19,'2021-01-26 08:23:31.986','2021-01-26 18:04:37.376');
INSERT INTO attendance VALUES (415,'2021-01-26',20,'2021-01-26 08:22:12.273','2021-01-26 19:07:56.24');
INSERT INTO attendance VALUES (416,'2021-01-26',21,'2021-01-26 08:51:02.933','2021-01-26 18:02:07.193');
INSERT INTO attendance VALUES (417,'2021-01-26',22,'2021-01-26 08:32:47.411','2021-01-26 18:22:06.547');
INSERT INTO attendance VALUES (418,'2021-01-26',23,'2021-01-26 08:46:23.45','2021-01-26 19:14:44.461');
INSERT INTO attendance VALUES (419,'2021-01-26',24,'2021-01-26 08:24:02.598','2021-01-26 18:44:17.639');
INSERT INTO attendance VALUES (420,'2021-01-26',25,'2021-01-26 08:54:34.645','2021-01-26 19:18:03.849');
INSERT INTO attendance VALUES (421,'2021-01-27',1,'2021-01-27 08:43:27.533','2021-01-27 18:10:22.73');
INSERT INTO attendance VALUES (422,'2021-01-27',2,'2021-01-27 08:29:46.592','2021-01-27 18:53:49.897');
INSERT INTO attendance VALUES (423,'2021-01-27',3,'2021-01-27 08:54:39.096','2021-01-27 18:34:00.797');
INSERT INTO attendance VALUES (424,'2021-01-27',4,'2021-01-27 08:18:29.394','2021-01-27 18:11:40.18');
INSERT INTO attendance VALUES (425,'2021-01-27',5,'2021-01-27 08:17:22.649','2021-01-27 18:19:40.506');
INSERT INTO attendance VALUES (426,'2021-01-27',6,'2021-01-27 08:50:20.383','2021-01-27 18:48:48.441');
INSERT INTO attendance VALUES (427,'2021-01-27',7,'2021-01-27 08:33:38.335','2021-01-27 18:18:39.592');
INSERT INTO attendance VALUES (428,'2021-01-27',8,'2021-01-27 08:33:30.523','2021-01-27 18:33:33.304');
INSERT INTO attendance VALUES (429,'2021-01-27',9,'2021-01-27 08:47:43.669','2021-01-27 18:41:57.067');
INSERT INTO attendance VALUES (430,'2021-01-27',10,'2021-01-27 08:45:31.512','2021-01-27 18:21:05.624');
INSERT INTO attendance VALUES (431,'2021-01-27',11,'2021-01-27 08:49:08.733','2021-01-27 18:14:02.457');
INSERT INTO attendance VALUES (432,'2021-01-27',12,'2021-01-27 08:20:06.228','2021-01-27 19:11:06.679');
INSERT INTO attendance VALUES (433,'2021-01-27',13,'2021-01-27 08:09:38.69','2021-01-27 18:13:38.656');
INSERT INTO attendance VALUES (434,'2021-01-27',14,'2021-01-27 09:00:11.061','2021-01-27 19:19:26.993');
INSERT INTO attendance VALUES (435,'2021-01-27',15,'2021-01-27 08:45:58.649','2021-01-27 18:59:29.979');
INSERT INTO attendance VALUES (436,'2021-01-27',16,'2021-01-27 08:21:01.728','2021-01-27 18:09:59.018');
INSERT INTO attendance VALUES (437,'2021-01-27',17,'2021-01-27 08:55:44.409','2021-01-27 19:19:07.503');
INSERT INTO attendance VALUES (438,'2021-01-27',18,'2021-01-27 08:45:31.677','2021-01-27 19:06:02.095');
INSERT INTO attendance VALUES (439,'2021-01-27',19,'2021-01-27 08:18:21.058','2021-01-27 18:04:55.2');
INSERT INTO attendance VALUES (440,'2021-01-27',20,'2021-01-27 08:29:18.128','2021-01-27 18:00:55.743');
INSERT INTO attendance VALUES (441,'2021-01-27',21,'2021-01-27 08:21:42.186','2021-01-27 18:12:28.633');
INSERT INTO attendance VALUES (442,'2021-01-27',22,'2021-01-27 08:47:23.797','2021-01-27 18:12:58.959');
INSERT INTO attendance VALUES (443,'2021-01-27',23,'2021-01-27 08:43:29.704','2021-01-27 18:50:21.561');
INSERT INTO attendance VALUES (444,'2021-01-27',24,'2021-01-27 08:23:36.712','2021-01-27 19:25:17.32');
INSERT INTO attendance VALUES (445,'2021-01-27',25,'2021-01-27 08:01:22.42','2021-01-27 18:31:59.285');
INSERT INTO attendance VALUES (446,'2021-01-28',1,'2021-01-28 08:57:34.217','2021-01-28 18:52:45.444');
INSERT INTO attendance VALUES (447,'2021-01-28',2,'2021-01-28 08:54:54.392','2021-01-28 18:19:36.335');
INSERT INTO attendance VALUES (448,'2021-01-28',3,'2021-01-28 08:56:33.694','2021-01-28 18:56:01.103');
INSERT INTO attendance VALUES (449,'2021-01-28',4,'2021-01-28 08:10:45.998','2021-01-28 19:03:09.834');
INSERT INTO attendance VALUES (450,'2021-01-28',5,'2021-01-28 08:55:04.184','2021-01-28 18:36:24.549');
INSERT INTO attendance VALUES (451,'2021-01-28',6,'2021-01-28 08:54:28.157','2021-01-28 19:09:46.627');
INSERT INTO attendance VALUES (452,'2021-01-28',7,'2021-01-28 08:03:25.87','2021-01-28 18:25:31.438');
INSERT INTO attendance VALUES (453,'2021-01-28',8,'2021-01-28 08:58:03.591','2021-01-28 18:14:01.072');
INSERT INTO attendance VALUES (454,'2021-01-28',9,'2021-01-28 08:15:15.748','2021-01-28 19:25:29.896');
INSERT INTO attendance VALUES (455,'2021-01-28',10,'2021-01-28 08:43:34.3','2021-01-28 18:59:03.158');
INSERT INTO attendance VALUES (456,'2021-01-28',11,'2021-01-28 08:47:07.086','2021-01-28 18:06:27.584');
INSERT INTO attendance VALUES (457,'2021-01-28',12,'2021-01-28 08:05:16.008','2021-01-28 18:03:13.385');
INSERT INTO attendance VALUES (458,'2021-01-28',13,'2021-01-28 08:16:48.372','2021-01-28 18:45:00.752');
INSERT INTO attendance VALUES (459,'2021-01-28',14,'2021-01-28 08:17:33.852','2021-01-28 18:47:02.8');
INSERT INTO attendance VALUES (460,'2021-01-28',15,'2021-01-28 08:29:15.423','2021-01-28 19:19:36.653');
INSERT INTO attendance VALUES (461,'2021-01-28',16,'2021-01-28 08:33:35.223','2021-01-28 18:26:26.948');
INSERT INTO attendance VALUES (462,'2021-01-28',17,'2021-01-28 08:24:49.433','2021-01-28 18:47:58.732');
INSERT INTO attendance VALUES (463,'2021-01-28',18,'2021-01-28 08:46:49.457','2021-01-28 18:18:22.37');
INSERT INTO attendance VALUES (464,'2021-01-28',19,'2021-01-28 08:12:05.06','2021-01-28 18:52:52.849');
INSERT INTO attendance VALUES (465,'2021-01-28',20,'2021-01-28 08:46:25.458','2021-01-28 19:13:31.868');
INSERT INTO attendance VALUES (466,'2021-01-28',21,'2021-01-28 08:46:49.514','2021-01-28 19:09:58.14');
INSERT INTO attendance VALUES (467,'2021-01-28',22,'2021-01-28 08:08:34.551','2021-01-28 18:52:46.033');
INSERT INTO attendance VALUES (468,'2021-01-28',23,'2021-01-28 08:16:19.864','2021-01-28 19:15:26.034');
INSERT INTO attendance VALUES (469,'2021-01-28',24,'2021-01-28 08:32:09.904','2021-01-28 18:46:51.614');
INSERT INTO attendance VALUES (470,'2021-01-28',25,'2021-01-28 08:18:32.066','2021-01-28 18:03:22.204');
INSERT INTO attendance VALUES (471,'2021-01-29',1,'2021-01-31 08:13:33.925','2021-01-31 19:19:45.593');
INSERT INTO attendance VALUES (472,'2021-01-29',2,'2021-01-31 08:36:15.024','2021-01-31 18:24:12.728');
INSERT INTO attendance VALUES (473,'2021-01-29',3,'2021-01-31 08:52:14.092','2021-01-31 19:10:51.328');
INSERT INTO attendance VALUES (474,'2021-01-29',4,'2021-01-31 08:06:29.025','2021-01-31 18:49:04.973');
INSERT INTO attendance VALUES (475,'2021-01-29',5,'2021-01-31 08:59:44.646','2021-01-31 18:00:58.809');
INSERT INTO attendance VALUES (476,'2021-01-29',6,'2021-01-31 08:59:25.199','2021-01-31 19:24:17.327');
INSERT INTO attendance VALUES (477,'2021-01-29',7,'2021-01-31 08:03:35.949','2021-01-31 19:11:35.583');
INSERT INTO attendance VALUES (478,'2021-01-29',8,'2021-01-31 08:55:41.736','2021-01-31 18:07:36.566');
INSERT INTO attendance VALUES (479,'2021-01-29',9,'2021-01-31 08:31:09.867','2021-01-31 18:09:15.552');
INSERT INTO attendance VALUES (480,'2021-01-29',10,'2021-01-31 08:00:47.301','2021-01-31 18:31:11.902');
INSERT INTO attendance VALUES (481,'2021-01-29',11,'2021-01-31 08:09:57.843','2021-01-31 18:56:01.037');
INSERT INTO attendance VALUES (482,'2021-01-29',12,'2021-01-31 08:33:13.425','2021-01-31 18:24:45.367');
INSERT INTO attendance VALUES (483,'2021-01-29',13,'2021-01-31 08:00:17.522','2021-01-31 18:00:08.211');
INSERT INTO attendance VALUES (484,'2021-01-29',14,'2021-01-31 08:28:42.511','2021-01-31 18:44:09.882');
INSERT INTO attendance VALUES (485,'2021-01-29',15,'2021-01-31 08:52:53.799','2021-01-31 18:52:40.081');
INSERT INTO attendance VALUES (486,'2021-01-29',16,'2021-01-31 08:06:40.745','2021-01-31 19:00:19.793');
INSERT INTO attendance VALUES (487,'2021-01-29',17,'2021-01-31 08:46:41.241','2021-01-31 19:01:34.619');
INSERT INTO attendance VALUES (488,'2021-01-29',19,'2021-01-31 08:42:15.648','2021-01-31 18:23:57.765');
INSERT INTO attendance VALUES (489,'2021-01-29',20,'2021-01-31 08:31:06.961','2021-01-31 18:16:45.358');
INSERT INTO attendance VALUES (490,'2021-01-29',21,'2021-01-31 08:29:01.671','2021-01-31 18:38:20.186');
INSERT INTO attendance VALUES (491,'2021-01-29',22,'2021-01-31 08:08:36.581','2021-01-31 18:01:04.587');
INSERT INTO attendance VALUES (492,'2021-01-29',23,'2021-01-31 08:44:07.088','2021-01-31 18:42:24.507');
INSERT INTO attendance VALUES (493,'2021-01-29',24,'2021-01-31 08:48:33.944','2021-01-31 18:00:21.114');
INSERT INTO attendance VALUES (494,'2021-01-29',25,'2021-01-31 08:02:05.548','2021-01-31 18:36:05.476');
select c.calendar_date, e.emp_name, a.clock_in, a.clock_out,
case when a.clock_out is null then '缺勤' when EXTRACT(hour from a.clock_in)>=9 then '迟到' when EXTRACT(hour from a.clock_out)<18 then '早退' end as '考勤状态'
from (select * from calendar where calendar_year=2021 and calendar_month=1 and is_work_day='Y' order by calendar_date desc) c
cross join employee e
left join attendance a on (a.check_date=c.calendar_date and a.emp_id=e.emp_id)
where a.id is null or a.clock_out is null or EXTRACT(hour from a.clock_in)>=9 or EXTRACT(hour from a.clock_out)<18;
SQL 案例分析:CASE 条件表达式
SQL 案例分析:CASE 条件表达式
-- 截至2020年入职年限不满10年的员工,男性员工的礼品为手表一块,女性员工的礼品为化妆品一套;
-- 截至2020年入职年限满10年不满15年的员工,男性员工的礼品为手机一块,女性员工的礼品为项链一套;
-- 截至2020年入职年限满15年的员工,不论男女礼品为电脑一台
SELECT emp_name, sex, hire_date,
case when EXTRACT(year from hire_date)>2011 and sex='男' then '手表'
when EXTRACT(year from hire_date)>2011 and sex='女' then '化妆品'
when EXTRACT(year from hire_date)>2006 and sex='男' then '手机'
when EXTRACT(year from hire_date)>2011 and sex='女' then '项链'
else '电脑'
end as '礼品'
FROM `employee`;
SQL案例分析:数据透视表
介绍如何通过SQL查询语句实现Excel数据透视表功能。
-- 创建销售数据表sales_data
-- saledate表示销售日期,product表示产品名称,channel表示销售渠道,amount表示销售金额
CREATE TABLE sales_data(saledate DATE, product VARCHAR(20), channel VARCHAR(20), amount NUMERIC(10, 2));
-- 插入测试数据
-- 只有Oracle数据库需要执行以下alter语句
-- alter session set nls_date_format = 'YYYY-MM-DD';
INSERT INTO sales_data VALUES ('2019-01-01','桔子','淘宝',1864.00);
INSERT INTO sales_data VALUES ('2019-01-01','桔子','京东',1329.00);
INSERT INTO sales_data VALUES ('2019-01-01','桔子','店面',1736.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','淘宝',1573.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','京东',1364.00);
INSERT INTO sales_data VALUES ('2019-01-01','香蕉','店面',1178.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','淘宝',511.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','京东',568.00);
INSERT INTO sales_data VALUES ('2019-01-01','苹果','店面',847.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','淘宝',1923.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','京东',775.00);
INSERT INTO sales_data VALUES ('2019-01-02','桔子','店面',599.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','淘宝',1612.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','京东',1057.00);
INSERT INTO sales_data VALUES ('2019-01-02','香蕉','店面',1580.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','淘宝',1345.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','京东',564.00);
INSERT INTO sales_data VALUES ('2019-01-02','苹果','店面',1953.00);
INSERT INTO sales_data VALUES ('2019-01-03','桔子','淘宝',729.00);
INSERT INTO sales_data VALUES ('2019-01-03','桔子','京东',1758.00);
INSERT INTO sales_data VALUES ('2019-01-03','桔子','店面',918.00);
INSERT INTO sales_data VALUES ('2019-01-03','香蕉','淘宝',1879.00);
INSERT INTO sales_data VALUES ('2019-01-03','香蕉','京东',1142.00);
INSERT INTO sales_data VALUES ('2019-01-03','香蕉','店面',731.00);
INSERT INTO sales_data VALUES ('2019-01-03','苹果','淘宝',1329.00);
INSERT INTO sales_data VALUES ('2019-01-03','苹果','京东',1315.00);
INSERT INTO sales_data VALUES ('2019-01-03','苹果','店面',1956.00);
INSERT INTO sales_data VALUES ('2019-01-04','桔子','淘宝',547.00);
INSERT INTO sales_data VALUES ('2019-01-04','桔子','京东',1462.00);
INSERT INTO sales_data VALUES ('2019-01-04','桔子','店面',1418.00);
INSERT INTO sales_data VALUES ('2019-01-04','香蕉','淘宝',1205.00);
INSERT INTO sales_data VALUES ('2019-01-04','香蕉','京东',1326.00);
INSERT INTO sales_data VALUES ('2019-01-04','香蕉','店面',746.00);
INSERT INTO sales_data VALUES ('2019-01-04','苹果','淘宝',940.00);
INSERT INTO sales_data VALUES ('2019-01-04','苹果','京东',898.00);
INSERT INTO sales_data VALUES ('2019-01-04','苹果','店面',1610.00);
INSERT INTO sales_data VALUES ('2019-01-05','桔子','淘宝',1624.00);
INSERT INTO sales_data VALUES ('2019-01-05','桔子','京东',915.00);
INSERT INTO sales_data VALUES ('2019-01-05','桔子','店面',1683.00);
INSERT INTO sales_data VALUES ('2019-01-05','香蕉','淘宝',1970.00);
INSERT INTO sales_data VALUES ('2019-01-05','香蕉','京东',833.00);
INSERT INTO sales_data VALUES ('2019-01-05','香蕉','店面',1954.00);
INSERT INTO sales_data VALUES ('2019-01-05','苹果','淘宝',565.00);
INSERT INTO sales_data VALUES ('2019-01-05','苹果','京东',1940.00);
INSERT INTO sales_data VALUES ('2019-01-05','苹果','店面',1006.00);
INSERT INTO sales_data VALUES ('2019-01-06','桔子','淘宝',1645.00);
INSERT INTO sales_data VALUES ('2019-01-06','桔子','京东',1285.00);
INSERT INTO sales_data VALUES ('2019-01-06','桔子','店面',1069.00);
INSERT INTO sales_data VALUES ('2019-01-06','香蕉','淘宝',1593.00);
INSERT INTO sales_data VALUES ('2019-01-06','香蕉','京东',1504.00);
INSERT INTO sales_data VALUES ('2019-01-06','香蕉','店面',817.00);
INSERT INTO sales_data VALUES ('2019-01-06','苹果','淘宝',2000.00);
INSERT INTO sales_data VALUES ('2019-01-06','苹果','京东',1373.00);
INSERT INTO sales_data VALUES ('2019-01-06','苹果','店面',1450.00);
INSERT INTO sales_data VALUES ('2019-01-07','桔子','淘宝',727.00);
INSERT INTO sales_data VALUES ('2019-01-07','桔子','京东',698.00);
INSERT INTO sales_data VALUES ('2019-01-07','桔子','店面',759.00);
INSERT INTO sales_data VALUES ('2019-01-07','香蕉','淘宝',673.00);
INSERT INTO sales_data VALUES ('2019-01-07','香蕉','京东',729.00);
INSERT INTO sales_data VALUES ('2019-01-07','香蕉','店面',1706.00);
INSERT INTO sales_data VALUES ('2019-01-07','苹果','淘宝',1575.00);
INSERT INTO sales_data VALUES ('2019-01-07','苹果','京东',1419.00);
INSERT INTO sales_data VALUES ('2019-01-07','苹果','店面',1017.00);
INSERT INTO sales_data VALUES ('2019-01-08','桔子','淘宝',1811.00);
INSERT INTO sales_data VALUES ('2019-01-08','桔子','京东',1849.00);
INSERT INTO sales_data VALUES ('2019-01-08','桔子','店面',1405.00);
INSERT INTO sales_data VALUES ('2019-01-08','香蕉','淘宝',1406.00);
INSERT INTO sales_data VALUES ('2019-01-08','香蕉','京东',1453.00);
INSERT INTO sales_data VALUES ('2019-01-08','香蕉','店面',1800.00);
INSERT INTO sales_data VALUES ('2019-01-08','苹果','淘宝',1070.00);
INSERT INTO sales_data VALUES ('2019-01-08','苹果','京东',1403.00);
INSERT INTO sales_data VALUES ('2019-01-08','苹果','店面',613.00);
INSERT INTO sales_data VALUES ('2019-01-09','桔子','淘宝',1009.00);
INSERT INTO sales_data VALUES ('2019-01-09','桔子','京东',1453.00);
INSERT INTO sales_data VALUES ('2019-01-09','桔子','店面',2038.00);
INSERT INTO sales_data VALUES ('2019-01-09','香蕉','淘宝',1495.00);
INSERT INTO sales_data VALUES ('2019-01-09','香蕉','京东',1073.00);
INSERT INTO sales_data VALUES ('2019-01-09','香蕉','店面',1298.00);
INSERT INTO sales_data VALUES ('2019-01-09','苹果','淘宝',2039.00);
INSERT INTO sales_data VALUES ('2019-01-09','苹果','京东',641.00);
INSERT INTO sales_data VALUES ('2019-01-09','苹果','店面',777.00);
INSERT INTO sales_data VALUES ('2019-01-10','桔子','淘宝',836.00);
INSERT INTO sales_data VALUES ('2019-01-10','桔子','京东',621.00);
INSERT INTO sales_data VALUES ('2019-01-10','桔子','店面',1630.00);
INSERT INTO sales_data VALUES ('2019-01-10','香蕉','淘宝',1761.00);
INSERT INTO sales_data VALUES ('2019-01-10','香蕉','京东',818.00);
INSERT INTO sales_data VALUES ('2019-01-10','香蕉','店面',1797.00);
INSERT INTO sales_data VALUES ('2019-01-10','苹果','淘宝',1990.00);
INSERT INTO sales_data VALUES ('2019-01-10','苹果','京东',960.00);
INSERT INTO sales_data VALUES ('2019-01-10','苹果','店面',1997.00);
INSERT INTO sales_data VALUES ('2019-01-11','桔子','淘宝',1671.00);
INSERT INTO sales_data VALUES ('2019-01-11','桔子','京东',2010.00);
INSERT INTO sales_data VALUES ('2019-01-11','桔子','店面',1391.00);
INSERT INTO sales_data VALUES ('2019-01-11','香蕉','淘宝',658.00);
INSERT INTO sales_data VALUES ('2019-01-11','香蕉','京东',1786.00);
INSERT INTO sales_data VALUES ('2019-01-11','香蕉','店面',1205.00);
INSERT INTO sales_data VALUES ('2019-01-11','苹果','淘宝',1528.00);
INSERT INTO sales_data VALUES ('2019-01-11','苹果','京东',1158.00);
INSERT INTO sales_data VALUES ('2019-01-11','苹果','店面',623.00);
INSERT INTO sales_data VALUES ('2019-01-12','桔子','淘宝',1299.00);
INSERT INTO sales_data VALUES ('2019-01-12','桔子','京东',1698.00);
INSERT INTO sales_data VALUES ('2019-01-12','桔子','店面',1497.00);
INSERT INTO sales_data VALUES ('2019-01-12','香蕉','淘宝',1377.00);
INSERT INTO sales_data VALUES ('2019-01-12','香蕉','京东',667.00);
INSERT INTO sales_data VALUES ('2019-01-12','香蕉','店面',910.00);
INSERT INTO sales_data VALUES ('2019-01-12','苹果','淘宝',1374.00);
INSERT INTO sales_data VALUES ('2019-01-12','苹果','京东',1621.00);
INSERT INTO sales_data VALUES ('2019-01-12','苹果','店面',1443.00);
INSERT INTO sales_data VALUES ('2019-01-13','桔子','淘宝',637.00);
INSERT INTO sales_data VALUES ('2019-01-13','桔子','京东',1625.00);
INSERT INTO sales_data VALUES ('2019-01-13','桔子','店面',1548.00);
INSERT INTO sales_data VALUES ('2019-01-13','香蕉','淘宝',873.00);
INSERT INTO sales_data VALUES ('2019-01-13','香蕉','京东',1916.00);
INSERT INTO sales_data VALUES ('2019-01-13','香蕉','店面',1624.00);
INSERT INTO sales_data VALUES ('2019-01-13','苹果','淘宝',1958.00);
INSERT INTO sales_data VALUES ('2019-01-13','苹果','京东',1632.00);
INSERT INTO sales_data VALUES ('2019-01-13','苹果','店面',1897.00);
INSERT INTO sales_data VALUES ('2019-01-14','桔子','淘宝',1715.00);
INSERT INTO sales_data VALUES ('2019-01-14','桔子','京东',1582.00);
INSERT INTO sales_data VALUES ('2019-01-14','桔子','店面',817.00);
INSERT INTO sales_data VALUES ('2019-01-14','香蕉','淘宝',1667.00);
INSERT INTO sales_data VALUES ('2019-01-14','香蕉','京东',1203.00);
INSERT INTO sales_data VALUES ('2019-01-14','香蕉','店面',777.00);
INSERT INTO sales_data VALUES ('2019-01-14','苹果','淘宝',1008.00);
INSERT INTO sales_data VALUES ('2019-01-14','苹果','京东',1311.00);
INSERT INTO sales_data VALUES ('2019-01-14','苹果','店面',2013.00);
INSERT INTO sales_data VALUES ('2019-01-15','桔子','淘宝',1668.00);
INSERT INTO sales_data VALUES ('2019-01-15','桔子','京东',794.00);
INSERT INTO sales_data VALUES ('2019-01-15','桔子','店面',1126.00);
INSERT INTO sales_data VALUES ('2019-01-15','香蕉','淘宝',1741.00);
INSERT INTO sales_data VALUES ('2019-01-15','香蕉','京东',1538.00);
INSERT INTO sales_data VALUES ('2019-01-15','香蕉','店面',768.00);
INSERT INTO sales_data VALUES ('2019-01-15','苹果','淘宝',1183.00);
INSERT INTO sales_data VALUES ('2019-01-15','苹果','京东',859.00);
INSERT INTO sales_data VALUES ('2019-01-15','苹果','店面',880.00);
INSERT INTO sales_data VALUES ('2019-01-16','桔子','淘宝',1543.00);
INSERT INTO sales_data VALUES ('2019-01-16','桔子','京东',1684.00);
INSERT INTO sales_data VALUES ('2019-01-16','桔子','店面',1951.00);
INSERT INTO sales_data VALUES ('2019-01-16','香蕉','淘宝',930.00);
INSERT INTO sales_data VALUES ('2019-01-16','香蕉','京东',1760.00);
INSERT INTO sales_data VALUES ('2019-01-16','香蕉','店面',1517.00);
INSERT INTO sales_data VALUES ('2019-01-16','苹果','淘宝',1918.00);
INSERT INTO sales_data VALUES ('2019-01-16','苹果','京东',2073.00);
INSERT INTO sales_data VALUES ('2019-01-16','苹果','店面',1373.00);
INSERT INTO sales_data VALUES ('2019-01-17','桔子','淘宝',1487.00);
INSERT INTO sales_data VALUES ('2019-01-17','桔子','京东',1976.00);
INSERT INTO sales_data VALUES ('2019-01-17','桔子','店面',950.00);
INSERT INTO sales_data VALUES ('2019-01-17','香蕉','淘宝',1324.00);
INSERT INTO sales_data VALUES ('2019-01-17','香蕉','京东',1627.00);
INSERT INTO sales_data VALUES ('2019-01-17','香蕉','店面',1967.00);
INSERT INTO sales_data VALUES ('2019-01-17','苹果','淘宝',1576.00);
INSERT INTO sales_data VALUES ('2019-01-17','苹果','京东',1229.00);
INSERT INTO sales_data VALUES ('2019-01-17','苹果','店面',1105.00);
INSERT INTO sales_data VALUES ('2019-01-18','桔子','淘宝',1792.00);
INSERT INTO sales_data VALUES ('2019-01-18','桔子','京东',1676.00);
INSERT INTO sales_data VALUES ('2019-01-18','桔子','店面',1856.00);
INSERT INTO sales_data VALUES ('2019-01-18','香蕉','淘宝',1740.00);
INSERT INTO sales_data VALUES ('2019-01-18','香蕉','京东',1274.00);
INSERT INTO sales_data VALUES ('2019-01-18','香蕉','店面',2080.00);
INSERT INTO sales_data VALUES ('2019-01-18','苹果','淘宝',796.00);
INSERT INTO sales_data VALUES ('2019-01-18','苹果','京东',946.00);
INSERT INTO sales_data VALUES ('2019-01-18','苹果','店面',1548.00);
INSERT INTO sales_data VALUES ('2019-01-19','桔子','淘宝',1000.00);
INSERT INTO sales_data VALUES ('2019-01-19','桔子','京东',1563.00);
INSERT INTO sales_data VALUES ('2019-01-19','桔子','店面',1843.00);
INSERT INTO sales_data VALUES ('2019-01-19','香蕉','淘宝',1310.00);
INSERT INTO sales_data VALUES ('2019-01-19','香蕉','京东',1031.00);
INSERT INTO sales_data VALUES ('2019-01-19','香蕉','店面',1451.00);
INSERT INTO sales_data VALUES ('2019-01-19','苹果','淘宝',1186.00);
INSERT INTO sales_data VALUES ('2019-01-19','苹果','京东',1386.00);
INSERT INTO sales_data VALUES ('2019-01-19','苹果','店面',1137.00);
INSERT INTO sales_data VALUES ('2019-01-20','桔子','淘宝',633.00);
INSERT INTO sales_data VALUES ('2019-01-20','桔子','京东',1235.00);
INSERT INTO sales_data VALUES ('2019-01-20','桔子','店面',1140.00);
INSERT INTO sales_data VALUES ('2019-01-20','香蕉','淘宝',1431.00);
INSERT INTO sales_data VALUES ('2019-01-20','香蕉','京东',642.00);
INSERT INTO sales_data VALUES ('2019-01-20','香蕉','店面',1036.00);
INSERT INTO sales_data VALUES ('2019-01-20','苹果','淘宝',1801.00);
INSERT INTO sales_data VALUES ('2019-01-20','苹果','京东',1386.00);
INSERT INTO sales_data VALUES ('2019-01-20','苹果','店面',2083.00);
INSERT INTO sales_data VALUES ('2019-01-21','桔子','淘宝',1694.00);
INSERT INTO sales_data VALUES ('2019-01-21','桔子','京东',887.00);
INSERT INTO sales_data VALUES ('2019-01-21','桔子','店面',1236.00);
INSERT INTO sales_data VALUES ('2019-01-21','香蕉','淘宝',719.00);
INSERT INTO sales_data VALUES ('2019-01-21','香蕉','京东',2094.00);
INSERT INTO sales_data VALUES ('2019-01-21','香蕉','店面',828.00);
INSERT INTO sales_data VALUES ('2019-01-21','苹果','淘宝',1990.00);
INSERT INTO sales_data VALUES ('2019-01-21','苹果','京东',1749.00);
INSERT INTO sales_data VALUES ('2019-01-21','苹果','店面',1517.00);
INSERT INTO sales_data VALUES ('2019-01-22','桔子','淘宝',1990.00);
INSERT INTO sales_data VALUES ('2019-01-22','桔子','京东',1965.00);
INSERT INTO sales_data VALUES ('2019-01-22','桔子','店面',1883.00);
INSERT INTO sales_data VALUES ('2019-01-22','香蕉','淘宝',1454.00);
INSERT INTO sales_data VALUES ('2019-01-22','香蕉','京东',875.00);
INSERT INTO sales_data VALUES ('2019-01-22','香蕉','店面',1356.00);
INSERT INTO sales_data VALUES ('2019-01-22','苹果','淘宝',1207.00);
INSERT INTO sales_data VALUES ('2019-01-22','苹果','京东',1595.00);
INSERT INTO sales_data VALUES ('2019-01-22','苹果','店面',1797.00);
INSERT INTO sales_data VALUES ('2019-01-23','桔子','淘宝',2073.00);
INSERT INTO sales_data VALUES ('2019-01-23','桔子','京东',696.00);
INSERT INTO sales_data VALUES ('2019-01-23','桔子','店面',1099.00);
INSERT INTO sales_data VALUES ('2019-01-23','香蕉','淘宝',1120.00);
INSERT INTO sales_data VALUES ('2019-01-23','香蕉','京东',733.00);
INSERT INTO sales_data VALUES ('2019-01-23','香蕉','店面',1739.00);
INSERT INTO sales_data VALUES ('2019-01-23','苹果','淘宝',1665.00);
INSERT INTO sales_data VALUES ('2019-01-23','苹果','京东',1569.00);
INSERT INTO sales_data VALUES ('2019-01-23','苹果','店面',1786.00);
INSERT INTO sales_data VALUES ('2019-01-24','桔子','淘宝',2111.00);
INSERT INTO sales_data VALUES ('2019-01-24','桔子','京东',1280.00);
INSERT INTO sales_data VALUES ('2019-01-24','桔子','店面',1082.00);
INSERT INTO sales_data VALUES ('2019-01-24','香蕉','淘宝',2099.00);
INSERT INTO sales_data VALUES ('2019-01-24','香蕉','京东',874.00);
INSERT INTO sales_data VALUES ('2019-01-24','香蕉','店面',1369.00);
INSERT INTO sales_data VALUES ('2019-01-24','苹果','淘宝',1235.00);
INSERT INTO sales_data VALUES ('2019-01-24','苹果','京东',993.00);
INSERT INTO sales_data VALUES ('2019-01-24','苹果','店面',1363.00);
INSERT INTO sales_data VALUES ('2019-01-25','桔子','淘宝',1468.00);
INSERT INTO sales_data VALUES ('2019-01-25','桔子','京东',888.00);
INSERT INTO sales_data VALUES ('2019-01-25','桔子','店面',1017.00);
INSERT INTO sales_data VALUES ('2019-01-25','香蕉','淘宝',885.00);
INSERT INTO sales_data VALUES ('2019-01-25','香蕉','京东',773.00);
INSERT INTO sales_data VALUES ('2019-01-25','香蕉','店面',878.00);
INSERT INTO sales_data VALUES ('2019-01-25','苹果','淘宝',662.00);
INSERT INTO sales_data VALUES ('2019-01-25','苹果','京东',1622.00);
INSERT INTO sales_data VALUES ('2019-01-25','苹果','店面',1148.00);
INSERT INTO sales_data VALUES ('2019-01-26','桔子','淘宝',1418.00);
INSERT INTO sales_data VALUES ('2019-01-26','桔子','京东',729.00);
INSERT INTO sales_data VALUES ('2019-01-26','桔子','店面',643.00);
INSERT INTO sales_data VALUES ('2019-01-26','香蕉','淘宝',1111.00);
INSERT INTO sales_data VALUES ('2019-01-26','香蕉','京东',692.00);
INSERT INTO sales_data VALUES ('2019-01-26','香蕉','店面',728.00);
INSERT INTO sales_data VALUES ('2019-01-26','苹果','淘宝',1600.00);
INSERT INTO sales_data VALUES ('2019-01-26','苹果','京东',1202.00);
INSERT INTO sales_data VALUES ('2019-01-26','苹果','店面',851.00);
INSERT INTO sales_data VALUES ('2019-01-27','桔子','淘宝',1233.00);
INSERT INTO sales_data VALUES ('2019-01-27','桔子','京东',761.00);
INSERT INTO sales_data VALUES ('2019-01-27','桔子','店面',1816.00);
INSERT INTO sales_data VALUES ('2019-01-27','香蕉','淘宝',909.00);
INSERT INTO sales_data VALUES ('2019-01-27','香蕉','京东',757.00);
INSERT INTO sales_data VALUES ('2019-01-27','香蕉','店面',981.00);
INSERT INTO sales_data VALUES ('2019-01-27','苹果','淘宝',1376.00);
INSERT INTO sales_data VALUES ('2019-01-27','苹果','京东',741.00);
INSERT INTO sales_data VALUES ('2019-01-27','苹果','店面',1240.00);
INSERT INTO sales_data VALUES ('2019-01-28','桔子','淘宝',635.00);
INSERT INTO sales_data VALUES ('2019-01-28','桔子','京东',1366.00);
INSERT INTO sales_data VALUES ('2019-01-28','桔子','店面',1623.00);
INSERT INTO sales_data VALUES ('2019-01-28','香蕉','淘宝',1383.00);
INSERT INTO sales_data VALUES ('2019-01-28','香蕉','京东',713.00);
INSERT INTO sales_data VALUES ('2019-01-28','香蕉','店面',1891.00);
INSERT INTO sales_data VALUES ('2019-01-28','苹果','淘宝',1781.00);
INSERT INTO sales_data VALUES ('2019-01-28','苹果','京东',978.00);
INSERT INTO sales_data VALUES ('2019-01-28','苹果','店面',2044.00);
INSERT INTO sales_data VALUES ('2019-01-29','桔子','淘宝',2044.00);
INSERT INTO sales_data VALUES ('2019-01-29','桔子','京东',1026.00);
INSERT INTO sales_data VALUES ('2019-01-29','桔子','店面',1551.00);
INSERT INTO sales_data VALUES ('2019-01-29','香蕉','淘宝',1071.00);
INSERT INTO sales_data VALUES ('2019-01-29','香蕉','京东',1819.00);
INSERT INTO sales_data VALUES ('2019-01-29','香蕉','店面',1655.00);
INSERT INTO sales_data VALUES ('2019-01-29','苹果','淘宝',1089.00);
INSERT INTO sales_data VALUES ('2019-01-29','苹果','京东',805.00);
INSERT INTO sales_data VALUES ('2019-01-29','苹果','店面',1722.00);
INSERT INTO sales_data VALUES ('2019-01-30','桔子','淘宝',1197.00);
INSERT INTO sales_data VALUES ('2019-01-30','桔子','京东',1785.00);
INSERT INTO sales_data VALUES ('2019-01-30','桔子','店面',804.00);
INSERT INTO sales_data VALUES ('2019-01-30','香蕉','淘宝',1424.00);
INSERT INTO sales_data VALUES ('2019-01-30','香蕉','京东',888.00);
INSERT INTO sales_data VALUES ('2019-01-30','香蕉','店面',935.00);
INSERT INTO sales_data VALUES ('2019-01-30','苹果','淘宝',1109.00);
INSERT INTO sales_data VALUES ('2019-01-30','苹果','京东',1167.00);
INSERT INTO sales_data VALUES ('2019-01-30','苹果','店面',1062.00);
INSERT INTO sales_data VALUES ('2019-01-31','桔子','淘宝',1465.00);
INSERT INTO sales_data VALUES ('2019-01-31','桔子','京东',1918.00);
INSERT INTO sales_data VALUES ('2019-01-31','桔子','店面',1178.00);
INSERT INTO sales_data VALUES ('2019-01-31','香蕉','淘宝',2075.00);
INSERT INTO sales_data VALUES ('2019-01-31','香蕉','京东',1918.00);
INSERT INTO sales_data VALUES ('2019-01-31','香蕉','店面',1908.00);
INSERT INTO sales_data VALUES ('2019-01-31','苹果','淘宝',1563.00);
INSERT INTO sales_data VALUES ('2019-01-31','苹果','京东',1166.00);
INSERT INTO sales_data VALUES ('2019-01-31','苹果','店面',1987.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','淘宝',1324.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','京东',817.00);
INSERT INTO sales_data VALUES ('2019-02-01','桔子','店面',835.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','淘宝',1233.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','京东',721.00);
INSERT INTO sales_data VALUES ('2019-02-01','香蕉','店面',1221.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','淘宝',2145.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','京东',1152.00);
INSERT INTO sales_data VALUES ('2019-02-01','苹果','店面',900.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','淘宝',1665.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','京东',1606.00);
INSERT INTO sales_data VALUES ('2019-02-02','桔子','店面',1070.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','淘宝',1247.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','京东',2158.00);
INSERT INTO sales_data VALUES ('2019-02-02','香蕉','店面',709.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','淘宝',1406.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','京东',1437.00);
INSERT INTO sales_data VALUES ('2019-02-02','苹果','店面',952.00);
INSERT INTO sales_data VALUES ('2019-02-03','桔子','淘宝',1701.00);
INSERT INTO sales_data VALUES ('2019-02-03','桔子','京东',1906.00);
INSERT INTO sales_data VALUES ('2019-02-03','桔子','店面',1479.00);
INSERT INTO sales_data VALUES ('2019-02-03','香蕉','淘宝',2118.00);
INSERT INTO sales_data VALUES ('2019-02-03','香蕉','京东',1221.00);
INSERT INTO sales_data VALUES ('2019-02-03','香蕉','店面',1247.00);
INSERT INTO sales_data VALUES ('2019-02-03','苹果','淘宝',1146.00);
INSERT INTO sales_data VALUES ('2019-02-03','苹果','京东',1146.00);
INSERT INTO sales_data VALUES ('2019-02-03','苹果','店面',1015.00);
INSERT INTO sales_data VALUES ('2019-02-04','桔子','淘宝',909.00);
INSERT INTO sales_data VALUES ('2019-02-04','桔子','京东',2065.00);
INSERT INTO sales_data VALUES ('2019-02-04','桔子','店面',1536.00);
INSERT INTO sales_data VALUES ('2019-02-04','香蕉','淘宝',746.00);
INSERT INTO sales_data VALUES ('2019-02-04','香蕉','京东',1234.00);
INSERT INTO sales_data VALUES ('2019-02-04','香蕉','店面',1698.00);
INSERT INTO sales_data VALUES ('2019-02-04','苹果','淘宝',926.00);
INSERT INTO sales_data VALUES ('2019-02-04','苹果','京东',1812.00);
INSERT INTO sales_data VALUES ('2019-02-04','苹果','店面',1764.00);
INSERT INTO sales_data VALUES ('2019-02-05','桔子','淘宝',1497.00);
INSERT INTO sales_data VALUES ('2019-02-05','桔子','京东',1806.00);
INSERT INTO sales_data VALUES ('2019-02-05','桔子','店面',766.00);
INSERT INTO sales_data VALUES ('2019-02-05','香蕉','淘宝',1741.00);
INSERT INTO sales_data VALUES ('2019-02-05','香蕉','京东',1311.00);
INSERT INTO sales_data VALUES ('2019-02-05','香蕉','店面',1712.00);
INSERT INTO sales_data VALUES ('2019-02-05','苹果','淘宝',2151.00);
INSERT INTO sales_data VALUES ('2019-02-05','苹果','京东',1898.00);
INSERT INTO sales_data VALUES ('2019-02-05','苹果','店面',1710.00);
INSERT INTO sales_data VALUES ('2019-02-06','桔子','淘宝',705.00);
INSERT INTO sales_data VALUES ('2019-02-06','桔子','京东',1149.00);
INSERT INTO sales_data VALUES ('2019-02-06','桔子','店面',992.00);
INSERT INTO sales_data VALUES ('2019-02-06','香蕉','淘宝',997.00);
INSERT INTO sales_data VALUES ('2019-02-06','香蕉','京东',685.00);
INSERT INTO sales_data VALUES ('2019-02-06','香蕉','店面',732.00);
INSERT INTO sales_data VALUES ('2019-02-06','苹果','淘宝',1811.00);
INSERT INTO sales_data VALUES ('2019-02-06','苹果','京东',2138.00);
INSERT INTO sales_data VALUES ('2019-02-06','苹果','店面',1288.00);
INSERT INTO sales_data VALUES ('2019-02-07','桔子','淘宝',898.00);
INSERT INTO sales_data VALUES ('2019-02-07','桔子','京东',1124.00);
INSERT INTO sales_data VALUES ('2019-02-07','桔子','店面',1775.00);
INSERT INTO sales_data VALUES ('2019-02-07','香蕉','淘宝',1248.00);
INSERT INTO sales_data VALUES ('2019-02-07','香蕉','京东',1363.00);
INSERT INTO sales_data VALUES ('2019-02-07','香蕉','店面',1669.00);
INSERT INTO sales_data VALUES ('2019-02-07','苹果','淘宝',2114.00);
INSERT INTO sales_data VALUES ('2019-02-07','苹果','京东',1439.00);
INSERT INTO sales_data VALUES ('2019-02-07','苹果','店面',733.00);
INSERT INTO sales_data VALUES ('2019-02-08','桔子','淘宝',1648.00);
INSERT INTO sales_data VALUES ('2019-02-08','桔子','京东',1700.00);
INSERT INTO sales_data VALUES ('2019-02-08','桔子','店面',1880.00);
INSERT INTO sales_data VALUES ('2019-02-08','香蕉','淘宝',1241.00);
INSERT INTO sales_data VALUES ('2019-02-08','香蕉','京东',1022.00);
INSERT INTO sales_data VALUES ('2019-02-08','香蕉','店面',1511.00);
INSERT INTO sales_data VALUES ('2019-02-08','苹果','淘宝',1332.00);
INSERT INTO sales_data VALUES ('2019-02-08','苹果','京东',2088.00);
INSERT INTO sales_data VALUES ('2019-02-08','苹果','店面',2147.00);
INSERT INTO sales_data VALUES ('2019-02-09','桔子','淘宝',874.00);
INSERT INTO sales_data VALUES ('2019-02-09','桔子','京东',2069.00);
INSERT INTO sales_data VALUES ('2019-02-09','桔子','店面',1876.00);
INSERT INTO sales_data VALUES ('2019-02-09','香蕉','淘宝',1909.00);
INSERT INTO sales_data VALUES ('2019-02-09','香蕉','京东',2094.00);
INSERT INTO sales_data VALUES ('2019-02-09','香蕉','店面',845.00);
INSERT INTO sales_data VALUES ('2019-02-09','苹果','淘宝',721.00);
INSERT INTO sales_data VALUES ('2019-02-09','苹果','京东',912.00);
INSERT INTO sales_data VALUES ('2019-02-09','苹果','店面',850.00);
INSERT INTO sales_data VALUES ('2019-02-10','桔子','淘宝',778.00);
INSERT INTO sales_data VALUES ('2019-02-10','桔子','京东',2048.00);
INSERT INTO sales_data VALUES ('2019-02-10','桔子','店面',813.00);
INSERT INTO sales_data VALUES ('2019-02-10','香蕉','淘宝',1386.00);
INSERT INTO sales_data VALUES ('2019-02-10','香蕉','京东',761.00);
INSERT INTO sales_data VALUES ('2019-02-10','香蕉','店面',1252.00);
INSERT INTO sales_data VALUES ('2019-02-10','苹果','淘宝',976.00);
INSERT INTO sales_data VALUES ('2019-02-10','苹果','京东',1324.00);
INSERT INTO sales_data VALUES ('2019-02-10','苹果','店面',1930.00);
INSERT INTO sales_data VALUES ('2019-02-11','桔子','淘宝',1965.00);
INSERT INTO sales_data VALUES ('2019-02-11','桔子','京东',1258.00);
INSERT INTO sales_data VALUES ('2019-02-11','桔子','店面',1189.00);
INSERT INTO sales_data VALUES ('2019-02-11','香蕉','淘宝',2013.00);
INSERT INTO sales_data VALUES ('2019-02-11','香蕉','京东',716.00);
INSERT INTO sales_data VALUES ('2019-02-11','香蕉','店面',2199.00);
INSERT INTO sales_data VALUES ('2019-02-11','苹果','淘宝',1703.00);
INSERT INTO sales_data VALUES ('2019-02-11','苹果','京东',1267.00);
INSERT INTO sales_data VALUES ('2019-02-11','苹果','店面',1031.00);
INSERT INTO sales_data VALUES ('2019-02-12','桔子','淘宝',1029.00);
INSERT INTO sales_data VALUES ('2019-02-12','桔子','京东',1914.00);
INSERT INTO sales_data VALUES ('2019-02-12','桔子','店面',934.00);
INSERT INTO sales_data VALUES ('2019-02-12','香蕉','淘宝',986.00);
INSERT INTO sales_data VALUES ('2019-02-12','香蕉','京东',2093.00);
INSERT INTO sales_data VALUES ('2019-02-12','香蕉','店面',808.00);
INSERT INTO sales_data VALUES ('2019-02-12','苹果','淘宝',2167.00);
INSERT INTO sales_data VALUES ('2019-02-12','苹果','京东',1807.00);
INSERT INTO sales_data VALUES ('2019-02-12','苹果','店面',2207.00);
INSERT INTO sales_data VALUES ('2019-02-13','桔子','淘宝',822.00);
INSERT INTO sales_data VALUES ('2019-02-13','桔子','京东',1838.00);
INSERT INTO sales_data VALUES ('2019-02-13','桔子','店面',929.00);
INSERT INTO sales_data VALUES ('2019-02-13','香蕉','淘宝',977.00);
INSERT INTO sales_data VALUES ('2019-02-13','香蕉','京东',1916.00);
INSERT INTO sales_data VALUES ('2019-02-13','香蕉','店面',777.00);
INSERT INTO sales_data VALUES ('2019-02-13','苹果','淘宝',1091.00);
INSERT INTO sales_data VALUES ('2019-02-13','苹果','京东',1102.00);
INSERT INTO sales_data VALUES ('2019-02-13','苹果','店面',837.00);
INSERT INTO sales_data VALUES ('2019-02-14','桔子','淘宝',1648.00);
INSERT INTO sales_data VALUES ('2019-02-14','桔子','京东',1383.00);
INSERT INTO sales_data VALUES ('2019-02-14','桔子','店面',1466.00);
INSERT INTO sales_data VALUES ('2019-02-14','香蕉','淘宝',1378.00);
INSERT INTO sales_data VALUES ('2019-02-14','香蕉','京东',1144.00);
INSERT INTO sales_data VALUES ('2019-02-14','香蕉','店面',2019.00);
INSERT INTO sales_data VALUES ('2019-02-14','苹果','淘宝',1862.00);
INSERT INTO sales_data VALUES ('2019-02-14','苹果','京东',952.00);
INSERT INTO sales_data VALUES ('2019-02-14','苹果','店面',2029.00);
INSERT INTO sales_data VALUES ('2019-02-15','桔子','淘宝',1861.00);
INSERT INTO sales_data VALUES ('2019-02-15','桔子','京东',1955.00);
INSERT INTO sales_data VALUES ('2019-02-15','桔子','店面',1096.00);
INSERT INTO sales_data VALUES ('2019-02-15','香蕉','淘宝',2187.00);
INSERT INTO sales_data VALUES ('2019-02-15','香蕉','京东',774.00);
INSERT INTO sales_data VALUES ('2019-02-15','香蕉','店面',800.00);
INSERT INTO sales_data VALUES ('2019-02-15','苹果','淘宝',911.00);
INSERT INTO sales_data VALUES ('2019-02-15','苹果','京东',1050.00);
INSERT INTO sales_data VALUES ('2019-02-15','苹果','店面',2184.00);
INSERT INTO sales_data VALUES ('2019-02-16','桔子','淘宝',1013.00);
INSERT INTO sales_data VALUES ('2019-02-16','桔子','京东',1012.00);
INSERT INTO sales_data VALUES ('2019-02-16','桔子','店面',1786.00);
INSERT INTO sales_data VALUES ('2019-02-16','香蕉','淘宝',1010.00);
INSERT INTO sales_data VALUES ('2019-02-16','香蕉','京东',1119.00);
INSERT INTO sales_data VALUES ('2019-02-16','香蕉','店面',1408.00);
INSERT INTO sales_data VALUES ('2019-02-16','苹果','淘宝',1224.00);
INSERT INTO sales_data VALUES ('2019-02-16','苹果','京东',1382.00);
INSERT INTO sales_data VALUES ('2019-02-16','苹果','店面',1109.00);
INSERT INTO sales_data VALUES ('2019-02-17','桔子','淘宝',1290.00);
INSERT INTO sales_data VALUES ('2019-02-17','桔子','京东',1762.00);
INSERT INTO sales_data VALUES ('2019-02-17','桔子','店面',1501.00);
INSERT INTO sales_data VALUES ('2019-02-17','香蕉','淘宝',1413.00);
INSERT INTO sales_data VALUES ('2019-02-17','香蕉','京东',1190.00);
INSERT INTO sales_data VALUES ('2019-02-17','香蕉','店面',2165.00);
INSERT INTO sales_data VALUES ('2019-02-17','苹果','淘宝',2159.00);
INSERT INTO sales_data VALUES ('2019-02-17','苹果','京东',1848.00);
INSERT INTO sales_data VALUES ('2019-02-17','苹果','店面',1088.00);
INSERT INTO sales_data VALUES ('2019-02-18','桔子','淘宝',1963.00);
INSERT INTO sales_data VALUES ('2019-02-18','桔子','京东',1496.00);
INSERT INTO sales_data VALUES ('2019-02-18','桔子','店面',1325.00);
INSERT INTO sales_data VALUES ('2019-02-18','香蕉','淘宝',1772.00);
INSERT INTO sales_data VALUES ('2019-02-18','香蕉','京东',1132.00);
INSERT INTO sales_data VALUES ('2019-02-18','香蕉','店面',1055.00);
INSERT INTO sales_data VALUES ('2019-02-18','苹果','淘宝',2143.00);
INSERT INTO sales_data VALUES ('2019-02-18','苹果','京东',1094.00);
INSERT INTO sales_data VALUES ('2019-02-18','苹果','店面',1104.00);
INSERT INTO sales_data VALUES ('2019-02-19','桔子','淘宝',2224.00);
INSERT INTO sales_data VALUES ('2019-02-19','桔子','京东',1285.00);
INSERT INTO sales_data VALUES ('2019-02-19','桔子','店面',1434.00);
INSERT INTO sales_data VALUES ('2019-02-19','香蕉','淘宝',2182.00);
INSERT INTO sales_data VALUES ('2019-02-19','香蕉','京东',1568.00);
INSERT INTO sales_data VALUES ('2019-02-19','香蕉','店面',1716.00);
INSERT INTO sales_data VALUES ('2019-02-19','苹果','淘宝',1738.00);
INSERT INTO sales_data VALUES ('2019-02-19','苹果','京东',1848.00);
INSERT INTO sales_data VALUES ('2019-02-19','苹果','店面',2106.00);
INSERT INTO sales_data VALUES ('2019-02-20','桔子','淘宝',921.00);
INSERT INTO sales_data VALUES ('2019-02-20','桔子','京东',847.00);
INSERT INTO sales_data VALUES ('2019-02-20','桔子','店面',1262.00);
INSERT INTO sales_data VALUES ('2019-02-20','香蕉','淘宝',1300.00);
INSERT INTO sales_data VALUES ('2019-02-20','香蕉','京东',1402.00);
INSERT INTO sales_data VALUES ('2019-02-20','香蕉','店面',789.00);
INSERT INTO sales_data VALUES ('2019-02-20','苹果','淘宝',2067.00);
INSERT INTO sales_data VALUES ('2019-02-20','苹果','京东',2080.00);
INSERT INTO sales_data VALUES ('2019-02-20','苹果','店面',1244.00);
INSERT INTO sales_data VALUES ('2019-02-21','桔子','淘宝',2002.00);
INSERT INTO sales_data VALUES ('2019-02-21','桔子','京东',2009.00);
INSERT INTO sales_data VALUES ('2019-02-21','桔子','店面',863.00);
INSERT INTO sales_data VALUES ('2019-02-21','香蕉','淘宝',855.00);
INSERT INTO sales_data VALUES ('2019-02-21','香蕉','京东',1731.00);
INSERT INTO sales_data VALUES ('2019-02-21','香蕉','店面',1618.00);
INSERT INTO sales_data VALUES ('2019-02-21','苹果','淘宝',1440.00);
INSERT INTO sales_data VALUES ('2019-02-21','苹果','京东',1263.00);
INSERT INTO sales_data VALUES ('2019-02-21','苹果','店面',2010.00);
INSERT INTO sales_data VALUES ('2019-02-22','桔子','淘宝',1761.00);
INSERT INTO sales_data VALUES ('2019-02-22','桔子','京东',1171.00);
INSERT INTO sales_data VALUES ('2019-02-22','桔子','店面',869.00);
INSERT INTO sales_data VALUES ('2019-02-22','香蕉','淘宝',2125.00);
INSERT INTO sales_data VALUES ('2019-02-22','香蕉','京东',1150.00);
INSERT INTO sales_data VALUES ('2019-02-22','香蕉','店面',1409.00);
INSERT INTO sales_data VALUES ('2019-02-22','苹果','淘宝',1314.00);
INSERT INTO sales_data VALUES ('2019-02-22','苹果','京东',1087.00);
INSERT INTO sales_data VALUES ('2019-02-22','苹果','店面',2232.00);
INSERT INTO sales_data VALUES ('2019-02-23','桔子','淘宝',790.00);
INSERT INTO sales_data VALUES ('2019-02-23','桔子','京东',2085.00);
INSERT INTO sales_data VALUES ('2019-02-23','桔子','店面',1840.00);
INSERT INTO sales_data VALUES ('2019-02-23','香蕉','淘宝',2151.00);
INSERT INTO sales_data VALUES ('2019-02-23','香蕉','京东',2257.00);
INSERT INTO sales_data VALUES ('2019-02-23','香蕉','店面',1937.00);
INSERT INTO sales_data VALUES ('2019-02-23','苹果','淘宝',1163.00);
INSERT INTO sales_data VALUES ('2019-02-23','苹果','京东',1307.00);
INSERT INTO sales_data VALUES ('2019-02-23','苹果','店面',1089.00);
INSERT INTO sales_data VALUES ('2019-02-24','桔子','淘宝',1208.00);
INSERT INTO sales_data VALUES ('2019-02-24','桔子','京东',1129.00);
INSERT INTO sales_data VALUES ('2019-02-24','桔子','店面',924.00);
INSERT INTO sales_data VALUES ('2019-02-24','香蕉','淘宝',1702.00);
INSERT INTO sales_data VALUES ('2019-02-24','香蕉','京东',875.00);
INSERT INTO sales_data VALUES ('2019-02-24','香蕉','店面',2178.00);
INSERT INTO sales_data VALUES ('2019-02-24','苹果','淘宝',1810.00);
INSERT INTO sales_data VALUES ('2019-02-24','苹果','京东',975.00);
INSERT INTO sales_data VALUES ('2019-02-24','苹果','店面',1655.00);
INSERT INTO sales_data VALUES ('2019-02-25','桔子','淘宝',1178.00);
INSERT INTO sales_data VALUES ('2019-02-25','桔子','京东',1666.00);
INSERT INTO sales_data VALUES ('2019-02-25','桔子','店面',2168.00);
INSERT INTO sales_data VALUES ('2019-02-25','香蕉','淘宝',933.00);
INSERT INTO sales_data VALUES ('2019-02-25','香蕉','京东',1166.00);
INSERT INTO sales_data VALUES ('2019-02-25','香蕉','店面',1079.00);
INSERT INTO sales_data VALUES ('2019-02-25','苹果','淘宝',1042.00);
INSERT INTO sales_data VALUES ('2019-02-25','苹果','京东',1031.00);
INSERT INTO sales_data VALUES ('2019-02-25','苹果','店面',1469.00);
INSERT INTO sales_data VALUES ('2019-02-26','桔子','淘宝',1695.00);
INSERT INTO sales_data VALUES ('2019-02-26','桔子','京东',1590.00);
INSERT INTO sales_data VALUES ('2019-02-26','桔子','店面',1802.00);
INSERT INTO sales_data VALUES ('2019-02-26','香蕉','淘宝',1667.00);
INSERT INTO sales_data VALUES ('2019-02-26','香蕉','京东',1615.00);
INSERT INTO sales_data VALUES ('2019-02-26','香蕉','店面',1622.00);
INSERT INTO sales_data VALUES ('2019-02-26','苹果','淘宝',1242.00);
INSERT INTO sales_data VALUES ('2019-02-26','苹果','京东',1501.00);
INSERT INTO sales_data VALUES ('2019-02-26','苹果','店面',1614.00);
INSERT INTO sales_data VALUES ('2019-02-27','桔子','淘宝',919.00);
INSERT INTO sales_data VALUES ('2019-02-27','桔子','京东',1904.00);
INSERT INTO sales_data VALUES ('2019-02-27','桔子','店面',2161.00);
INSERT INTO sales_data VALUES ('2019-02-27','香蕉','淘宝',1243.00);
INSERT INTO sales_data VALUES ('2019-02-27','香蕉','京东',842.00);
INSERT INTO sales_data VALUES ('2019-02-27','香蕉','店面',1019.00);
INSERT INTO sales_data VALUES ('2019-02-27','苹果','淘宝',1397.00);
INSERT INTO sales_data VALUES ('2019-02-27','苹果','京东',1774.00);
INSERT INTO sales_data VALUES ('2019-02-27','苹果','店面',1125.00);
INSERT INTO sales_data VALUES ('2019-02-28','桔子','淘宝',1310.00);
INSERT INTO sales_data VALUES ('2019-02-28','桔子','京东',1319.00);
INSERT INTO sales_data VALUES ('2019-02-28','桔子','店面',1335.00);
INSERT INTO sales_data VALUES ('2019-02-28','香蕉','淘宝',2195.00);
INSERT INTO sales_data VALUES ('2019-02-28','香蕉','京东',1721.00);
INSERT INTO sales_data VALUES ('2019-02-28','香蕉','店面',2226.00);
INSERT INTO sales_data VALUES ('2019-02-28','苹果','淘宝',2088.00);
INSERT INTO sales_data VALUES ('2019-02-28','苹果','京东',1879.00);
INSERT INTO sales_data VALUES ('2019-02-28','苹果','店面',1117.00);
INSERT INTO sales_data VALUES ('2019-03-01','桔子','淘宝',897.00);
INSERT INTO sales_data VALUES ('2019-03-01','桔子','京东',2151.00);
INSERT INTO sales_data VALUES ('2019-03-01','桔子','店面',1378.00);
INSERT INTO sales_data VALUES ('2019-03-01','香蕉','淘宝',1591.00);
INSERT INTO sales_data VALUES ('2019-03-01','香蕉','京东',1566.00);
INSERT INTO sales_data VALUES ('2019-03-01','香蕉','店面',2187.00);
INSERT INTO sales_data VALUES ('2019-03-01','苹果','淘宝',1113.00);
INSERT INTO sales_data VALUES ('2019-03-01','苹果','京东',953.00);
INSERT INTO sales_data VALUES ('2019-03-01','苹果','店面',1522.00);
INSERT INTO sales_data VALUES ('2019-03-02','桔子','淘宝',1960.00);
INSERT INTO sales_data VALUES ('2019-03-02','桔子','京东',1420.00);
INSERT INTO sales_data VALUES ('2019-03-02','桔子','店面',2248.00);
INSERT INTO sales_data VALUES ('2019-03-02','香蕉','淘宝',1294.00);
INSERT INTO sales_data VALUES ('2019-03-02','香蕉','京东',1554.00);
INSERT INTO sales_data VALUES ('2019-03-02','香蕉','店面',1868.00);
INSERT INTO sales_data VALUES ('2019-03-02','苹果','淘宝',1169.00);
INSERT INTO sales_data VALUES ('2019-03-02','苹果','京东',2012.00);
INSERT INTO sales_data VALUES ('2019-03-02','苹果','店面',1924.00);
INSERT INTO sales_data VALUES ('2019-03-03','桔子','淘宝',1409.00);
INSERT INTO sales_data VALUES ('2019-03-03','桔子','京东',1129.00);
INSERT INTO sales_data VALUES ('2019-03-03','桔子','店面',1418.00);
INSERT INTO sales_data VALUES ('2019-03-03','香蕉','淘宝',1748.00);
INSERT INTO sales_data VALUES ('2019-03-03','香蕉','京东',1649.00);
INSERT INTO sales_data VALUES ('2019-03-03','香蕉','店面',1947.00);
INSERT INTO sales_data VALUES ('2019-03-03','苹果','淘宝',2294.00);
INSERT INTO sales_data VALUES ('2019-03-03','苹果','京东',1554.00);
INSERT INTO sales_data VALUES ('2019-03-03','苹果','店面',1378.00);
INSERT INTO sales_data VALUES ('2019-03-04','桔子','淘宝',2234.00);
INSERT INTO sales_data VALUES ('2019-03-04','桔子','京东',1357.00);
INSERT INTO sales_data VALUES ('2019-03-04','桔子','店面',972.00);
INSERT INTO sales_data VALUES ('2019-03-04','香蕉','淘宝',1061.00);
INSERT INTO sales_data VALUES ('2019-03-04','香蕉','京东',1459.00);
INSERT INTO sales_data VALUES ('2019-03-04','香蕉','店面',828.00);
INSERT INTO sales_data VALUES ('2019-03-04','苹果','淘宝',1644.00);
INSERT INTO sales_data VALUES ('2019-03-04','苹果','京东',2255.00);
INSERT INTO sales_data VALUES ('2019-03-04','苹果','店面',1599.00);
INSERT INTO sales_data VALUES ('2019-03-05','桔子','淘宝',1542.00);
INSERT INTO sales_data VALUES ('2019-03-05','桔子','京东',1078.00);
INSERT INTO sales_data VALUES ('2019-03-05','桔子','店面',1762.00);
INSERT INTO sales_data VALUES ('2019-03-05','香蕉','淘宝',2269.00);
INSERT INTO sales_data VALUES ('2019-03-05','香蕉','京东',2238.00);
INSERT INTO sales_data VALUES ('2019-03-05','香蕉','店面',882.00);
INSERT INTO sales_data VALUES ('2019-03-05','苹果','淘宝',2217.00);
INSERT INTO sales_data VALUES ('2019-03-05','苹果','京东',1232.00);
INSERT INTO sales_data VALUES ('2019-03-05','苹果','店面',1636.00);
INSERT INTO sales_data VALUES ('2019-03-06','桔子','淘宝',1790.00);
INSERT INTO sales_data VALUES ('2019-03-06','桔子','京东',1606.00);
INSERT INTO sales_data VALUES ('2019-03-06','桔子','店面',1352.00);
INSERT INTO sales_data VALUES ('2019-03-06','香蕉','淘宝',1414.00);
INSERT INTO sales_data VALUES ('2019-03-06','香蕉','京东',2210.00);
INSERT INTO sales_data VALUES ('2019-03-06','香蕉','店面',1676.00);
INSERT INTO sales_data VALUES ('2019-03-06','苹果','淘宝',2028.00);
INSERT INTO sales_data VALUES ('2019-03-06','苹果','京东',1653.00);
INSERT INTO sales_data VALUES ('2019-03-06','苹果','店面',1020.00);
INSERT INTO sales_data VALUES ('2019-03-07','桔子','淘宝',1675.00);
INSERT INTO sales_data VALUES ('2019-03-07','桔子','京东',1647.00);
INSERT INTO sales_data VALUES ('2019-03-07','桔子','店面',1775.00);
INSERT INTO sales_data VALUES ('2019-03-07','香蕉','淘宝',2248.00);
INSERT INTO sales_data VALUES ('2019-03-07','香蕉','京东',1571.00);
INSERT INTO sales_data VALUES ('2019-03-07','香蕉','店面',2321.00);
INSERT INTO sales_data VALUES ('2019-03-07','苹果','淘宝',910.00);
INSERT INTO sales_data VALUES ('2019-03-07','苹果','京东',1822.00);
INSERT INTO sales_data VALUES ('2019-03-07','苹果','店面',1470.00);
INSERT INTO sales_data VALUES ('2019-03-08','桔子','淘宝',933.00);
INSERT INTO sales_data VALUES ('2019-03-08','桔子','京东',1161.00);
INSERT INTO sales_data VALUES ('2019-03-08','桔子','店面',1420.00);
INSERT INTO sales_data VALUES ('2019-03-08','香蕉','淘宝',1722.00);
INSERT INTO sales_data VALUES ('2019-03-08','香蕉','京东',1888.00);
INSERT INTO sales_data VALUES ('2019-03-08','香蕉','店面',1683.00);
INSERT INTO sales_data VALUES ('2019-03-08','苹果','淘宝',1169.00);
INSERT INTO sales_data VALUES ('2019-03-08','苹果','京东',1842.00);
INSERT INTO sales_data VALUES ('2019-03-08','苹果','店面',1606.00);
INSERT INTO sales_data VALUES ('2019-03-09','桔子','淘宝',1241.00);
INSERT INTO sales_data VALUES ('2019-03-09','桔子','京东',1749.00);
INSERT INTO sales_data VALUES ('2019-03-09','桔子','店面',2028.00);
INSERT INTO sales_data VALUES ('2019-03-09','香蕉','淘宝',2061.00);
INSERT INTO sales_data VALUES ('2019-03-09','香蕉','京东',1219.00);
INSERT INTO sales_data VALUES ('2019-03-09','香蕉','店面',1314.00);
INSERT INTO sales_data VALUES ('2019-03-09','苹果','淘宝',1094.00);
INSERT INTO sales_data VALUES ('2019-03-09','苹果','京东',1813.00);
INSERT INTO sales_data VALUES ('2019-03-09','苹果','店面',1203.00);
INSERT INTO sales_data VALUES ('2019-03-10','桔子','淘宝',1955.00);
INSERT INTO sales_data VALUES ('2019-03-10','桔子','京东',1526.00);
INSERT INTO sales_data VALUES ('2019-03-10','桔子','店面',2041.00);
INSERT INTO sales_data VALUES ('2019-03-10','香蕉','淘宝',2155.00);
INSERT INTO sales_data VALUES ('2019-03-10','香蕉','京东',875.00);
INSERT INTO sales_data VALUES ('2019-03-10','香蕉','店面',1363.00);
INSERT INTO sales_data VALUES ('2019-03-10','苹果','淘宝',1605.00);
INSERT INTO sales_data VALUES ('2019-03-10','苹果','京东',2298.00);
INSERT INTO sales_data VALUES ('2019-03-10','苹果','店面',2109.00);
INSERT INTO sales_data VALUES ('2019-03-11','桔子','淘宝',1606.00);
INSERT INTO sales_data VALUES ('2019-03-11','桔子','京东',888.00);
INSERT INTO sales_data VALUES ('2019-03-11','桔子','店面',1611.00);
INSERT INTO sales_data VALUES ('2019-03-11','香蕉','淘宝',2251.00);
INSERT INTO sales_data VALUES ('2019-03-11','香蕉','京东',991.00);
INSERT INTO sales_data VALUES ('2019-03-11','香蕉','店面',1942.00);
INSERT INTO sales_data VALUES ('2019-03-11','苹果','淘宝',1341.00);
INSERT INTO sales_data VALUES ('2019-03-11','苹果','京东',1883.00);
INSERT INTO sales_data VALUES ('2019-03-11','苹果','店面',1500.00);
INSERT INTO sales_data VALUES ('2019-03-12','桔子','淘宝',2199.00);
INSERT INTO sales_data VALUES ('2019-03-12','桔子','京东',2226.00);
INSERT INTO sales_data VALUES ('2019-03-12','桔子','店面',1017.00);
INSERT INTO sales_data VALUES ('2019-03-12','香蕉','淘宝',1476.00);
INSERT INTO sales_data VALUES ('2019-03-12','香蕉','京东',1132.00);
INSERT INTO sales_data VALUES ('2019-03-12','香蕉','店面',1931.00);
INSERT INTO sales_data VALUES ('2019-03-12','苹果','淘宝',1168.00);
INSERT INTO sales_data VALUES ('2019-03-12','苹果','京东',858.00);
INSERT INTO sales_data VALUES ('2019-03-12','苹果','店面',2314.00);
INSERT INTO sales_data VALUES ('2019-03-13','桔子','淘宝',1652.00);
INSERT INTO sales_data VALUES ('2019-03-13','桔子','京东',1122.00);
INSERT INTO sales_data VALUES ('2019-03-13','桔子','店面',1797.00);
INSERT INTO sales_data VALUES ('2019-03-13','香蕉','淘宝',2020.00);
INSERT INTO sales_data VALUES ('2019-03-13','香蕉','京东',2237.00);
INSERT INTO sales_data VALUES ('2019-03-13','香蕉','店面',983.00);
INSERT INTO sales_data VALUES ('2019-03-13','苹果','淘宝',1721.00);
INSERT INTO sales_data VALUES ('2019-03-13','苹果','京东',2053.00);
INSERT INTO sales_data VALUES ('2019-03-13','苹果','店面',1018.00);
INSERT INTO sales_data VALUES ('2019-03-14','桔子','淘宝',2249.00);
INSERT INTO sales_data VALUES ('2019-03-14','桔子','京东',1323.00);
INSERT INTO sales_data VALUES ('2019-03-14','桔子','店面',982.00);
INSERT INTO sales_data VALUES ('2019-03-14','香蕉','淘宝',2018.00);
INSERT INTO sales_data VALUES ('2019-03-14','香蕉','京东',2084.00);
INSERT INTO sales_data VALUES ('2019-03-14','香蕉','店面',1025.00);
INSERT INTO sales_data VALUES ('2019-03-14','苹果','淘宝',1284.00);
INSERT INTO sales_data VALUES ('2019-03-14','苹果','京东',1990.00);
INSERT INTO sales_data VALUES ('2019-03-14','苹果','店面',1171.00);
INSERT INTO sales_data VALUES ('2019-03-15','桔子','淘宝',886.00);
INSERT INTO sales_data VALUES ('2019-03-15','桔子','京东',992.00);
INSERT INTO sales_data VALUES ('2019-03-15','桔子','店面',2214.00);
INSERT INTO sales_data VALUES ('2019-03-15','香蕉','淘宝',1541.00);
INSERT INTO sales_data VALUES ('2019-03-15','香蕉','京东',2341.00);
INSERT INTO sales_data VALUES ('2019-03-15','香蕉','店面',2090.00);
INSERT INTO sales_data VALUES ('2019-03-15','苹果','淘宝',1708.00);
INSERT INTO sales_data VALUES ('2019-03-15','苹果','京东',1467.00);
INSERT INTO sales_data VALUES ('2019-03-15','苹果','店面',872.00);
INSERT INTO sales_data VALUES ('2019-03-16','桔子','淘宝',1293.00);
INSERT INTO sales_data VALUES ('2019-03-16','桔子','京东',1790.00);
INSERT INTO sales_data VALUES ('2019-03-16','桔子','店面',885.00);
INSERT INTO sales_data VALUES ('2019-03-16','香蕉','淘宝',1258.00);
INSERT INTO sales_data VALUES ('2019-03-16','香蕉','京东',1087.00);
INSERT INTO sales_data VALUES ('2019-03-16','香蕉','店面',1153.00);
INSERT INTO sales_data VALUES ('2019-03-16','苹果','淘宝',2200.00);
INSERT INTO sales_data VALUES ('2019-03-16','苹果','京东',2252.00);
INSERT INTO sales_data VALUES ('2019-03-16','苹果','店面',1035.00);
INSERT INTO sales_data VALUES ('2019-03-17','桔子','淘宝',2333.00);
INSERT INTO sales_data VALUES ('2019-03-17','桔子','京东',1624.00);
INSERT INTO sales_data VALUES ('2019-03-17','桔子','店面',2238.00);
INSERT INTO sales_data VALUES ('2019-03-17','香蕉','淘宝',996.00);
INSERT INTO sales_data VALUES ('2019-03-17','香蕉','京东',1513.00);
INSERT INTO sales_data VALUES ('2019-03-17','香蕉','店面',1200.00);
INSERT INTO sales_data VALUES ('2019-03-17','苹果','淘宝',1118.00);
INSERT INTO sales_data VALUES ('2019-03-17','苹果','京东',1171.00);
INSERT INTO sales_data VALUES ('2019-03-17','苹果','店面',924.00);
INSERT INTO sales_data VALUES ('2019-03-18','桔子','淘宝',1288.00);
INSERT INTO sales_data VALUES ('2019-03-18','桔子','京东',1600.00);
INSERT INTO sales_data VALUES ('2019-03-18','桔子','店面',2059.00);
INSERT INTO sales_data VALUES ('2019-03-18','香蕉','淘宝',1599.00);
INSERT INTO sales_data VALUES ('2019-03-18','香蕉','京东',1622.00);
INSERT INTO sales_data VALUES ('2019-03-18','香蕉','店面',2186.00);
INSERT INTO sales_data VALUES ('2019-03-18','苹果','淘宝',1448.00);
INSERT INTO sales_data VALUES ('2019-03-18','苹果','京东',2298.00);
INSERT INTO sales_data VALUES ('2019-03-18','苹果','店面',2162.00);
INSERT INTO sales_data VALUES ('2019-03-19','桔子','淘宝',1178.00);
INSERT INTO sales_data VALUES ('2019-03-19','桔子','京东',1646.00);
INSERT INTO sales_data VALUES ('2019-03-19','桔子','店面',1268.00);
INSERT INTO sales_data VALUES ('2019-03-19','香蕉','淘宝',1185.00);
INSERT INTO sales_data VALUES ('2019-03-19','香蕉','京东',2069.00);
INSERT INTO sales_data VALUES ('2019-03-19','香蕉','店面',2188.00);
INSERT INTO sales_data VALUES ('2019-03-19','苹果','淘宝',1200.00);
INSERT INTO sales_data VALUES ('2019-03-19','苹果','京东',957.00);
INSERT INTO sales_data VALUES ('2019-03-19','苹果','店面',905.00);
INSERT INTO sales_data VALUES ('2019-03-20','桔子','淘宝',1488.00);
INSERT INTO sales_data VALUES ('2019-03-20','桔子','京东',2292.00);
INSERT INTO sales_data VALUES ('2019-03-20','桔子','店面',2292.00);
INSERT INTO sales_data VALUES ('2019-03-20','香蕉','淘宝',1653.00);
INSERT INTO sales_data VALUES ('2019-03-20','香蕉','京东',2250.00);
INSERT INTO sales_data VALUES ('2019-03-20','香蕉','店面',1540.00);
INSERT INTO sales_data VALUES ('2019-03-20','苹果','淘宝',1516.00);
INSERT INTO sales_data VALUES ('2019-03-20','苹果','京东',2371.00);
INSERT INTO sales_data VALUES ('2019-03-20','苹果','店面',2178.00);
INSERT INTO sales_data VALUES ('2019-03-21','桔子','淘宝',1846.00);
INSERT INTO sales_data VALUES ('2019-03-21','桔子','京东',1119.00);
INSERT INTO sales_data VALUES ('2019-03-21','桔子','店面',980.00);
INSERT INTO sales_data VALUES ('2019-03-21','香蕉','淘宝',1895.00);
INSERT INTO sales_data VALUES ('2019-03-21','香蕉','京东',1527.00);
INSERT INTO sales_data VALUES ('2019-03-21','香蕉','店面',1700.00);
INSERT INTO sales_data VALUES ('2019-03-21','苹果','淘宝',1574.00);
INSERT INTO sales_data VALUES ('2019-03-21','苹果','京东',2246.00);
INSERT INTO sales_data VALUES ('2019-03-21','苹果','店面',942.00);
INSERT INTO sales_data VALUES ('2019-03-22','桔子','淘宝',1384.00);
INSERT INTO sales_data VALUES ('2019-03-22','桔子','京东',1319.00);
INSERT INTO sales_data VALUES ('2019-03-22','桔子','店面',2365.00);
INSERT INTO sales_data VALUES ('2019-03-22','香蕉','淘宝',1166.00);
INSERT INTO sales_data VALUES ('2019-03-22','香蕉','京东',1612.00);
INSERT INTO sales_data VALUES ('2019-03-22','香蕉','店面',1626.00);
INSERT INTO sales_data VALUES ('2019-03-22','苹果','淘宝',1549.00);
INSERT INTO sales_data VALUES ('2019-03-22','苹果','京东',1912.00);
INSERT INTO sales_data VALUES ('2019-03-22','苹果','店面',1311.00);
INSERT INTO sales_data VALUES ('2019-03-23','桔子','淘宝',1357.00);
INSERT INTO sales_data VALUES ('2019-03-23','桔子','京东',2232.00);
INSERT INTO sales_data VALUES ('2019-03-23','桔子','店面',1388.00);
INSERT INTO sales_data VALUES ('2019-03-23','香蕉','淘宝',1377.00);
INSERT INTO sales_data VALUES ('2019-03-23','香蕉','京东',1330.00);
INSERT INTO sales_data VALUES ('2019-03-23','香蕉','店面',1290.00);
INSERT INTO sales_data VALUES ('2019-03-23','苹果','淘宝',1279.00);
INSERT INTO sales_data VALUES ('2019-03-23','苹果','京东',2093.00);
INSERT INTO sales_data VALUES ('2019-03-23','苹果','店面',1150.00);
INSERT INTO sales_data VALUES ('2019-03-24','桔子','淘宝',1935.00);
INSERT INTO sales_data VALUES ('2019-03-24','桔子','京东',1224.00);
INSERT INTO sales_data VALUES ('2019-03-24','桔子','店面',1136.00);
INSERT INTO sales_data VALUES ('2019-03-24','香蕉','淘宝',1723.00);
INSERT INTO sales_data VALUES ('2019-03-24','香蕉','京东',2174.00);
INSERT INTO sales_data VALUES ('2019-03-24','香蕉','店面',1360.00);
INSERT INTO sales_data VALUES ('2019-03-24','苹果','淘宝',1808.00);
INSERT INTO sales_data VALUES ('2019-03-24','苹果','京东',1674.00);
INSERT INTO sales_data VALUES ('2019-03-24','苹果','店面',1992.00);
INSERT INTO sales_data VALUES ('2019-03-25','桔子','淘宝',1118.00);
INSERT INTO sales_data VALUES ('2019-03-25','桔子','京东',2358.00);
INSERT INTO sales_data VALUES ('2019-03-25','桔子','店面',1848.00);
INSERT INTO sales_data VALUES ('2019-03-25','香蕉','淘宝',1165.00);
INSERT INTO sales_data VALUES ('2019-03-25','香蕉','京东',1342.00);
INSERT INTO sales_data VALUES ('2019-03-25','香蕉','店面',2266.00);
INSERT INTO sales_data VALUES ('2019-03-25','苹果','淘宝',1130.00);
INSERT INTO sales_data VALUES ('2019-03-25','苹果','京东',1608.00);
INSERT INTO sales_data VALUES ('2019-03-25','苹果','店面',1478.00);
INSERT INTO sales_data VALUES ('2019-03-26','桔子','淘宝',1862.00);
INSERT INTO sales_data VALUES ('2019-03-26','桔子','京东',2262.00);
INSERT INTO sales_data VALUES ('2019-03-26','桔子','店面',995.00);
INSERT INTO sales_data VALUES ('2019-03-26','香蕉','淘宝',2273.00);
INSERT INTO sales_data VALUES ('2019-03-26','香蕉','京东',1214.00);
INSERT INTO sales_data VALUES ('2019-03-26','香蕉','店面',2322.00);
INSERT INTO sales_data VALUES ('2019-03-26','苹果','淘宝',1256.00);
INSERT INTO sales_data VALUES ('2019-03-26','苹果','京东',1687.00);
INSERT INTO sales_data VALUES ('2019-03-26','苹果','店面',1247.00);
INSERT INTO sales_data VALUES ('2019-03-27','桔子','淘宝',1646.00);
INSERT INTO sales_data VALUES ('2019-03-27','桔子','京东',2066.00);
INSERT INTO sales_data VALUES ('2019-03-27','桔子','店面',940.00);
INSERT INTO sales_data VALUES ('2019-03-27','香蕉','淘宝',1891.00);
INSERT INTO sales_data VALUES ('2019-03-27','香蕉','京东',1590.00);
INSERT INTO sales_data VALUES ('2019-03-27','香蕉','店面',1254.00);
INSERT INTO sales_data VALUES ('2019-03-27','苹果','淘宝',2117.00);
INSERT INTO sales_data VALUES ('2019-03-27','苹果','京东',2403.00);
INSERT INTO sales_data VALUES ('2019-03-27','苹果','店面',1018.00);
INSERT INTO sales_data VALUES ('2019-03-28','桔子','淘宝',1072.00);
INSERT INTO sales_data VALUES ('2019-03-28','桔子','京东',1806.00);
INSERT INTO sales_data VALUES ('2019-03-28','桔子','店面',1787.00);
INSERT INTO sales_data VALUES ('2019-03-28','香蕉','淘宝',2154.00);
INSERT INTO sales_data VALUES ('2019-03-28','香蕉','京东',2009.00);
INSERT INTO sales_data VALUES ('2019-03-28','香蕉','店面',1730.00);
INSERT INTO sales_data VALUES ('2019-03-28','苹果','淘宝',1586.00);
INSERT INTO sales_data VALUES ('2019-03-28','苹果','京东',2259.00);
INSERT INTO sales_data VALUES ('2019-03-28','苹果','店面',2157.00);
INSERT INTO sales_data VALUES ('2019-03-29','桔子','淘宝',1443.00);
INSERT INTO sales_data VALUES ('2019-03-29','桔子','京东',979.00);
INSERT INTO sales_data VALUES ('2019-03-29','桔子','店面',1355.00);
INSERT INTO sales_data VALUES ('2019-03-29','香蕉','淘宝',2006.00);
INSERT INTO sales_data VALUES ('2019-03-29','香蕉','京东',1921.00);
INSERT INTO sales_data VALUES ('2019-03-29','香蕉','店面',1197.00);
INSERT INTO sales_data VALUES ('2019-03-29','苹果','淘宝',2081.00);
INSERT INTO sales_data VALUES ('2019-03-29','苹果','京东',1773.00);
INSERT INTO sales_data VALUES ('2019-03-29','苹果','店面',1492.00);
INSERT INTO sales_data VALUES ('2019-03-30','桔子','淘宝',1987.00);
INSERT INTO sales_data VALUES ('2019-03-30','桔子','京东',2114.00);
INSERT INTO sales_data VALUES ('2019-03-30','桔子','店面',2263.00);
INSERT INTO sales_data VALUES ('2019-03-30','香蕉','淘宝',2314.00);
INSERT INTO sales_data VALUES ('2019-03-30','香蕉','京东',1335.00);
INSERT INTO sales_data VALUES ('2019-03-30','香蕉','店面',1904.00);
INSERT INTO sales_data VALUES ('2019-03-30','苹果','淘宝',2329.00);
INSERT INTO sales_data VALUES ('2019-03-30','苹果','京东',2300.00);
INSERT INTO sales_data VALUES ('2019-03-30','苹果','店面',1069.00);
INSERT INTO sales_data VALUES ('2019-03-31','桔子','淘宝',1163.00);
INSERT INTO sales_data VALUES ('2019-03-31','桔子','京东',1997.00);
INSERT INTO sales_data VALUES ('2019-03-31','桔子','店面',1052.00);
INSERT INTO sales_data VALUES ('2019-03-31','香蕉','淘宝',1256.00);
INSERT INTO sales_data VALUES ('2019-03-31','香蕉','京东',2139.00);
INSERT INTO sales_data VALUES ('2019-03-31','香蕉','店面',1928.00);
INSERT INTO sales_data VALUES ('2019-03-31','苹果','淘宝',2113.00);
INSERT INTO sales_data VALUES ('2019-03-31','苹果','京东',1863.00);
INSERT INTO sales_data VALUES ('2019-03-31','苹果','店面',1507.00);
INSERT INTO sales_data VALUES ('2019-04-01','桔子','淘宝',1418.00);
INSERT INTO sales_data VALUES ('2019-04-01','桔子','京东',1024.00);
INSERT INTO sales_data VALUES ('2019-04-01','桔子','店面',1341.00);
INSERT INTO sales_data VALUES ('2019-04-01','香蕉','淘宝',1145.00);
INSERT INTO sales_data VALUES ('2019-04-01','香蕉','京东',1532.00);
INSERT INTO sales_data VALUES ('2019-04-01','香蕉','店面',1385.00);
INSERT INTO sales_data VALUES ('2019-04-01','苹果','淘宝',1565.00);
INSERT INTO sales_data VALUES ('2019-04-01','苹果','京东',1103.00);
INSERT INTO sales_data VALUES ('2019-04-01','苹果','店面',2371.00);
INSERT INTO sales_data VALUES ('2019-04-02','桔子','淘宝',1832.00);
INSERT INTO sales_data VALUES ('2019-04-02','桔子','京东',2253.00);
INSERT INTO sales_data VALUES ('2019-04-02','桔子','店面',1714.00);
INSERT INTO sales_data VALUES ('2019-04-02','香蕉','淘宝',2389.00);
INSERT INTO sales_data VALUES ('2019-04-02','香蕉','京东',1801.00);
INSERT INTO sales_data VALUES ('2019-04-02','香蕉','店面',1388.00);
INSERT INTO sales_data VALUES ('2019-04-02','苹果','淘宝',2212.00);
INSERT INTO sales_data VALUES ('2019-04-02','苹果','京东',1675.00);
INSERT INTO sales_data VALUES ('2019-04-02','苹果','店面',1783.00);
INSERT INTO sales_data VALUES ('2019-04-03','桔子','淘宝',1681.00);
INSERT INTO sales_data VALUES ('2019-04-03','桔子','京东',1570.00);
INSERT INTO sales_data VALUES ('2019-04-03','桔子','店面',1648.00);
INSERT INTO sales_data VALUES ('2019-04-03','香蕉','淘宝',1810.00);
INSERT INTO sales_data VALUES ('2019-04-03','香蕉','京东',1787.00);
INSERT INTO sales_data VALUES ('2019-04-03','香蕉','店面',1200.00);
INSERT INTO sales_data VALUES ('2019-04-03','苹果','淘宝',1917.00);
INSERT INTO sales_data VALUES ('2019-04-03','苹果','京东',2098.00);
INSERT INTO sales_data VALUES ('2019-04-03','苹果','店面',2394.00);
INSERT INTO sales_data VALUES ('2019-04-04','桔子','淘宝',1406.00);
INSERT INTO sales_data VALUES ('2019-04-04','桔子','京东',1771.00);
INSERT INTO sales_data VALUES ('2019-04-04','桔子','店面',1816.00);
INSERT INTO sales_data VALUES ('2019-04-04','香蕉','淘宝',1968.00);
INSERT INTO sales_data VALUES ('2019-04-04','香蕉','京东',2239.00);
INSERT INTO sales_data VALUES ('2019-04-04','香蕉','店面',1890.00);
INSERT INTO sales_data VALUES ('2019-04-04','苹果','淘宝',2359.00);
INSERT INTO sales_data VALUES ('2019-04-04','苹果','京东',2434.00);
INSERT INTO sales_data VALUES ('2019-04-04','苹果','店面',972.00);
INSERT INTO sales_data VALUES ('2019-04-05','桔子','淘宝',1299.00);
INSERT INTO sales_data VALUES ('2019-04-05','桔子','京东',1554.00);
INSERT INTO sales_data VALUES ('2019-04-05','桔子','店面',1130.00);
INSERT INTO sales_data VALUES ('2019-04-05','香蕉','淘宝',1220.00);
INSERT INTO sales_data VALUES ('2019-04-05','香蕉','京东',2432.00);
INSERT INTO sales_data VALUES ('2019-04-05','香蕉','店面',2428.00);
INSERT INTO sales_data VALUES ('2019-04-05','苹果','淘宝',1979.00);
INSERT INTO sales_data VALUES ('2019-04-05','苹果','京东',2366.00);
INSERT INTO sales_data VALUES ('2019-04-05','苹果','店面',1774.00);
INSERT INTO sales_data VALUES ('2019-04-06','桔子','淘宝',2417.00);
INSERT INTO sales_data VALUES ('2019-04-06','桔子','京东',2128.00);
INSERT INTO sales_data VALUES ('2019-04-06','桔子','店面',1000.00);
INSERT INTO sales_data VALUES ('2019-04-06','香蕉','淘宝',1744.00);
INSERT INTO sales_data VALUES ('2019-04-06','香蕉','京东',1349.00);
INSERT INTO sales_data VALUES ('2019-04-06','香蕉','店面',1609.00);
INSERT INTO sales_data VALUES ('2019-04-06','苹果','淘宝',2432.00);
INSERT INTO sales_data VALUES ('2019-04-06','苹果','京东',2199.00);
INSERT INTO sales_data VALUES ('2019-04-06','苹果','店面',2437.00);
INSERT INTO sales_data VALUES ('2019-04-07','桔子','淘宝',1177.00);
INSERT INTO sales_data VALUES ('2019-04-07','桔子','京东',1662.00);
INSERT INTO sales_data VALUES ('2019-04-07','桔子','店面',2080.00);
INSERT INTO sales_data VALUES ('2019-04-07','香蕉','淘宝',1111.00);
INSERT INTO sales_data VALUES ('2019-04-07','香蕉','京东',2102.00);
INSERT INTO sales_data VALUES ('2019-04-07','香蕉','店面',1386.00);
INSERT INTO sales_data VALUES ('2019-04-07','苹果','淘宝',1962.00);
INSERT INTO sales_data VALUES ('2019-04-07','苹果','京东',1605.00);
INSERT INTO sales_data VALUES ('2019-04-07','苹果','店面',1160.00);
INSERT INTO sales_data VALUES ('2019-04-08','桔子','淘宝',1392.00);
INSERT INTO sales_data VALUES ('2019-04-08','桔子','京东',1504.00);
INSERT INTO sales_data VALUES ('2019-04-08','桔子','店面',1134.00);
INSERT INTO sales_data VALUES ('2019-04-08','香蕉','淘宝',1399.00);
INSERT INTO sales_data VALUES ('2019-04-08','香蕉','京东',1832.00);
INSERT INTO sales_data VALUES ('2019-04-08','香蕉','店面',1718.00);
INSERT INTO sales_data VALUES ('2019-04-08','苹果','淘宝',1559.00);
INSERT INTO sales_data VALUES ('2019-04-08','苹果','京东',2082.00);
INSERT INTO sales_data VALUES ('2019-04-08','苹果','店面',1680.00);
INSERT INTO sales_data VALUES ('2019-04-09','桔子','淘宝',1522.00);
INSERT INTO sales_data VALUES ('2019-04-09','桔子','京东',1596.00);
INSERT INTO sales_data VALUES ('2019-04-09','桔子','店面',1581.00);
INSERT INTO sales_data VALUES ('2019-04-09','香蕉','淘宝',2326.00);
INSERT INTO sales_data VALUES ('2019-04-09','香蕉','京东',1537.00);
INSERT INTO sales_data VALUES ('2019-04-09','香蕉','店面',1234.00);
INSERT INTO sales_data VALUES ('2019-04-09','苹果','淘宝',2351.00);
INSERT INTO sales_data VALUES ('2019-04-09','苹果','京东',2307.00);
INSERT INTO sales_data VALUES ('2019-04-09','苹果','店面',1608.00);
INSERT INTO sales_data VALUES ('2019-04-10','桔子','淘宝',1490.00);
INSERT INTO sales_data VALUES ('2019-04-10','桔子','京东',2269.00);
INSERT INTO sales_data VALUES ('2019-04-10','桔子','店面',1337.00);
INSERT INTO sales_data VALUES ('2019-04-10','香蕉','淘宝',1451.00);
INSERT INTO sales_data VALUES ('2019-04-10','香蕉','京东',2466.00);
INSERT INTO sales_data VALUES ('2019-04-10','香蕉','店面',2019.00);
INSERT INTO sales_data VALUES ('2019-04-10','苹果','淘宝',1051.00);
INSERT INTO sales_data VALUES ('2019-04-10','苹果','京东',1096.00);
INSERT INTO sales_data VALUES ('2019-04-10','苹果','店面',1642.00);
INSERT INTO sales_data VALUES ('2019-04-11','桔子','淘宝',1462.00);
INSERT INTO sales_data VALUES ('2019-04-11','桔子','京东',2083.00);
INSERT INTO sales_data VALUES ('2019-04-11','桔子','店面',2272.00);
INSERT INTO sales_data VALUES ('2019-04-11','香蕉','淘宝',1642.00);
INSERT INTO sales_data VALUES ('2019-04-11','香蕉','京东',2490.00);
INSERT INTO sales_data VALUES ('2019-04-11','香蕉','店面',1290.00);
INSERT INTO sales_data VALUES ('2019-04-11','苹果','淘宝',1792.00);
INSERT INTO sales_data VALUES ('2019-04-11','苹果','京东',1404.00);
INSERT INTO sales_data VALUES ('2019-04-11','苹果','店面',2138.00);
INSERT INTO sales_data VALUES ('2019-04-12','桔子','淘宝',1030.00);
INSERT INTO sales_data VALUES ('2019-04-12','桔子','京东',1983.00);
INSERT INTO sales_data VALUES ('2019-04-12','桔子','店面',1740.00);
INSERT INTO sales_data VALUES ('2019-04-12','香蕉','淘宝',1725.00);
INSERT INTO sales_data VALUES ('2019-04-12','香蕉','京东',1015.00);
INSERT INTO sales_data VALUES ('2019-04-12','香蕉','店面',2346.00);
INSERT INTO sales_data VALUES ('2019-04-12','苹果','淘宝',2316.00);
INSERT INTO sales_data VALUES ('2019-04-12','苹果','京东',2351.00);
INSERT INTO sales_data VALUES ('2019-04-12','苹果','店面',1393.00);
INSERT INTO sales_data VALUES ('2019-04-13','桔子','淘宝',1065.00);
INSERT INTO sales_data VALUES ('2019-04-13','桔子','京东',2216.00);
INSERT INTO sales_data VALUES ('2019-04-13','桔子','店面',1215.00);
INSERT INTO sales_data VALUES ('2019-04-13','香蕉','淘宝',1683.00);
INSERT INTO sales_data VALUES ('2019-04-13','香蕉','京东',1211.00);
INSERT INTO sales_data VALUES ('2019-04-13','香蕉','店面',2489.00);
INSERT INTO sales_data VALUES ('2019-04-13','苹果','淘宝',2025.00);
INSERT INTO sales_data VALUES ('2019-04-13','苹果','京东',1667.00);
INSERT INTO sales_data VALUES ('2019-04-13','苹果','店面',2460.00);
INSERT INTO sales_data VALUES ('2019-04-14','桔子','淘宝',1555.00);
INSERT INTO sales_data VALUES ('2019-04-14','桔子','京东',1728.00);
INSERT INTO sales_data VALUES ('2019-04-14','桔子','店面',1066.00);
INSERT INTO sales_data VALUES ('2019-04-14','香蕉','淘宝',2201.00);
INSERT INTO sales_data VALUES ('2019-04-14','香蕉','京东',2191.00);
INSERT INTO sales_data VALUES ('2019-04-14','香蕉','店面',2149.00);
INSERT INTO sales_data VALUES ('2019-04-14','苹果','淘宝',1973.00);
INSERT INTO sales_data VALUES ('2019-04-14','苹果','京东',1333.00);
INSERT INTO sales_data VALUES ('2019-04-14','苹果','店面',2140.00);
INSERT INTO sales_data VALUES ('2019-04-15','桔子','淘宝',2268.00);
INSERT INTO sales_data VALUES ('2019-04-15','桔子','京东',2130.00);
INSERT INTO sales_data VALUES ('2019-04-15','桔子','店面',1048.00);
INSERT INTO sales_data VALUES ('2019-04-15','香蕉','淘宝',1906.00);
INSERT INTO sales_data VALUES ('2019-04-15','香蕉','京东',2155.00);
INSERT INTO sales_data VALUES ('2019-04-15','香蕉','店面',2026.00);
INSERT INTO sales_data VALUES ('2019-04-15','苹果','淘宝',1141.00);
INSERT INTO sales_data VALUES ('2019-04-15','苹果','京东',1375.00);
INSERT INTO sales_data VALUES ('2019-04-15','苹果','店面',2036.00);
INSERT INTO sales_data VALUES ('2019-04-16','桔子','淘宝',2486.00);
INSERT INTO sales_data VALUES ('2019-04-16','桔子','京东',1191.00);
INSERT INTO sales_data VALUES ('2019-04-16','桔子','店面',1886.00);
INSERT INTO sales_data VALUES ('2019-04-16','香蕉','淘宝',1374.00);
INSERT INTO sales_data VALUES ('2019-04-16','香蕉','京东',1246.00);
INSERT INTO sales_data VALUES ('2019-04-16','香蕉','店面',1593.00);
INSERT INTO sales_data VALUES ('2019-04-16','苹果','淘宝',1579.00);
INSERT INTO sales_data VALUES ('2019-04-16','苹果','京东',1919.00);
INSERT INTO sales_data VALUES ('2019-04-16','苹果','店面',1794.00);
INSERT INTO sales_data VALUES ('2019-04-17','桔子','淘宝',1563.00);
INSERT INTO sales_data VALUES ('2019-04-17','桔子','京东',1439.00);
INSERT INTO sales_data VALUES ('2019-04-17','桔子','店面',2456.00);
INSERT INTO sales_data VALUES ('2019-04-17','香蕉','淘宝',1513.00);
INSERT INTO sales_data VALUES ('2019-04-17','香蕉','京东',1978.00);
INSERT INTO sales_data VALUES ('2019-04-17','香蕉','店面',1669.00);
INSERT INTO sales_data VALUES ('2019-04-17','苹果','淘宝',1564.00);
INSERT INTO sales_data VALUES ('2019-04-17','苹果','京东',1665.00);
INSERT INTO sales_data VALUES ('2019-04-17','苹果','店面',1345.00);
INSERT INTO sales_data VALUES ('2019-04-18','桔子','淘宝',1204.00);
INSERT INTO sales_data VALUES ('2019-04-18','桔子','京东',1127.00);
INSERT INTO sales_data VALUES ('2019-04-18','桔子','店面',1668.00);
INSERT INTO sales_data VALUES ('2019-04-18','香蕉','淘宝',2328.00);
INSERT INTO sales_data VALUES ('2019-04-18','香蕉','京东',2376.00);
INSERT INTO sales_data VALUES ('2019-04-18','香蕉','店面',1278.00);
INSERT INTO sales_data VALUES ('2019-04-18','苹果','淘宝',2357.00);
INSERT INTO sales_data VALUES ('2019-04-18','苹果','京东',1762.00);
INSERT INTO sales_data VALUES ('2019-04-18','苹果','店面',2413.00);
INSERT INTO sales_data VALUES ('2019-04-19','桔子','淘宝',1868.00);
INSERT INTO sales_data VALUES ('2019-04-19','桔子','京东',1888.00);
INSERT INTO sales_data VALUES ('2019-04-19','桔子','店面',1272.00);
INSERT INTO sales_data VALUES ('2019-04-19','香蕉','淘宝',1384.00);
INSERT INTO sales_data VALUES ('2019-04-19','香蕉','京东',1849.00);
INSERT INTO sales_data VALUES ('2019-04-19','香蕉','店面',1438.00);
INSERT INTO sales_data VALUES ('2019-04-19','苹果','淘宝',2245.00);
INSERT INTO sales_data VALUES ('2019-04-19','苹果','京东',2198.00);
INSERT INTO sales_data VALUES ('2019-04-19','苹果','店面',1659.00);
INSERT INTO sales_data VALUES ('2019-04-20','桔子','淘宝',1317.00);
INSERT INTO sales_data VALUES ('2019-04-20','桔子','京东',1258.00);
INSERT INTO sales_data VALUES ('2019-04-20','桔子','店面',1057.00);
INSERT INTO sales_data VALUES ('2019-04-20','香蕉','淘宝',2086.00);
INSERT INTO sales_data VALUES ('2019-04-20','香蕉','京东',1791.00);
INSERT INTO sales_data VALUES ('2019-04-20','香蕉','店面',1466.00);
INSERT INTO sales_data VALUES ('2019-04-20','苹果','淘宝',2012.00);
INSERT INTO sales_data VALUES ('2019-04-20','苹果','京东',2274.00);
INSERT INTO sales_data VALUES ('2019-04-20','苹果','店面',2415.00);
INSERT INTO sales_data VALUES ('2019-04-21','桔子','淘宝',1156.00);
INSERT INTO sales_data VALUES ('2019-04-21','桔子','京东',1313.00);
INSERT INTO sales_data VALUES ('2019-04-21','桔子','店面',1554.00);
INSERT INTO sales_data VALUES ('2019-04-21','香蕉','淘宝',1471.00);
INSERT INTO sales_data VALUES ('2019-04-21','香蕉','京东',1482.00);
INSERT INTO sales_data VALUES ('2019-04-21','香蕉','店面',1647.00);
INSERT INTO sales_data VALUES ('2019-04-21','苹果','淘宝',2104.00);
INSERT INTO sales_data VALUES ('2019-04-21','苹果','京东',1276.00);
INSERT INTO sales_data VALUES ('2019-04-21','苹果','店面',1487.00);
INSERT INTO sales_data VALUES ('2019-04-22','桔子','淘宝',2352.00);
INSERT INTO sales_data VALUES ('2019-04-22','桔子','京东',1102.00);
INSERT INTO sales_data VALUES ('2019-04-22','桔子','店面',2219.00);
INSERT INTO sales_data VALUES ('2019-04-22','香蕉','淘宝',2230.00);
INSERT INTO sales_data VALUES ('2019-04-22','香蕉','京东',1930.00);
INSERT INTO sales_data VALUES ('2019-04-22','香蕉','店面',1567.00);
INSERT INTO sales_data VALUES ('2019-04-22','苹果','淘宝',2462.00);
INSERT INTO sales_data VALUES ('2019-04-22','苹果','京东',2274.00);
INSERT INTO sales_data VALUES ('2019-04-22','苹果','店面',2376.00);
INSERT INTO sales_data VALUES ('2019-04-23','桔子','淘宝',1365.00);
INSERT INTO sales_data VALUES ('2019-04-23','桔子','京东',1983.00);
INSERT INTO sales_data VALUES ('2019-04-23','桔子','店面',2039.00);
INSERT INTO sales_data VALUES ('2019-04-23','香蕉','淘宝',1984.00);
INSERT INTO sales_data VALUES ('2019-04-23','香蕉','京东',2256.00);
INSERT INTO sales_data VALUES ('2019-04-23','香蕉','店面',2252.00);
INSERT INTO sales_data VALUES ('2019-04-23','苹果','淘宝',1997.00);
INSERT INTO sales_data VALUES ('2019-04-23','苹果','京东',1797.00);
INSERT INTO sales_data VALUES ('2019-04-23','苹果','店面',1498.00);
INSERT INTO sales_data VALUES ('2019-04-24','桔子','淘宝',2423.00);
INSERT INTO sales_data VALUES ('2019-04-24','桔子','京东',1269.00);
INSERT INTO sales_data VALUES ('2019-04-24','桔子','店面',1232.00);
INSERT INTO sales_data VALUES ('2019-04-24','香蕉','淘宝',2293.00);
INSERT INTO sales_data VALUES ('2019-04-24','香蕉','京东',1375.00);
INSERT INTO sales_data VALUES ('2019-04-24','香蕉','店面',1495.00);
INSERT INTO sales_data VALUES ('2019-04-24','苹果','淘宝',1297.00);
INSERT INTO sales_data VALUES ('2019-04-24','苹果','京东',1796.00);
INSERT INTO sales_data VALUES ('2019-04-24','苹果','店面',1927.00);
INSERT INTO sales_data VALUES ('2019-04-25','桔子','淘宝',1899.00);
INSERT INTO sales_data VALUES ('2019-04-25','桔子','京东',1356.00);
INSERT INTO sales_data VALUES ('2019-04-25','桔子','店面',2158.00);
INSERT INTO sales_data VALUES ('2019-04-25','香蕉','淘宝',2336.00);
INSERT INTO sales_data VALUES ('2019-04-25','香蕉','京东',1153.00);
INSERT INTO sales_data VALUES ('2019-04-25','香蕉','店面',2205.00);
INSERT INTO sales_data VALUES ('2019-04-25','苹果','淘宝',2000.00);
INSERT INTO sales_data VALUES ('2019-04-25','苹果','京东',2327.00);
INSERT INTO sales_data VALUES ('2019-04-25','苹果','店面',1580.00);
INSERT INTO sales_data VALUES ('2019-04-26','桔子','淘宝',2517.00);
INSERT INTO sales_data VALUES ('2019-04-26','桔子','京东',2239.00);
INSERT INTO sales_data VALUES ('2019-04-26','桔子','店面',1304.00);
INSERT INTO sales_data VALUES ('2019-04-26','香蕉','淘宝',2338.00);
INSERT INTO sales_data VALUES ('2019-04-26','香蕉','京东',2545.00);
INSERT INTO sales_data VALUES ('2019-04-26','香蕉','店面',2228.00);
INSERT INTO sales_data VALUES ('2019-04-26','苹果','淘宝',1817.00);
INSERT INTO sales_data VALUES ('2019-04-26','苹果','京东',1969.00);
INSERT INTO sales_data VALUES ('2019-04-26','苹果','店面',1923.00);
INSERT INTO sales_data VALUES ('2019-04-27','桔子','淘宝',1514.00);
INSERT INTO sales_data VALUES ('2019-04-27','桔子','京东',1411.00);
INSERT INTO sales_data VALUES ('2019-04-27','桔子','店面',1165.00);
INSERT INTO sales_data VALUES ('2019-04-27','香蕉','淘宝',1952.00);
INSERT INTO sales_data VALUES ('2019-04-27','香蕉','京东',1268.00);
INSERT INTO sales_data VALUES ('2019-04-27','香蕉','店面',1369.00);
INSERT INTO sales_data VALUES ('2019-04-27','苹果','淘宝',2119.00);
INSERT INTO sales_data VALUES ('2019-04-27','苹果','京东',2496.00);
INSERT INTO sales_data VALUES ('2019-04-27','苹果','店面',1679.00);
INSERT INTO sales_data VALUES ('2019-04-28','桔子','淘宝',2554.00);
INSERT INTO sales_data VALUES ('2019-04-28','桔子','京东',1233.00);
INSERT INTO sales_data VALUES ('2019-04-28','桔子','店面',2416.00);
INSERT INTO sales_data VALUES ('2019-04-28','香蕉','淘宝',1917.00);
INSERT INTO sales_data VALUES ('2019-04-28','香蕉','京东',2061.00);
INSERT INTO sales_data VALUES ('2019-04-28','香蕉','店面',1201.00);
INSERT INTO sales_data VALUES ('2019-04-28','苹果','淘宝',1505.00);
INSERT INTO sales_data VALUES ('2019-04-28','苹果','京东',1827.00);
INSERT INTO sales_data VALUES ('2019-04-28','苹果','店面',1284.00);
INSERT INTO sales_data VALUES ('2019-04-29','桔子','淘宝',1145.00);
INSERT INTO sales_data VALUES ('2019-04-29','桔子','京东',1262.00);
INSERT INTO sales_data VALUES ('2019-04-29','桔子','店面',2546.00);
INSERT INTO sales_data VALUES ('2019-04-29','香蕉','淘宝',1655.00);
INSERT INTO sales_data VALUES ('2019-04-29','香蕉','京东',1204.00);
INSERT INTO sales_data VALUES ('2019-04-29','香蕉','店面',2210.00);
INSERT INTO sales_data VALUES ('2019-04-29','苹果','淘宝',1884.00);
INSERT INTO sales_data VALUES ('2019-04-29','苹果','京东',2467.00);
INSERT INTO sales_data VALUES ('2019-04-29','苹果','店面',2180.00);
INSERT INTO sales_data VALUES ('2019-04-30','桔子','淘宝',1542.00);
INSERT INTO sales_data VALUES ('2019-04-30','桔子','京东',1714.00);
INSERT INTO sales_data VALUES ('2019-04-30','桔子','店面',1579.00);
INSERT INTO sales_data VALUES ('2019-04-30','香蕉','淘宝',2390.00);
INSERT INTO sales_data VALUES ('2019-04-30','香蕉','京东',2148.00);
INSERT INTO sales_data VALUES ('2019-04-30','香蕉','店面',1910.00);
INSERT INTO sales_data VALUES ('2019-04-30','苹果','淘宝',2476.00);
INSERT INTO sales_data VALUES ('2019-04-30','苹果','京东',1520.00);
INSERT INTO sales_data VALUES ('2019-04-30','苹果','店面',2098.00);
INSERT INTO sales_data VALUES ('2019-05-01','桔子','淘宝',1270.00);
INSERT INTO sales_data VALUES ('2019-05-01','桔子','京东',2564.00);
INSERT INTO sales_data VALUES ('2019-05-01','桔子','店面',2019.00);
INSERT INTO sales_data VALUES ('2019-05-01','香蕉','淘宝',1870.00);
INSERT INTO sales_data VALUES ('2019-05-01','香蕉','京东',2533.00);
INSERT INTO sales_data VALUES ('2019-05-01','香蕉','店面',2167.00);
INSERT INTO sales_data VALUES ('2019-05-01','苹果','淘宝',1700.00);
INSERT INTO sales_data VALUES ('2019-05-01','苹果','京东',1865.00);
INSERT INTO sales_data VALUES ('2019-05-01','苹果','店面',1643.00);
INSERT INTO sales_data VALUES ('2019-05-02','桔子','淘宝',1822.00);
INSERT INTO sales_data VALUES ('2019-05-02','桔子','京东',2290.00);
INSERT INTO sales_data VALUES ('2019-05-02','桔子','店面',2391.00);
INSERT INTO sales_data VALUES ('2019-05-02','香蕉','淘宝',2021.00);
INSERT INTO sales_data VALUES ('2019-05-02','香蕉','京东',2345.00);
INSERT INTO sales_data VALUES ('2019-05-02','香蕉','店面',2563.00);
INSERT INTO sales_data VALUES ('2019-05-02','苹果','淘宝',1977.00);
INSERT INTO sales_data VALUES ('2019-05-02','苹果','京东',1410.00);
INSERT INTO sales_data VALUES ('2019-05-02','苹果','店面',1177.00);
INSERT INTO sales_data VALUES ('2019-05-03','桔子','淘宝',1602.00);
INSERT INTO sales_data VALUES ('2019-05-03','桔子','京东',2209.00);
INSERT INTO sales_data VALUES ('2019-05-03','桔子','店面',2559.00);
INSERT INTO sales_data VALUES ('2019-05-03','香蕉','淘宝',1192.00);
INSERT INTO sales_data VALUES ('2019-05-03','香蕉','京东',1156.00);
INSERT INTO sales_data VALUES ('2019-05-03','香蕉','店面',1678.00);
INSERT INTO sales_data VALUES ('2019-05-03','苹果','淘宝',1677.00);
INSERT INTO sales_data VALUES ('2019-05-03','苹果','京东',2452.00);
INSERT INTO sales_data VALUES ('2019-05-03','苹果','店面',1231.00);
INSERT INTO sales_data VALUES ('2019-05-04','桔子','淘宝',2496.00);
INSERT INTO sales_data VALUES ('2019-05-04','桔子','京东',2337.00);
INSERT INTO sales_data VALUES ('2019-05-04','桔子','店面',1661.00);
INSERT INTO sales_data VALUES ('2019-05-04','香蕉','淘宝',1999.00);
INSERT INTO sales_data VALUES ('2019-05-04','香蕉','京东',2508.00);
INSERT INTO sales_data VALUES ('2019-05-04','香蕉','店面',1626.00);
INSERT INTO sales_data VALUES ('2019-05-04','苹果','淘宝',1418.00);
INSERT INTO sales_data VALUES ('2019-05-04','苹果','京东',1777.00);
INSERT INTO sales_data VALUES ('2019-05-04','苹果','店面',1559.00);
INSERT INTO sales_data VALUES ('2019-05-05','桔子','淘宝',2490.00);
INSERT INTO sales_data VALUES ('2019-05-05','桔子','京东',2382.00);
INSERT INTO sales_data VALUES ('2019-05-05','桔子','店面',2329.00);
INSERT INTO sales_data VALUES ('2019-05-05','香蕉','淘宝',1533.00);
INSERT INTO sales_data VALUES ('2019-05-05','香蕉','京东',1599.00);
INSERT INTO sales_data VALUES ('2019-05-05','香蕉','店面',2014.00);
INSERT INTO sales_data VALUES ('2019-05-05','苹果','淘宝',1319.00);
INSERT INTO sales_data VALUES ('2019-05-05','苹果','京东',2515.00);
INSERT INTO sales_data VALUES ('2019-05-05','苹果','店面',1754.00);
INSERT INTO sales_data VALUES ('2019-05-06','桔子','淘宝',1282.00);
INSERT INTO sales_data VALUES ('2019-05-06','桔子','京东',1892.00);
INSERT INTO sales_data VALUES ('2019-05-06','桔子','店面',2064.00);
INSERT INTO sales_data VALUES ('2019-05-06','香蕉','淘宝',1354.00);
INSERT INTO sales_data VALUES ('2019-05-06','香蕉','京东',2384.00);
INSERT INTO sales_data VALUES ('2019-05-06','香蕉','店面',1663.00);
INSERT INTO sales_data VALUES ('2019-05-06','苹果','淘宝',1303.00);
INSERT INTO sales_data VALUES ('2019-05-06','苹果','京东',2467.00);
INSERT INTO sales_data VALUES ('2019-05-06','苹果','店面',1709.00);
INSERT INTO sales_data VALUES ('2019-05-07','桔子','淘宝',1876.00);
INSERT INTO sales_data VALUES ('2019-05-07','桔子','京东',1538.00);
INSERT INTO sales_data VALUES ('2019-05-07','桔子','店面',1556.00);
INSERT INTO sales_data VALUES ('2019-05-07','香蕉','淘宝',1997.00);
INSERT INTO sales_data VALUES ('2019-05-07','香蕉','京东',1419.00);
INSERT INTO sales_data VALUES ('2019-05-07','香蕉','店面',1278.00);
INSERT INTO sales_data VALUES ('2019-05-07','苹果','淘宝',2544.00);
INSERT INTO sales_data VALUES ('2019-05-07','苹果','京东',2303.00);
INSERT INTO sales_data VALUES ('2019-05-07','苹果','店面',1171.00);
INSERT INTO sales_data VALUES ('2019-05-08','桔子','淘宝',1559.00);
INSERT INTO sales_data VALUES ('2019-05-08','桔子','京东',2611.00);
INSERT INTO sales_data VALUES ('2019-05-08','桔子','店面',1838.00);
INSERT INTO sales_data VALUES ('2019-05-08','香蕉','淘宝',2003.00);
INSERT INTO sales_data VALUES ('2019-05-08','香蕉','京东',2481.00);
INSERT INTO sales_data VALUES ('2019-05-08','香蕉','店面',1601.00);
INSERT INTO sales_data VALUES ('2019-05-08','苹果','淘宝',1713.00);
INSERT INTO sales_data VALUES ('2019-05-08','苹果','京东',1395.00);
INSERT INTO sales_data VALUES ('2019-05-08','苹果','店面',2080.00);
INSERT INTO sales_data VALUES ('2019-05-09','桔子','淘宝',2612.00);
INSERT INTO sales_data VALUES ('2019-05-09','桔子','京东',1599.00);
INSERT INTO sales_data VALUES ('2019-05-09','桔子','店面',1980.00);
INSERT INTO sales_data VALUES ('2019-05-09','香蕉','淘宝',1746.00);
INSERT INTO sales_data VALUES ('2019-05-09','香蕉','京东',1756.00);
INSERT INTO sales_data VALUES ('2019-05-09','香蕉','店面',1246.00);
INSERT INTO sales_data VALUES ('2019-05-09','苹果','淘宝',1184.00);
INSERT INTO sales_data VALUES ('2019-05-09','苹果','京东',1984.00);
INSERT INTO sales_data VALUES ('2019-05-09','苹果','店面',2505.00);
INSERT INTO sales_data VALUES ('2019-05-10','桔子','淘宝',1728.00);
INSERT INTO sales_data VALUES ('2019-05-10','桔子','京东',2168.00);
INSERT INTO sales_data VALUES ('2019-05-10','桔子','店面',2352.00);
INSERT INTO sales_data VALUES ('2019-05-10','香蕉','淘宝',2312.00);
INSERT INTO sales_data VALUES ('2019-05-10','香蕉','京东',1414.00);
INSERT INTO sales_data VALUES ('2019-05-10','香蕉','店面',1260.00);
INSERT INTO sales_data VALUES ('2019-05-10','苹果','淘宝',1238.00);
INSERT INTO sales_data VALUES ('2019-05-10','苹果','京东',2281.00);
INSERT INTO sales_data VALUES ('2019-05-10','苹果','店面',1549.00);
INSERT INTO sales_data VALUES ('2019-05-11','桔子','淘宝',1391.00);
INSERT INTO sales_data VALUES ('2019-05-11','桔子','京东',2200.00);
INSERT INTO sales_data VALUES ('2019-05-11','桔子','店面',1227.00);
INSERT INTO sales_data VALUES ('2019-05-11','香蕉','淘宝',1432.00);
INSERT INTO sales_data VALUES ('2019-05-11','香蕉','京东',2624.00);
INSERT INTO sales_data VALUES ('2019-05-11','香蕉','店面',1204.00);
INSERT INTO sales_data VALUES ('2019-05-11','苹果','淘宝',2136.00);
INSERT INTO sales_data VALUES ('2019-05-11','苹果','京东',1992.00);
INSERT INTO sales_data VALUES ('2019-05-11','苹果','店面',2550.00);
INSERT INTO sales_data VALUES ('2019-05-12','桔子','淘宝',2606.00);
INSERT INTO sales_data VALUES ('2019-05-12','桔子','京东',2575.00);
INSERT INTO sales_data VALUES ('2019-05-12','桔子','店面',1315.00);
INSERT INTO sales_data VALUES ('2019-05-12','香蕉','淘宝',2051.00);
INSERT INTO sales_data VALUES ('2019-05-12','香蕉','京东',2547.00);
INSERT INTO sales_data VALUES ('2019-05-12','香蕉','店面',1774.00);
INSERT INTO sales_data VALUES ('2019-05-12','苹果','淘宝',1391.00);
INSERT INTO sales_data VALUES ('2019-05-12','苹果','京东',1652.00);
INSERT INTO sales_data VALUES ('2019-05-12','苹果','店面',2389.00);
INSERT INTO sales_data VALUES ('2019-05-13','桔子','淘宝',1502.00);
INSERT INTO sales_data VALUES ('2019-05-13','桔子','京东',1701.00);
INSERT INTO sales_data VALUES ('2019-05-13','桔子','店面',1739.00);
INSERT INTO sales_data VALUES ('2019-05-13','香蕉','淘宝',1367.00);
INSERT INTO sales_data VALUES ('2019-05-13','香蕉','京东',2284.00);
INSERT INTO sales_data VALUES ('2019-05-13','香蕉','店面',1261.00);
INSERT INTO sales_data VALUES ('2019-05-13','苹果','淘宝',2574.00);
INSERT INTO sales_data VALUES ('2019-05-13','苹果','京东',1951.00);
INSERT INTO sales_data VALUES ('2019-05-13','苹果','店面',1530.00);
INSERT INTO sales_data VALUES ('2019-05-14','桔子','淘宝',1194.00);
INSERT INTO sales_data VALUES ('2019-05-14','桔子','京东',2049.00);
INSERT INTO sales_data VALUES ('2019-05-14','桔子','店面',1171.00);
INSERT INTO sales_data VALUES ('2019-05-14','香蕉','淘宝',1598.00);
INSERT INTO sales_data VALUES ('2019-05-14','香蕉','京东',2290.00);
INSERT INTO sales_data VALUES ('2019-05-14','香蕉','店面',2220.00);
INSERT INTO sales_data VALUES ('2019-05-14','苹果','淘宝',1676.00);
INSERT INTO sales_data VALUES ('2019-05-14','苹果','京东',2573.00);
INSERT INTO sales_data VALUES ('2019-05-14','苹果','店面',2194.00);
INSERT INTO sales_data VALUES ('2019-05-15','桔子','淘宝',1734.00);
INSERT INTO sales_data VALUES ('2019-05-15','桔子','京东',2063.00);
INSERT INTO sales_data VALUES ('2019-05-15','桔子','店面',1541.00);
INSERT INTO sales_data VALUES ('2019-05-15','香蕉','淘宝',1634.00);
INSERT INTO sales_data VALUES ('2019-05-15','香蕉','京东',2014.00);
INSERT INTO sales_data VALUES ('2019-05-15','香蕉','店面',1461.00);
INSERT INTO sales_data VALUES ('2019-05-15','苹果','淘宝',1794.00);
INSERT INTO sales_data VALUES ('2019-05-15','苹果','京东',1411.00);
INSERT INTO sales_data VALUES ('2019-05-15','苹果','店面',1353.00);
INSERT INTO sales_data VALUES ('2019-05-16','桔子','淘宝',2418.00);
INSERT INTO sales_data VALUES ('2019-05-16','桔子','京东',1651.00);
INSERT INTO sales_data VALUES ('2019-05-16','桔子','店面',1855.00);
INSERT INTO sales_data VALUES ('2019-05-16','香蕉','淘宝',2152.00);
INSERT INTO sales_data VALUES ('2019-05-16','香蕉','京东',1994.00);
INSERT INTO sales_data VALUES ('2019-05-16','香蕉','店面',2396.00);
INSERT INTO sales_data VALUES ('2019-05-16','苹果','淘宝',1231.00);
INSERT INTO sales_data VALUES ('2019-05-16','苹果','京东',2201.00);
INSERT INTO sales_data VALUES ('2019-05-16','苹果','店面',2020.00);
INSERT INTO sales_data VALUES ('2019-05-17','桔子','淘宝',1337.00);
INSERT INTO sales_data VALUES ('2019-05-17','桔子','京东',2120.00);
INSERT INTO sales_data VALUES ('2019-05-17','桔子','店面',1316.00);
INSERT INTO sales_data VALUES ('2019-05-17','香蕉','淘宝',1706.00);
INSERT INTO sales_data VALUES ('2019-05-17','香蕉','京东',2150.00);
INSERT INTO sales_data VALUES ('2019-05-17','香蕉','店面',2200.00);
INSERT INTO sales_data VALUES ('2019-05-17','苹果','淘宝',1712.00);
INSERT INTO sales_data VALUES ('2019-05-17','苹果','京东',2583.00);
INSERT INTO sales_data VALUES ('2019-05-17','苹果','店面',1825.00);
INSERT INTO sales_data VALUES ('2019-05-18','桔子','淘宝',1272.00);
INSERT INTO sales_data VALUES ('2019-05-18','桔子','京东',1599.00);
INSERT INTO sales_data VALUES ('2019-05-18','桔子','店面',1738.00);
INSERT INTO sales_data VALUES ('2019-05-18','香蕉','淘宝',2302.00);
INSERT INTO sales_data VALUES ('2019-05-18','香蕉','京东',2163.00);
INSERT INTO sales_data VALUES ('2019-05-18','香蕉','店面',2631.00);
INSERT INTO sales_data VALUES ('2019-05-18','苹果','淘宝',2673.00);
INSERT INTO sales_data VALUES ('2019-05-18','苹果','京东',2627.00);
INSERT INTO sales_data VALUES ('2019-05-18','苹果','店面',1975.00);
INSERT INTO sales_data VALUES ('2019-05-19','桔子','淘宝',1469.00);
INSERT INTO sales_data VALUES ('2019-05-19','桔子','京东',1757.00);
INSERT INTO sales_data VALUES ('2019-05-19','桔子','店面',2221.00);
INSERT INTO sales_data VALUES ('2019-05-19','香蕉','淘宝',1652.00);
INSERT INTO sales_data VALUES ('2019-05-19','香蕉','京东',1500.00);
INSERT INTO sales_data VALUES ('2019-05-19','香蕉','店面',1197.00);
INSERT INTO sales_data VALUES ('2019-05-19','苹果','淘宝',2331.00);
INSERT INTO sales_data VALUES ('2019-05-19','苹果','京东',2477.00);
INSERT INTO sales_data VALUES ('2019-05-19','苹果','店面',2016.00);
INSERT INTO sales_data VALUES ('2019-05-20','桔子','淘宝',2057.00);
INSERT INTO sales_data VALUES ('2019-05-20','桔子','京东',2537.00);
INSERT INTO sales_data VALUES ('2019-05-20','桔子','店面',1547.00);
INSERT INTO sales_data VALUES ('2019-05-20','香蕉','淘宝',1403.00);
INSERT INTO sales_data VALUES ('2019-05-20','香蕉','京东',2694.00);
INSERT INTO sales_data VALUES ('2019-05-20','香蕉','店面',2487.00);
INSERT INTO sales_data VALUES ('2019-05-20','苹果','淘宝',1539.00);
INSERT INTO sales_data VALUES ('2019-05-20','苹果','京东',1720.00);
INSERT INTO sales_data VALUES ('2019-05-20','苹果','店面',1957.00);
INSERT INTO sales_data VALUES ('2019-05-21','桔子','淘宝',2564.00);
INSERT INTO sales_data VALUES ('2019-05-21','桔子','京东',2257.00);
INSERT INTO sales_data VALUES ('2019-05-21','桔子','店面',1865.00);
INSERT INTO sales_data VALUES ('2019-05-21','香蕉','淘宝',1709.00);
INSERT INTO sales_data VALUES ('2019-05-21','香蕉','京东',2345.00);
INSERT INTO sales_data VALUES ('2019-05-21','香蕉','店面',2278.00);
INSERT INTO sales_data VALUES ('2019-05-21','苹果','淘宝',2261.00);
INSERT INTO sales_data VALUES ('2019-05-21','苹果','京东',1961.00);
INSERT INTO sales_data VALUES ('2019-05-21','苹果','店面',1756.00);
INSERT INTO sales_data VALUES ('2019-05-22','桔子','淘宝',2212.00);
INSERT INTO sales_data VALUES ('2019-05-22','桔子','京东',1954.00);
INSERT INTO sales_data VALUES ('2019-05-22','桔子','店面',1703.00);
INSERT INTO sales_data VALUES ('2019-05-22','香蕉','淘宝',1503.00);
INSERT INTO sales_data VALUES ('2019-05-22','香蕉','京东',2233.00);
INSERT INTO sales_data VALUES ('2019-05-22','香蕉','店面',2270.00);
INSERT INTO sales_data VALUES ('2019-05-22','苹果','淘宝',2533.00);
INSERT INTO sales_data VALUES ('2019-05-22','苹果','京东',2695.00);
INSERT INTO sales_data VALUES ('2019-05-22','苹果','店面',2580.00);
INSERT INTO sales_data VALUES ('2019-05-23','桔子','淘宝',2546.00);
INSERT INTO sales_data VALUES ('2019-05-23','桔子','京东',2341.00);
INSERT INTO sales_data VALUES ('2019-05-23','桔子','店面',2371.00);
INSERT INTO sales_data VALUES ('2019-05-23','香蕉','淘宝',1872.00);
INSERT INTO sales_data VALUES ('2019-05-23','香蕉','京东',1703.00);
INSERT INTO sales_data VALUES ('2019-05-23','香蕉','店面',2213.00);
INSERT INTO sales_data VALUES ('2019-05-23','苹果','淘宝',2223.00);
INSERT INTO sales_data VALUES ('2019-05-23','苹果','京东',1911.00);
INSERT INTO sales_data VALUES ('2019-05-23','苹果','店面',2212.00);
INSERT INTO sales_data VALUES ('2019-05-24','桔子','淘宝',2021.00);
INSERT INTO sales_data VALUES ('2019-05-24','桔子','京东',2259.00);
INSERT INTO sales_data VALUES ('2019-05-24','桔子','店面',1242.00);
INSERT INTO sales_data VALUES ('2019-05-24','香蕉','淘宝',1282.00);
INSERT INTO sales_data VALUES ('2019-05-24','香蕉','京东',2123.00);
INSERT INTO sales_data VALUES ('2019-05-24','香蕉','店面',2299.00);
INSERT INTO sales_data VALUES ('2019-05-24','苹果','淘宝',1947.00);
INSERT INTO sales_data VALUES ('2019-05-24','苹果','京东',2632.00);
INSERT INTO sales_data VALUES ('2019-05-24','苹果','店面',1944.00);
INSERT INTO sales_data VALUES ('2019-05-25','桔子','淘宝',1530.00);
INSERT INTO sales_data VALUES ('2019-05-25','桔子','京东',2198.00);
INSERT INTO sales_data VALUES ('2019-05-25','桔子','店面',2710.00);
INSERT INTO sales_data VALUES ('2019-05-25','香蕉','淘宝',2087.00);
INSERT INTO sales_data VALUES ('2019-05-25','香蕉','京东',1706.00);
INSERT INTO sales_data VALUES ('2019-05-25','香蕉','店面',1959.00);
INSERT INTO sales_data VALUES ('2019-05-25','苹果','淘宝',2585.00);
INSERT INTO sales_data VALUES ('2019-05-25','苹果','京东',2003.00);
INSERT INTO sales_data VALUES ('2019-05-25','苹果','店面',1487.00);
INSERT INTO sales_data VALUES ('2019-05-26','桔子','淘宝',2155.00);
INSERT INTO sales_data VALUES ('2019-05-26','桔子','京东',1837.00);
INSERT INTO sales_data VALUES ('2019-05-26','桔子','店面',1482.00);
INSERT INTO sales_data VALUES ('2019-05-26','香蕉','淘宝',2030.00);
INSERT INTO sales_data VALUES ('2019-05-26','香蕉','京东',1672.00);
INSERT INTO sales_data VALUES ('2019-05-26','香蕉','店面',2612.00);
INSERT INTO sales_data VALUES ('2019-05-26','苹果','淘宝',1691.00);
INSERT INTO sales_data VALUES ('2019-05-26','苹果','京东',2334.00);
INSERT INTO sales_data VALUES ('2019-05-26','苹果','店面',1606.00);
INSERT INTO sales_data VALUES ('2019-05-27','桔子','淘宝',2699.00);
INSERT INTO sales_data VALUES ('2019-05-27','桔子','京东',1852.00);
INSERT INTO sales_data VALUES ('2019-05-27','桔子','店面',2311.00);
INSERT INTO sales_data VALUES ('2019-05-27','香蕉','淘宝',2201.00);
INSERT INTO sales_data VALUES ('2019-05-27','香蕉','京东',2658.00);
INSERT INTO sales_data VALUES ('2019-05-27','香蕉','店面',1856.00);
INSERT INTO sales_data VALUES ('2019-05-27','苹果','淘宝',2228.00);
INSERT INTO sales_data VALUES ('2019-05-27','苹果','京东',2725.00);
INSERT INTO sales_data VALUES ('2019-05-27','苹果','店面',1264.00);
INSERT INTO sales_data VALUES ('2019-05-28','桔子','淘宝',1818.00);
INSERT INTO sales_data VALUES ('2019-05-28','桔子','京东',1963.00);
INSERT INTO sales_data VALUES ('2019-05-28','桔子','店面',2686.00);
INSERT INTO sales_data VALUES ('2019-05-28','香蕉','淘宝',2547.00);
INSERT INTO sales_data VALUES ('2019-05-28','香蕉','京东',2273.00);
INSERT INTO sales_data VALUES ('2019-05-28','香蕉','店面',2164.00);
INSERT INTO sales_data VALUES ('2019-05-28','苹果','淘宝',2537.00);
INSERT INTO sales_data VALUES ('2019-05-28','苹果','京东',1640.00);
INSERT INTO sales_data VALUES ('2019-05-28','苹果','店面',2650.00);
INSERT INTO sales_data VALUES ('2019-05-29','桔子','淘宝',1781.00);
INSERT INTO sales_data VALUES ('2019-05-29','桔子','京东',1510.00);
INSERT INTO sales_data VALUES ('2019-05-29','桔子','店面',1938.00);
INSERT INTO sales_data VALUES ('2019-05-29','香蕉','淘宝',2048.00);
INSERT INTO sales_data VALUES ('2019-05-29','香蕉','京东',2440.00);
INSERT INTO sales_data VALUES ('2019-05-29','香蕉','店面',2549.00);
INSERT INTO sales_data VALUES ('2019-05-29','苹果','淘宝',2304.00);
INSERT INTO sales_data VALUES ('2019-05-29','苹果','京东',1745.00);
INSERT INTO sales_data VALUES ('2019-05-29','苹果','店面',1497.00);
INSERT INTO sales_data VALUES ('2019-05-30','桔子','淘宝',2197.00);
INSERT INTO sales_data VALUES ('2019-05-30','桔子','京东',2216.00);
INSERT INTO sales_data VALUES ('2019-05-30','桔子','店面',2610.00);
INSERT INTO sales_data VALUES ('2019-05-30','香蕉','淘宝',2577.00);
INSERT INTO sales_data VALUES ('2019-05-30','香蕉','京东',2185.00);
INSERT INTO sales_data VALUES ('2019-05-30','香蕉','店面',1733.00);
INSERT INTO sales_data VALUES ('2019-05-30','苹果','淘宝',2159.00);
INSERT INTO sales_data VALUES ('2019-05-30','苹果','京东',1657.00);
INSERT INTO sales_data VALUES ('2019-05-30','苹果','店面',1661.00);
INSERT INTO sales_data VALUES ('2019-05-31','桔子','淘宝',1290.00);
INSERT INTO sales_data VALUES ('2019-05-31','桔子','京东',2660.00);
INSERT INTO sales_data VALUES ('2019-05-31','桔子','店面',1661.00);
INSERT INTO sales_data VALUES ('2019-05-31','香蕉','淘宝',1324.00);
INSERT INTO sales_data VALUES ('2019-05-31','香蕉','京东',1743.00);
INSERT INTO sales_data VALUES ('2019-05-31','香蕉','店面',2389.00);
INSERT INTO sales_data VALUES ('2019-05-31','苹果','淘宝',1274.00);
INSERT INTO sales_data VALUES ('2019-05-31','苹果','京东',1554.00);
INSERT INTO sales_data VALUES ('2019-05-31','苹果','店面',1927.00);
INSERT INTO sales_data VALUES ('2019-06-01','桔子','淘宝',2209.00);
INSERT INTO sales_data VALUES ('2019-06-01','桔子','京东',1361.00);
INSERT INTO sales_data VALUES ('2019-06-01','桔子','店面',2336.00);
INSERT INTO sales_data VALUES ('2019-06-01','香蕉','淘宝',2123.00);
INSERT INTO sales_data VALUES ('2019-06-01','香蕉','京东',1902.00);
INSERT INTO sales_data VALUES ('2019-06-01','香蕉','店面',2606.00);
INSERT INTO sales_data VALUES ('2019-06-01','苹果','淘宝',1321.00);
INSERT INTO sales_data VALUES ('2019-06-01','苹果','京东',2710.00);
INSERT INTO sales_data VALUES ('2019-06-01','苹果','店面',2306.00);
INSERT INTO sales_data VALUES ('2019-06-02','桔子','淘宝',2635.00);
INSERT INTO sales_data VALUES ('2019-06-02','桔子','京东',2279.00);
INSERT INTO sales_data VALUES ('2019-06-02','桔子','店面',1316.00);
INSERT INTO sales_data VALUES ('2019-06-02','香蕉','淘宝',1392.00);
INSERT INTO sales_data VALUES ('2019-06-02','香蕉','京东',1731.00);
INSERT INTO sales_data VALUES ('2019-06-02','香蕉','店面',2287.00);
INSERT INTO sales_data VALUES ('2019-06-02','苹果','淘宝',2757.00);
INSERT INTO sales_data VALUES ('2019-06-02','苹果','京东',1563.00);
INSERT INTO sales_data VALUES ('2019-06-02','苹果','店面',1728.00);
INSERT INTO sales_data VALUES ('2019-06-03','桔子','淘宝',1750.00);
INSERT INTO sales_data VALUES ('2019-06-03','桔子','京东',2482.00);
INSERT INTO sales_data VALUES ('2019-06-03','桔子','店面',2144.00);
INSERT INTO sales_data VALUES ('2019-06-03','香蕉','淘宝',2166.00);
INSERT INTO sales_data VALUES ('2019-06-03','香蕉','京东',2522.00);
INSERT INTO sales_data VALUES ('2019-06-03','香蕉','店面',2054.00);
INSERT INTO sales_data VALUES ('2019-06-03','苹果','淘宝',2577.00);
INSERT INTO sales_data VALUES ('2019-06-03','苹果','京东',2596.00);
INSERT INTO sales_data VALUES ('2019-06-03','苹果','店面',2547.00);
INSERT INTO sales_data VALUES ('2019-06-04','桔子','淘宝',2220.00);
INSERT INTO sales_data VALUES ('2019-06-04','桔子','京东',2625.00);
INSERT INTO sales_data VALUES ('2019-06-04','桔子','店面',1357.00);
INSERT INTO sales_data VALUES ('2019-06-04','香蕉','淘宝',1397.00);
INSERT INTO sales_data VALUES ('2019-06-04','香蕉','京东',2079.00);
INSERT INTO sales_data VALUES ('2019-06-04','香蕉','店面',1463.00);
INSERT INTO sales_data VALUES ('2019-06-04','苹果','淘宝',2478.00);
INSERT INTO sales_data VALUES ('2019-06-04','苹果','京东',1447.00);
INSERT INTO sales_data VALUES ('2019-06-04','苹果','店面',2109.00);
INSERT INTO sales_data VALUES ('2019-06-05','桔子','淘宝',2334.00);
INSERT INTO sales_data VALUES ('2019-06-05','桔子','京东',1518.00);
INSERT INTO sales_data VALUES ('2019-06-05','桔子','店面',2069.00);
INSERT INTO sales_data VALUES ('2019-06-05','香蕉','淘宝',1885.00);
INSERT INTO sales_data VALUES ('2019-06-05','香蕉','京东',1393.00);
INSERT INTO sales_data VALUES ('2019-06-05','香蕉','店面',1588.00);
INSERT INTO sales_data VALUES ('2019-06-05','苹果','淘宝',1942.00);
INSERT INTO sales_data VALUES ('2019-06-05','苹果','京东',1525.00);
INSERT INTO sales_data VALUES ('2019-06-05','苹果','店面',2059.00);
INSERT INTO sales_data VALUES ('2019-06-06','桔子','淘宝',1474.00);
INSERT INTO sales_data VALUES ('2019-06-06','桔子','京东',1527.00);
INSERT INTO sales_data VALUES ('2019-06-06','桔子','店面',2367.00);
INSERT INTO sales_data VALUES ('2019-06-06','香蕉','淘宝',1941.00);
INSERT INTO sales_data VALUES ('2019-06-06','香蕉','京东',2012.00);
INSERT INTO sales_data VALUES ('2019-06-06','香蕉','店面',2085.00);
INSERT INTO sales_data VALUES ('2019-06-06','苹果','淘宝',1321.00);
INSERT INTO sales_data VALUES ('2019-06-06','苹果','京东',1413.00);
INSERT INTO sales_data VALUES ('2019-06-06','苹果','店面',1841.00);
INSERT INTO sales_data VALUES ('2019-06-07','桔子','淘宝',2115.00);
INSERT INTO sales_data VALUES ('2019-06-07','桔子','京东',2730.00);
INSERT INTO sales_data VALUES ('2019-06-07','桔子','店面',1677.00);
INSERT INTO sales_data VALUES ('2019-06-07','香蕉','淘宝',1897.00);
INSERT INTO sales_data VALUES ('2019-06-07','香蕉','京东',2180.00);
INSERT INTO sales_data VALUES ('2019-06-07','香蕉','店面',1532.00);
INSERT INTO sales_data VALUES ('2019-06-07','苹果','淘宝',1984.00);
INSERT INTO sales_data VALUES ('2019-06-07','苹果','京东',2307.00);
INSERT INTO sales_data VALUES ('2019-06-07','苹果','店面',2341.00);
INSERT INTO sales_data VALUES ('2019-06-08','桔子','淘宝',2181.00);
INSERT INTO sales_data VALUES ('2019-06-08','桔子','京东',2020.00);
INSERT INTO sales_data VALUES ('2019-06-08','桔子','店面',2523.00);
INSERT INTO sales_data VALUES ('2019-06-08','香蕉','淘宝',1521.00);
INSERT INTO sales_data VALUES ('2019-06-08','香蕉','京东',1579.00);
INSERT INTO sales_data VALUES ('2019-06-08','香蕉','店面',2766.00);
INSERT INTO sales_data VALUES ('2019-06-08','苹果','淘宝',2315.00);
INSERT INTO sales_data VALUES ('2019-06-08','苹果','京东',2190.00);
INSERT INTO sales_data VALUES ('2019-06-08','苹果','店面',1384.00);
INSERT INTO sales_data VALUES ('2019-06-09','桔子','淘宝',2633.00);
INSERT INTO sales_data VALUES ('2019-06-09','桔子','京东',1361.00);
INSERT INTO sales_data VALUES ('2019-06-09','桔子','店面',1639.00);
INSERT INTO sales_data VALUES ('2019-06-09','香蕉','淘宝',1916.00);
INSERT INTO sales_data VALUES ('2019-06-09','香蕉','京东',1555.00);
INSERT INTO sales_data VALUES ('2019-06-09','香蕉','店面',1886.00);
INSERT INTO sales_data VALUES ('2019-06-09','苹果','淘宝',1504.00);
INSERT INTO sales_data VALUES ('2019-06-09','苹果','京东',2217.00);
INSERT INTO sales_data VALUES ('2019-06-09','苹果','店面',2619.00);
INSERT INTO sales_data VALUES ('2019-06-10','桔子','淘宝',2313.00);
INSERT INTO sales_data VALUES ('2019-06-10','桔子','京东',2262.00);
INSERT INTO sales_data VALUES ('2019-06-10','桔子','店面',2757.00);
INSERT INTO sales_data VALUES ('2019-06-10','香蕉','淘宝',1375.00);
INSERT INTO sales_data VALUES ('2019-06-10','香蕉','京东',1592.00);
INSERT INTO sales_data VALUES ('2019-06-10','香蕉','店面',2701.00);
INSERT INTO sales_data VALUES ('2019-06-10','苹果','淘宝',1767.00);
INSERT INTO sales_data VALUES ('2019-06-10','苹果','京东',2204.00);
INSERT INTO sales_data VALUES ('2019-06-10','苹果','店面',2096.00);
INSERT INTO sales_data VALUES ('2019-06-11','桔子','淘宝',2019.00);
INSERT INTO sales_data VALUES ('2019-06-11','桔子','京东',1408.00);
INSERT INTO sales_data VALUES ('2019-06-11','桔子','店面',1623.00);
INSERT INTO sales_data VALUES ('2019-06-11','香蕉','淘宝',1575.00);
INSERT INTO sales_data VALUES ('2019-06-11','香蕉','京东',2299.00);
INSERT INTO sales_data VALUES ('2019-06-11','香蕉','店面',2353.00);
INSERT INTO sales_data VALUES ('2019-06-11','苹果','淘宝',1308.00);
INSERT INTO sales_data VALUES ('2019-06-11','苹果','京东',2530.00);
INSERT INTO sales_data VALUES ('2019-06-11','苹果','店面',2643.00);
INSERT INTO sales_data VALUES ('2019-06-12','桔子','淘宝',2788.00);
INSERT INTO sales_data VALUES ('2019-06-12','桔子','京东',2060.00);
INSERT INTO sales_data VALUES ('2019-06-12','桔子','店面',2048.00);
INSERT INTO sales_data VALUES ('2019-06-12','香蕉','淘宝',1382.00);
INSERT INTO sales_data VALUES ('2019-06-12','香蕉','京东',1897.00);
INSERT INTO sales_data VALUES ('2019-06-12','香蕉','店面',2114.00);
INSERT INTO sales_data VALUES ('2019-06-12','苹果','淘宝',1726.00);
INSERT INTO sales_data VALUES ('2019-06-12','苹果','京东',2519.00);
INSERT INTO sales_data VALUES ('2019-06-12','苹果','店面',2374.00);
INSERT INTO sales_data VALUES ('2019-06-13','桔子','淘宝',2322.00);
INSERT INTO sales_data VALUES ('2019-06-13','桔子','京东',2732.00);
INSERT INTO sales_data VALUES ('2019-06-13','桔子','店面',1801.00);
INSERT INTO sales_data VALUES ('2019-06-13','香蕉','淘宝',2145.00);
INSERT INTO sales_data VALUES ('2019-06-13','香蕉','京东',2246.00);
INSERT INTO sales_data VALUES ('2019-06-13','香蕉','店面',2763.00);
INSERT INTO sales_data VALUES ('2019-06-13','苹果','淘宝',2102.00);
INSERT INTO sales_data VALUES ('2019-06-13','苹果','京东',2320.00);
INSERT INTO sales_data VALUES ('2019-06-13','苹果','店面',1555.00);
INSERT INTO sales_data VALUES ('2019-06-14','桔子','淘宝',2008.00);
INSERT INTO sales_data VALUES ('2019-06-14','桔子','京东',2792.00);
INSERT INTO sales_data VALUES ('2019-06-14','桔子','店面',2465.00);
INSERT INTO sales_data VALUES ('2019-06-14','香蕉','淘宝',2805.00);
INSERT INTO sales_data VALUES ('2019-06-14','香蕉','京东',2006.00);
INSERT INTO sales_data VALUES ('2019-06-14','香蕉','店面',2568.00);
INSERT INTO sales_data VALUES ('2019-06-14','苹果','淘宝',1623.00);
INSERT INTO sales_data VALUES ('2019-06-14','苹果','京东',2276.00);
INSERT INTO sales_data VALUES ('2019-06-14','苹果','店面',2062.00);
INSERT INTO sales_data VALUES ('2019-06-15','桔子','淘宝',2677.00);
INSERT INTO sales_data VALUES ('2019-06-15','桔子','京东',2284.00);
INSERT INTO sales_data VALUES ('2019-06-15','桔子','店面',1793.00);
INSERT INTO sales_data VALUES ('2019-06-15','香蕉','淘宝',2514.00);
INSERT INTO sales_data VALUES ('2019-06-15','香蕉','京东',2262.00);
INSERT INTO sales_data VALUES ('2019-06-15','香蕉','店面',2542.00);
INSERT INTO sales_data VALUES ('2019-06-15','苹果','淘宝',1752.00);
INSERT INTO sales_data VALUES ('2019-06-15','苹果','京东',2334.00);
INSERT INTO sales_data VALUES ('2019-06-15','苹果','店面',1629.00);
INSERT INTO sales_data VALUES ('2019-06-16','桔子','淘宝',2561.00);
INSERT INTO sales_data VALUES ('2019-06-16','桔子','京东',2754.00);
INSERT INTO sales_data VALUES ('2019-06-16','桔子','店面',1343.00);
INSERT INTO sales_data VALUES ('2019-06-16','香蕉','淘宝',2125.00);
INSERT INTO sales_data VALUES ('2019-06-16','香蕉','京东',2261.00);
INSERT INTO sales_data VALUES ('2019-06-16','香蕉','店面',2760.00);
INSERT INTO sales_data VALUES ('2019-06-16','苹果','淘宝',2610.00);
INSERT INTO sales_data VALUES ('2019-06-16','苹果','京东',1592.00);
INSERT INTO sales_data VALUES ('2019-06-16','苹果','店面',2191.00);
INSERT INTO sales_data VALUES ('2019-06-17','桔子','淘宝',2563.00);
INSERT INTO sales_data VALUES ('2019-06-17','桔子','京东',2383.00);
INSERT INTO sales_data VALUES ('2019-06-17','桔子','店面',1701.00);
INSERT INTO sales_data VALUES ('2019-06-17','香蕉','淘宝',2804.00);
INSERT INTO sales_data VALUES ('2019-06-17','香蕉','京东',1572.00);
INSERT INTO sales_data VALUES ('2019-06-17','香蕉','店面',1674.00);
INSERT INTO sales_data VALUES ('2019-06-17','苹果','淘宝',2448.00);
INSERT INTO sales_data VALUES ('2019-06-17','苹果','京东',1557.00);
INSERT INTO sales_data VALUES ('2019-06-17','苹果','店面',2360.00);
INSERT INTO sales_data VALUES ('2019-06-18','桔子','淘宝',2201.00);
INSERT INTO sales_data VALUES ('2019-06-18','桔子','京东',1865.00);
INSERT INTO sales_data VALUES ('2019-06-18','桔子','店面',1821.00);
INSERT INTO sales_data VALUES ('2019-06-18','香蕉','淘宝',1444.00);
INSERT INTO sales_data VALUES ('2019-06-18','香蕉','京东',1716.00);
INSERT INTO sales_data VALUES ('2019-06-18','香蕉','店面',2780.00);
INSERT INTO sales_data VALUES ('2019-06-18','苹果','淘宝',1911.00);
INSERT INTO sales_data VALUES ('2019-06-18','苹果','京东',1405.00);
INSERT INTO sales_data VALUES ('2019-06-18','苹果','店面',2216.00);
INSERT INTO sales_data VALUES ('2019-06-19','桔子','淘宝',1634.00);
INSERT INTO sales_data VALUES ('2019-06-19','桔子','京东',1837.00);
INSERT INTO sales_data VALUES ('2019-06-19','桔子','店面',1730.00);
INSERT INTO sales_data VALUES ('2019-06-19','香蕉','淘宝',1938.00);
INSERT INTO sales_data VALUES ('2019-06-19','香蕉','京东',1568.00);
INSERT INTO sales_data VALUES ('2019-06-19','香蕉','店面',1655.00);
INSERT INTO sales_data VALUES ('2019-06-19','苹果','淘宝',1951.00);
INSERT INTO sales_data VALUES ('2019-06-19','苹果','京东',2363.00);
INSERT INTO sales_data VALUES ('2019-06-19','苹果','店面',2586.00);
INSERT INTO sales_data VALUES ('2019-06-20','桔子','淘宝',1886.00);
INSERT INTO sales_data VALUES ('2019-06-20','桔子','京东',2148.00);
INSERT INTO sales_data VALUES ('2019-06-20','桔子','店面',1352.00);
INSERT INTO sales_data VALUES ('2019-06-20','香蕉','淘宝',2747.00);
INSERT INTO sales_data VALUES ('2019-06-20','香蕉','京东',1876.00);
INSERT INTO sales_data VALUES ('2019-06-20','香蕉','店面',2401.00);
INSERT INTO sales_data VALUES ('2019-06-20','苹果','淘宝',1614.00);
INSERT INTO sales_data VALUES ('2019-06-20','苹果','京东',1845.00);
INSERT INTO sales_data VALUES ('2019-06-20','苹果','店面',2638.00);
INSERT INTO sales_data VALUES ('2019-06-21','桔子','淘宝',1958.00);
INSERT INTO sales_data VALUES ('2019-06-21','桔子','京东',1464.00);
INSERT INTO sales_data VALUES ('2019-06-21','桔子','店面',1364.00);
INSERT INTO sales_data VALUES ('2019-06-21','香蕉','淘宝',1483.00);
INSERT INTO sales_data VALUES ('2019-06-21','香蕉','京东',2325.00);
INSERT INTO sales_data VALUES ('2019-06-21','香蕉','店面',1889.00);
INSERT INTO sales_data VALUES ('2019-06-21','苹果','淘宝',1964.00);
INSERT INTO sales_data VALUES ('2019-06-21','苹果','京东',2429.00);
INSERT INTO sales_data VALUES ('2019-06-21','苹果','店面',2265.00);
INSERT INTO sales_data VALUES ('2019-06-22','桔子','淘宝',1908.00);
INSERT INTO sales_data VALUES ('2019-06-22','桔子','京东',1505.00);
INSERT INTO sales_data VALUES ('2019-06-22','桔子','店面',2336.00);
INSERT INTO sales_data VALUES ('2019-06-22','香蕉','淘宝',2785.00);
INSERT INTO sales_data VALUES ('2019-06-22','香蕉','京东',1794.00);
INSERT INTO sales_data VALUES ('2019-06-22','香蕉','店面',2828.00);
INSERT INTO sales_data VALUES ('2019-06-22','苹果','淘宝',1670.00);
INSERT INTO sales_data VALUES ('2019-06-22','苹果','京东',2387.00);
INSERT INTO sales_data VALUES ('2019-06-22','苹果','店面',1551.00);
INSERT INTO sales_data VALUES ('2019-06-23','桔子','淘宝',1985.00);
INSERT INTO sales_data VALUES ('2019-06-23','桔子','京东',1498.00);
INSERT INTO sales_data VALUES ('2019-06-23','桔子','店面',2573.00);
INSERT INTO sales_data VALUES ('2019-06-23','香蕉','淘宝',1725.00);
INSERT INTO sales_data VALUES ('2019-06-23','香蕉','京东',2034.00);
INSERT INTO sales_data VALUES ('2019-06-23','香蕉','店面',1871.00);
INSERT INTO sales_data VALUES ('2019-06-23','苹果','淘宝',1728.00);
INSERT INTO sales_data VALUES ('2019-06-23','苹果','京东',1932.00);
INSERT INTO sales_data VALUES ('2019-06-23','苹果','店面',2398.00);
INSERT INTO sales_data VALUES ('2019-06-24','桔子','淘宝',2784.00);
INSERT INTO sales_data VALUES ('2019-06-24','桔子','京东',2201.00);
INSERT INTO sales_data VALUES ('2019-06-24','桔子','店面',1398.00);
INSERT INTO sales_data VALUES ('2019-06-24','香蕉','淘宝',2571.00);
INSERT INTO sales_data VALUES ('2019-06-24','香蕉','京东',2803.00);
INSERT INTO sales_data VALUES ('2019-06-24','香蕉','店面',1507.00);
INSERT INTO sales_data VALUES ('2019-06-24','苹果','淘宝',2581.00);
INSERT INTO sales_data VALUES ('2019-06-24','苹果','京东',1431.00);
INSERT INTO sales_data VALUES ('2019-06-24','苹果','店面',2477.00);
INSERT INTO sales_data VALUES ('2019-06-25','桔子','淘宝',1620.00);
INSERT INTO sales_data VALUES ('2019-06-25','桔子','京东',2044.00);
INSERT INTO sales_data VALUES ('2019-06-25','桔子','店面',2055.00);
INSERT INTO sales_data VALUES ('2019-06-25','香蕉','淘宝',2530.00);
INSERT INTO sales_data VALUES ('2019-06-25','香蕉','京东',2593.00);
INSERT INTO sales_data VALUES ('2019-06-25','香蕉','店面',2201.00);
INSERT INTO sales_data VALUES ('2019-06-25','苹果','淘宝',2006.00);
INSERT INTO sales_data VALUES ('2019-06-25','苹果','京东',2517.00);
INSERT INTO sales_data VALUES ('2019-06-25','苹果','店面',2634.00);
INSERT INTO sales_data VALUES ('2019-06-26','桔子','淘宝',1979.00);
INSERT INTO sales_data VALUES ('2019-06-26','桔子','京东',2832.00);
INSERT INTO sales_data VALUES ('2019-06-26','桔子','店面',2166.00);
INSERT INTO sales_data VALUES ('2019-06-26','香蕉','淘宝',2170.00);
INSERT INTO sales_data VALUES ('2019-06-26','香蕉','京东',1952.00);
INSERT INTO sales_data VALUES ('2019-06-26','香蕉','店面',2299.00);
INSERT INTO sales_data VALUES ('2019-06-26','苹果','淘宝',1878.00);
INSERT INTO sales_data VALUES ('2019-06-26','苹果','京东',2312.00);
INSERT INTO sales_data VALUES ('2019-06-26','苹果','店面',1468.00);
INSERT INTO sales_data VALUES ('2019-06-27','桔子','淘宝',2389.00);
INSERT INTO sales_data VALUES ('2019-06-27','桔子','京东',2680.00);
INSERT INTO sales_data VALUES ('2019-06-27','桔子','店面',2040.00);
INSERT INTO sales_data VALUES ('2019-06-27','香蕉','淘宝',1922.00);
INSERT INTO sales_data VALUES ('2019-06-27','香蕉','京东',2594.00);
INSERT INTO sales_data VALUES ('2019-06-27','香蕉','店面',2870.00);
INSERT INTO sales_data VALUES ('2019-06-27','苹果','淘宝',1950.00);
INSERT INTO sales_data VALUES ('2019-06-27','苹果','京东',2296.00);
INSERT INTO sales_data VALUES ('2019-06-27','苹果','店面',2803.00);
INSERT INTO sales_data VALUES ('2019-06-28','桔子','淘宝',2092.00);
INSERT INTO sales_data VALUES ('2019-06-28','桔子','京东',2011.00);
INSERT INTO sales_data VALUES ('2019-06-28','桔子','店面',2869.00);
INSERT INTO sales_data VALUES ('2019-06-28','香蕉','淘宝',1699.00);
INSERT INTO sales_data VALUES ('2019-06-28','香蕉','京东',2256.00);
INSERT INTO sales_data VALUES ('2019-06-28','香蕉','店面',2038.00);
INSERT INTO sales_data VALUES ('2019-06-28','苹果','淘宝',2379.00);
INSERT INTO sales_data VALUES ('2019-06-28','苹果','京东',1911.00);
INSERT INTO sales_data VALUES ('2019-06-28','苹果','店面',1756.00);
INSERT INTO sales_data VALUES ('2019-06-29','桔子','淘宝',1709.00);
INSERT INTO sales_data VALUES ('2019-06-29','桔子','京东',2547.00);
INSERT INTO sales_data VALUES ('2019-06-29','桔子','店面',1403.00);
INSERT INTO sales_data VALUES ('2019-06-29','香蕉','淘宝',1469.00);
INSERT INTO sales_data VALUES ('2019-06-29','香蕉','京东',1646.00);
INSERT INTO sales_data VALUES ('2019-06-29','香蕉','店面',2856.00);
INSERT INTO sales_data VALUES ('2019-06-29','苹果','淘宝',2255.00);
INSERT INTO sales_data VALUES ('2019-06-29','苹果','京东',2436.00);
INSERT INTO sales_data VALUES ('2019-06-29','苹果','店面',1928.00);
INSERT INTO sales_data VALUES ('2019-06-30','桔子','淘宝',1679.00);
INSERT INTO sales_data VALUES ('2019-06-30','桔子','京东',1439.00);
INSERT INTO sales_data VALUES ('2019-06-30','桔子','店面',2865.00);
INSERT INTO sales_data VALUES ('2019-06-30','香蕉','淘宝',1767.00);
INSERT INTO sales_data VALUES ('2019-06-30','香蕉','京东',2443.00);
INSERT INTO sales_data VALUES ('2019-06-30','香蕉','店面',2660.00);
INSERT INTO sales_data VALUES ('2019-06-30','苹果','淘宝',2422.00);
INSERT INTO sales_data VALUES ('2019-06-30','苹果','京东',1481.00);
INSERT INTO sales_data VALUES ('2019-06-30','苹果','店面',2369.00);
select COALESCE(product, '总计') as '产品',
COALESCE(channel, '--') as '渠道',
sum(case EXTRACT(month from saledate) when 1 then amount end) as '1月',
sum(case EXTRACT(month from saledate) when 2 then amount end) as '2月',
sum(case EXTRACT(month from saledate) when 3 then amount end) as '3月',
sum(case EXTRACT(month from saledate) when 4 then amount end) as '4月',
sum(case EXTRACT(month from saledate) when 5 then amount end) as '5月',
sum(case EXTRACT(month from saledate) when 6 then amount end) as '6月',
sum(amount) as '合计'
from sales_data
GROUP BY product, channel with ROLLUP;
文章来源地址https://www.toymoban.com/news/detail-576651.html
文章来源:https://www.toymoban.com/news/detail-576651.html
到了这里,关于sql_in_action的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!