数据如下:
id sdate edate
1 2019-02-03 2019-02-08
1 2019-02-01 2019-02-05
1 2019-02-10 2019-02-15
2 2019-02-22 2019-02-28
2 2019-02-02 2019-02-06
2 2019-02-15 2019-02-21
需要的结果是:
1 14
2 19
需求:
求出不同id占用的天数,如果咱用日期重复则不算,否则相加。如实例id为1的数据
01--05,03--08,其中,03,04,05重复不算,算01--08的天数为8,10--15天数为5,结果为14
id为2的数据
02--06天数为5,15--21天数为7,22--28天数为7,结果为15
id sdate edate
1 2019-02-03 2019-02-08
1 2019-02-01 2019-02-05
1 2019-02-10 2019-02-15
2 2019-02-22 2019-02-28
2 2019-02-02 2019-02-06
2 2019-02-15 2019-02-21
需要的结果是:
1 14
2 19
需求:
求出不同id占用的天数,如果咱用日期重复则不算,否则相加。如实例id为1的数据
01--05,03--08,其中,03,04,05重复不算,算01--08的天数为8,10--15天数为5,结果为14
id为2的数据
02--06天数为5,15--21天数为7,22--28天数为7,结果为15
如果information_schema.columns的条数不够,你可以换一个你有的,数据量足够多的表。
information_schema.columns表是做什么用的,你可以百度一下。
直接执行以下语句即可。
SELECT t.id,COUNT(DISTINCT tt.dd) c FROM (
SELECT '1' id ,'2019-02-03' sdate,'2019-02-08' edate FROM DUAL UNION ALL
SELECT '1','2019-02-01','2019-02-05' FROM DUAL UNION ALL
SELECT '1','2019-02-10','2019-02-15' FROM DUAL UNION ALL
SELECT '2','2019-02-22','2019-02-28' FROM DUAL UNION ALL
SELECT '2','2019-02-02','2019-02-06' FROM DUAL UNION ALL
SELECT '2','2019-02-15','2019-02-21' FROM DUAL ) t
LEFT JOIN ( SELECT t.d,DATE_ADD(t.d, INTERVAL (@R:=@R+1)-1 DAY) dd FROM (
SELECT (SELECT MIN(t.sdate) d FROM (
SELECT '1' id ,'2019-02-03' sdate,'2019-02-08' edate FROM DUAL UNION ALL
SELECT '1','2019-02-01','2019-02-05' FROM DUAL UNION ALL
SELECT '1','2019-02-10','2019-02-15' FROM DUAL UNION ALL
SELECT '2','2019-02-22','2019-02-28' FROM DUAL UNION ALL
SELECT '2','2019-02-02','2019-02-06' FROM DUAL UNION ALL
SELECT '2','2019-02-15','2019-02-21' FROM DUAL ) t ) d ,@R:=0
FROM information_schema.columns ) t) tt ON tt.dd BETWEEN t.sdate AND t.edate
GROUP BY t.id ;
`id` int(10) DEFAULT NULL,
`sdate` date DEFAULT NULL,
`edate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;实现功能如下:select id,count(DISTINCT date) as num from mydate t1 ,(
SELECT
@s :=@s + 1 as 'index',
DATE(DATE_SUB(t3.edate, INTERVAL @s DAY)) AS 'date'
FROM mysql.help_topic,(SELECT @s := -1) temp,
( select min(sdate) sdate,max(edate) edate from mydate) t3
where @s < (t3.edate - t3.sdate)
) t2
where t2.date between t1.sdate and t1.edate
group by id
;