如何使用SQL知道一个月中剩余的星期一，星期二，星期三，...和星期日的数量？

我使用MySQL测试了该解决方案，但是它可以在任何SQL引擎中使用。

测试数据：

CREATE TABLE test_date (
  date date NOT NULL
);

INSERT INTO test_date VALUES
('2019-03-01'),
('2019-03-20'),
('2019-03-21'),
('2019-03-22'),
('2019-03-23'),
('2019-03-24'),
('2019-03-25'),
('2019-03-26');

查询以获取您的数据：

SELECT 
    td2.date,
    /* 
    To calculate remaining of a weekday, you can then use this formula:

    if desired_week_day_number > week_day_number
       and desired_week_day_number <= (week_day_number + remaining_days_after_weeks):
        remaining_weeks + 1
    elseif desired_week_day_number <= (week_day_number + remaining_days_after_weeks - 7):
        remaining_weeks + 1
    else remaining_weeks
    */
    CASE WHEN (1 > td2.week_day_number AND 1 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 1 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_sundays,
    CASE WHEN (2 > td2.week_day_number AND 2 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 2 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_mondays,
    CASE WHEN (3 > td2.week_day_number AND 3 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 3 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_tuesdays,
    CASE WHEN (4 > td2.week_day_number AND 4 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 4 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_wednesdays,
    CASE WHEN (5 > td2.week_day_number AND 5 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 5 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_thursdays, 
    CASE WHEN (6 > td2.week_day_number AND 6 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 6 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_fridays,
    CASE WHEN (7 > td2.week_day_number AND 7 <= (td2.week_day_number + td2.remaining_days_after_weeks))
              OR 7 <= (td2.week_day_number + td2.remaining_days_after_weeks - 7)
         THEN td2.remaining_weeks + 1
         ELSE td2.remaining_weeks
    END remaining_saturdays,
    td2.*
FROM (
    SELECT
        td.date,
        day(td.date) day_number,
        dayofweek(td.date) week_day_number,
        dayname(td.date) week_day_name,
        day(last_day(td.date)) nb_month_days,
        day(last_day(td.date)) - day(td.date) remaining_days,
        floor((day(last_day(td.date)) - day(td.date)) / 7) remaining_weeks,
        day(last_day(td.date)) - day(td.date) -
            floor((day(last_day(td.date)) - day(td.date)) / 7) * 7 remaining_days_after_weeks
    FROM test_date td
) AS td2
ORDER BY td2.date;

您可以将完全相同的内容插入更新语句中。

如果您将工作日放在子查询/子表中，当然可以使其更短/更简单，从而避免在一周的每一天重复该公式，但是我现在将此练习交给您。;-)的工作主要是弄清楚如何计算给定工作日的剩余时间。