mysql - 没有周末的日期倒退

Question

我经常使用这个query1 返回可变日期的 6 天:

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;

在这个插入了序列的辅助表的帮助下。

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”开始，输出将是 ->

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

Answer 1

我们可以利用 DayName()函数获取对应于日期的星期几的名称。我们将利用此函数的结果来限制周末 NOT IN ('Saturday', 'Sunday')。

此外，我们还需要将数字生成器的范围增加到 10。因为我们有可能在 5 个工作日的两侧遇到 2 个周末(总共 4 天)。

因此，我们需要 2(第一对周末)+ 5(工作日)+ 2(第二对周末)+ 1(第 6 个工作日)= 10 个日期来考虑。这种极端情况的一个例子是输入日期是星期日。

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

模式(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 |

View on DB Fiddle

---

Edge Case Demo - 输入日期: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 |

View on DB Fiddle