题目如下：

求每个品牌的促销天数

表sale为促销营销表，数据中存在日期重复的情况，例如id为1的end_date为20180905，id为2的start_date为20180903，即id为1和id为2的存在重复的销售日期，求出每个品牌的促销天数(重复不算)

表结果如下：

+------+-------+------------+------------+|id|brand|start_date|end_date|+------+-------+------------+------------+|1|nike|2018-09-01|2018-09-05||2|nike|2018-09-03|2018-09-06||3|nike|2018-09-09|2018-09-15||4|oppo|2018-08-04|2018-08-05||5|oppo|2018-08-04|2018-08-15||6|vivo|2018-08-15|2018-08-21||7|vivo|2018-09-02|2018-09-12|+------+-------+------------+------------+

最终结果应为

建表语句

--------------------------------Tablestructureforsale------------------------------DROPTABLEIFEXISTS`sale`;CREATETABLE`sale`(`id`int(11)DEFAULTNULL,`brand`varchar(255)DEFAULTNULL,`start_date`dateDEFAULTNULL,`end_date`dateDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8;--------------------------------Recordsofsale------------------------------INSERTINTO`sale`VALUES(1,'nike','2018-09-01','2018-09-05');INSERTINTO`sale`VALUES(2,'nike','2018-09-03','2018-09-06');INSERTINTO`sale`VALUES(3,'nike','2018-09-09','2018-09-15');INSERTINTO`sale`VALUES(4,'oppo','2018-08-04','2018-08-05');INSERTINTO`sale`VALUES(5,'oppo','2018-08-04','2018-08-15');INSERTINTO`sale`VALUES(6,'vivo','2018-08-15','2018-08-21');INSERTINTO`sale`VALUES(7,'vivo','2018-09-02','2018-09-12');

方式1：

利用自关联下一条记录的方法

selectbrand,sum(end_date-befor_date+1)all_daysfrom(selects.id,s.brand,s.start_date,s.end_date,if(s.start_date>=ifnull(t.end_date,s.start_date),s.start_date,DATE_ADD(t.end_date,interval1day))asbefor_datefromsalesleftjoin(selectid+1asid,brand,end_datefromsale)tons.id=t.idands.brand=t.brandorderbys.id)tmpgroupbybrand

运行结果

+-------+---------+|brand|all_day|+-------+---------+|nike|13||oppo|12||vivo|18|+-------+---------+

该方法对本题中的表格有效，但对于有id不连续的品牌的记录时不一定适用。

方式2：

SELECTa.brand,SUM(CASEWHENa.start_date=b.start_dateANDa.end_date=b.end_dateANDNOTEXISTS(SELECT*FROMsalecLEFTJOINsaledONc.brand=d.brandWHEREd.brand=a.brandANDc.start_date=a.start_dateANDc.id<>d.idAND(d.start_dateBETWEENc.start_dateANDc.end_dateANDd.end_date>c.end_dateORc.start_dateBETWEENd.start_dateANDd.end_dateANDc.end_date>d.end_date))THEN(a.end_date-a.start_date+1)WHEN(a.id<>b.idANDb.start_dateBETWEENa.start_dateANDa.end_dateANDb.end_date>a.end_date)THEN(b.end_date-a.start_date+1)ELSE0END)ASall_daysFROMsaleaJOINsalebONa.brand=b.brandGROUPBYa.brand

运行结果

+-------+----------+|brand|all_days|+-------+----------+|nike|13||oppo|12||vivo|18|+-------+----------+

其中条件

d.start_dateBETWEENc.start_dateANDc.end_dateANDd.end_date>c.end_dateORc.start_dateBETWEENd.start_dateANDd.end_dateANDc.end_date>d.end_date

可以换成

c.start_date<d.end_dateAND(c.end_date>d.start_date)

结果同样正确

用分析函数同样可行的，自己电脑暂时没装oracle，用的mysql写的。