The data is as follows:
Id sdate edate
1 2019-02-03 2019-02-08
1 2019-02-01 2019-02-05
1 2019-02-10 2019-02-15
The 2019-02-22 2019-02-28
The 2019-02-02 2019-02-06
The 2019-02-15 2019-02-21
The result is:
1
142 19
Requirements:
Calculate different id to take up the number of days, if I repeat, not with date, otherwise, such as instance id data for 1
01-05 01-08, among them, 03,04,05 repeat not, calculate a 01 -- 08 the number of days for 8, 10, 15 days for 5, the results of 14
Id data for 2
02-6 days for 5, 15, 21 days of 7, 22, 28 days for 7, the results of 15

CodePudding user response:

I don't have your watch, it built a temporary table,
If information_schema. The columns of the number is not enough, you can replace a, you have enough quantity of the data table,
Information_schema. What do you use the columns list, you can baidu
Perform the following statements can be directly,
The SELECT t.i d, COUNT (DISTINCT tt. Dd) c FROM (
SELECT '1' id, sdate '2019-02-03', '2019-02-08' edate FROM DUAL UNION ALL
SELECT '1', '2019-02-01', '2019-02-05' the FROM DUAL UNION ALL
SELECT '1', '2019-02-10', '2019-02-15' the FROM DUAL UNION ALL
SELECT '2', '2019-02-22', '2019-02-28' the FROM DUAL UNION ALL
SELECT '2', '2019-02-02', '2019-02-06' the FROM DUAL UNION ALL
SELECT '2', '2019-02-15', '2019-02-21' the FROM DUAL) t
LEFT the JOIN (SELECT t.d, DATE_ADD (t.d, INTERVAL (@ R:=@ R + 1) - 1 DAY) dd FROM (
SELECT (SELECT MIN (t.s date) d the FROM (
SELECT '1' id, sdate '2019-02-03', '2019-02-08' edate FROM DUAL UNION ALL
SELECT '1', '2019-02-01', '2019-02-05' the FROM DUAL UNION ALL
SELECT '1', '2019-02-10', '2019-02-15' the FROM DUAL UNION ALL
SELECT '2', '2019-02-22', '2019-02-28' the FROM DUAL UNION ALL
SELECT '2', '2019-02-02', '2019-02-06' the FROM DUAL UNION ALL
SELECT '2', '2019-02-15', '2019-02-21') FROM DUAL) t d, @ R:=0
The FROM information_schema. Columns) t) tt ON tt. Dd BETWEEN t.s date AND t.e date
GROUP BY t.i d;

CodePudding user response:

I built a list:

 
The CREATE TABLE ` mydate ` (
` id ` int (10) DEFAULT NULL, 
` sdate ` date DEFAULT NULL, 
` edate ` date DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Functions are as follows:

 
Select id, count (DISTINCT date) as num from mydate t1, (
SELECT 
@ s:=@ s + 1 as' index ', 
The DATE (DATE_SUB (t3) edate, INTERVAL @ s DAY)) AS a 'DATE' 
The FROM mysql. Help_topic (SELECT @ s:=1) temp, 
(select min (sdate) sdate, Max (edate) edate from mydate) t3 
Where @ s & lt; (t3. Edate - t3. Sdate) 
T2) 
Where t2. The date between t1. Sdate and t1, edate 
Group by id 
; 

CodePudding user response:

This SQL is very difficult

CodePudding user response:

To give you an idea of virtual table: first, the date of the continuous, covering all your date, then grouping statistics, to weigh the number of days after (you give the time, the number of days in virtual list)

CodePudding user response:

SELECT id, the SUM (DATEDIFF (edate, sdate)) days FROM the table name GROUP BY id.