原始题目需求
**原始表,**需要求出每个品牌的促销天数
结果表
思路:①先将跨天日期拆解为以天为单位的记录
②聚合count(distinct tdate)
1.原表建表和插入数据sql脚本
/*
Navicat Premium Data Transfer
Source Server : windows1
Source Server Type : MySQL
Source Server Version : 50527
Source Host : localhost:3306
Source Schema : sqltest
Target Server Type : MySQL
Target Server Version : 50527
File Encoding : 65001
Date: 26/10/2019 20:45:40
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`id` int(11) NOT NULL,
`brand` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`startdate` date NULL DEFAULT NULL,
`enddate` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 'nike', '2018-09-01', '2018-09-05');
INSERT INTO `sales` VALUES (2, 'nike', '2018-09-03', '2018-09-06');
INSERT INTO `sales` VALUES (3, 'nike', '2018-09-09', '2018-09-15');
INSERT INTO `sales` VALUES (4, 'oppo', '2018-08-04', '2018-08-05');
INSERT INTO `sales` VALUES (5, 'oppo', '2018-08-04', '2018-08-15');
INSERT INTO `sales` VALUES (6, 'vivo', '2018-08-15', '2018-08-21');
INSERT INTO `sales` VALUES (7, 'vivo', '2018-09-02', '2018-09-12');
SET FOREIGN_KEY_CHECKS = 1;
2.结果表建表sql脚本
/*
Navicat Premium Data Transfer
Source Server : windows1
Source Server Type : MySQL
Source Server Version : 50527
Source Host : localhost:3306
Source Schema : sqltest
Target Server Type : MySQL
Target Server Version : 50527
File Encoding : 65001
Date: 26/10/2019 20:46:39
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sales2
-- ----------------------------
DROP TABLE IF EXISTS `sales2`;
CREATE TABLE `sales2` (
`id` int(11) NULL DEFAULT NULL,
`brand` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`tdate` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
3.游标实现代码
drop PROCEDURE if EXISTS p3;
create procedure p3()
begin
declare id ,gt,it int;
declare brand varchar(15);
declare sdate,edate,mdate date;
declare flag int default 0;
-- 声明游标
declare cur cursor for select *,datediff(enddate,startdate) from sales;
declare continue handler for not found set flag = 1;
-- 打开游标
open cur;
-- 获取结果
l2:loop
fetch cur into id,brand,sdate,edate,gt;
if flag=1 then -- 当无法fetch会触发handler continue
leave l2;
end if;
-- 逻辑处理,拆解跨天
IF(gt>=0) THEN
set it=0;
while it<=gt DO
insert into sales2 values(id, brand,date_add(sdate,interval it day));
set it=it+1;
end while;
end if;
-- 关闭游标
end loop;
close cur;
end;
call p3();-- 不报错
4.查询结果表
select * from sales2;
5.最终需求结果
select brand,count(distinct tdate) as all_days from sales2 GROUP BY brand;