我想通过SELECT语句在两个日期之间获得星期一,但是我只在SQL Server中搜索。
我已经尝试过,但是它不起作用:
SELECT DATE_ADD('2017-01-01', INTERVAL ROW DAY) AS Date
FROM (
SELECT @row := @row + 1 AS row
FROM (
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
) t1,
(
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
) t2,
(
SELECT @row:=-1
) t3 LIMIT 31
) b
WHERE DATE_ADD('2017-01-01', INTERVAL ROW DAY) BETWEEN '2017-01-01' AND '2017-12-31'
AND DAYOFWEEK(DATE_ADD('2017-01-01', INTERVAL ROW DAY)) = 2
此查询只给了我一个月的星期一日期。
我希望你能帮助我
问候!
如果您真的只想解决此SQL,则必须深入研究并生成几天的序列,这是迄今为止解决此问题的最困难的侧面任务。除此之外,正如您已经猜到的那样,您可以使用DAYOFWEEK
或WEEKDAY
来获得所需的日子。
SELECT *
FROM (
SELECT DATE_ADD('2013-01-01',
INTERVAL n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) AS DATE
FROM (
SELECT 0 AS num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) AS n1,
(
SELECT 0 AS num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) AS n2,
(
SELECT 0 AS num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) AS n3,
(
SELECT 0 AS num
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) AS n4
) AS a
WHERE DATE >= '2017-01-01' AND DATE < NOW()
AND WEEKDAY(DATE) = 0
ORDER BY DATE
其他一些RDBM对此具有iN构建功能。
如果您有一个已经存在所有日期的日历表,如果您的日历表很容易。
我认为该解决方案的性能比以前的
更好SET @date_start = date('2019-07-06');
SET @date_end = date('2019-09-18');
SELECT CASE WHEN MOD(DAYOFWEEK(@date_start), 7) <= 2 AND
MOD(DAYOFWEEK(@date_start), 7) + DATEDIFF(v.selected_date, @date_start) - 7 * FLOOR(DATEDIFF(v.selected_date, @date_start) / 7) >= 2 OR
MOD(DAYOFWEEK(@date_start), 7) > 2 AND
MOD(DAYOFWEEK(@date_start), 7) + DATEDIFF(v.selected_date, @date_start) - 7 * FLOOR(DATEDIFF(v.selected_date, @date_start) / 7) >= 9 THEN 1 ELSE 0 END
+ FLOOR(DATEDIFF(v.selected_date, @date_start) / 7) num_of_mo
, @date_start start_date,v.selected_date end_date
FROM (select adddate(@date_start,t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE v.selected_date <= @date_end;