不带周末的日期回退 [英] Throwback in dates without Weekends

问题描述

我经常使用此query1在可变日期返回6天:

I often use this query1 to go back 6 days on a variable date:

query1 = SELECT
    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
    FROM `HELPER_SEQ`
    AS S WHERE S.`I` <= 6;

借助此辅助表并在其中插入了序列.

With the help of this auxiliary table with a sequence inserted there.

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES (1),(2),(3),(4),(5),(6);

问题:

如何避免使用query1在周末工作日? 以这样的方式，输出将在6个可用日内返回，在此示例中，从'2018-11-21'开始，输出将是->

How can I avoid output weekends days using query1? In a such way that the output go back in 6 useful days, in this example starting on '2018-11-21' the output would be ->

| 2018-11-21 | 
| 2018-11-20 |
| 2018-11-19 | 
| 2018-11-16 | 
| 2018-11-15 | 
| 2018-11-14 |

推荐答案

我们可以利用此外，我们将需要将生成器的数量范围增加到10 .因为我们有可能在5个工作日的任一侧遇到2个周末(共4天).

Also, we will need to increase the number generator range upto 10. Because there is a possibility that we can come across 2 weekends (total 4 days) on either side of 5 weekdays.

因此，我们需要2个(第一对周末)+ 5个(工作日)+ 2个(第二对周末)+ 1个(第六个工作日)= 10个要考虑的日期.例如，当输入日期为星期日时，就会出现这种情况.

So, we need 2 (first pair of weekend days) + 5 (weekdays) + 2 (second pair of weekend days) + 1 (6th weekday) = 10 dates to consider. An example of this edge case would be when an input date is Sunday.

在非边缘情况下，我们将需要使用LIMIT 6将结果限制为最多6天.

We will need to use LIMIT 6 to restrict the result upto 6 days only, in the non-edge cases.

模式(MySQL v5.7)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

查询

SELECT
    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
FROM `HELPER_SEQ` AS S 
WHERE S.`I` <= 10 
  AND DAYNAME(DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')
ORDER BY VAR DESC
LIMIT 6;

结果

| VAR        |
| ---------- |
| 2018-11-21 |
| 2018-11-20 |
| 2018-11-19 |
| 2018-11-16 |
| 2018-11-15 |
| 2018-11-14 |

在数据库小提琴上查看

边缘案例演示-输入日期:2018年11月25日(星期日)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

查询#2

SELECT
    DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
FROM `HELPER_SEQ` AS S 
WHERE S.`I` <= 10 
  AND DAYNAME(DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')
ORDER BY VAR DESC
LIMIT 6;

结果

| VAR        |
| ---------- |
| 2018-11-23 |
| 2018-11-22 |
| 2018-11-21 |
| 2018-11-20 |
| 2018-11-19 |
| 2018-11-16 |

在DB Fiddle上查看

这篇关于不带周末的日期回退的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！