课堂测试第三次(数据查询导出)
数据分析处理:
(1)统计每天各个机场的销售数量和销售金额。
要求的输出字段
day_id,sale_nbr,,cnt,round
日期编号,卖出方代码,数量,金额
(2)统计每天各个代理商的销售数量和销售金额。
要求的输出字段
day_id,sale_nbr,,cnt,round
日期编号,卖出方代码,数量,金额
(3)统计每天各个代理商的销售活跃度。
要求的输出字段
day_id,sale_nbr, sale_number
日期编号,卖出方代码,交易次数(买入或者卖出均算交易次数)
(4)汇总统计9月1日到9月15日之间各个代理商的销售利润。
编号,卖出方代码,买入数量,买入金额,卖出数量,卖出金额,销售利润(卖出金额-买入金额)
Mysql建表语句:
USE `company`;
CREATE TABLE `sale1` (
`day_id` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`sale_nbr` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`cnt` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`round` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `sale2` (
`day_id` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`sale_nbr` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`cnt` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`round` varchar(50) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
USE `company`;
CREATE TABLE `sale3` (
`day_id` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`sale_nbr` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
`sale_number` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `sale4` (
`day_id` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`nbr` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`buy_cnt` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`buy_round` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`sale_cnt` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`sale_round` varchar(50) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
查询1:
INSERT INTO company.`sale1`(
day_id,
sale_nbr,
cnt,
ROUND
)SELECT
day_id,
sale_nbr,
SUM(cnt),
SUM(ROUND)
FROM
company.sale
WHERE sale_nbr LIKE 'C%'
GROUP BY day_id,sale_nbr;
查询2
INSERT INTO company.`sale2`(
day_id,
sale_nbr,
cnt,
ROUND
)SELECT
day_id,
sale_nbr,
SUM(cnt),
SUM(ROUND)
FROM
company.sale
WHERE sale_nbr LIKE 'O%'
GROUP BY day_id,sale_nbr;
查询3:
INSERT INTO company.sale3(
day_id,
sale_nbr,
sale_number
)SELECT
day_id,
sale_nbr,
COUNT(sale_nbr)
FROM
company.sale
WHERE sale_nbr LIKE "O%"
GROUP BY sale_nbr,
day_id ;
查询4
买:
SELECT day_id, buy_nbr,SUM(cnt) AS buy_cnt ,SUM(ROUND) AS buy_round FROM company.sale WHERE (buy_nbr!='PAX') AND (buy_nbr!='') GROUP BY buy_nbr,day_id;
卖:
SELECT day_id, sale_nbr,SUM(cnt) AS sale_cnt,SUM(ROUND) AS sale_round FROM company.sale WHERE sale_nbr LIKE "O%" GROUP BY sale_nbr,day_id;
插入:
INSERT INTO company.`sale4_b`(
day_id,
buy_nbr,
buy_cnt,
buy_round
)SELECT day_id, buy_nbr,SUM(cnt) AS buy_cnt ,SUM(ROUND) AS buy_round FROM company.sale WHERE (buy_nbr!='PAX') AND (buy_nbr!='') GROUP BY buy_nbr,day_id;
插入sale4:
INSERT INTO company.sale4(
day_id,
nbr,
buy_cnt,
buy_round,
sale_cnt,
sale_round,
w
)SELECT
sale4_b.day_id,
buy_nbr,
buy_cnt,
buy_round,
sale_cnt,
sale_round,
(sale_round-buy_round)
FROM
sale4_b
JOIN sale4_s
WHERE sale4_b.day_id = sale4_s.day_id
AND sale4_b.buy_nbr = sale4_s.sale_nbr ;