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;

それに応じて、まったく同じものを更新ステートメントにプラグインできます。

もちろん、曜日をサブクエリ/サブテーブルに入れて、曜日ごとに数式を繰り返さないようにすると、はるかに短く/簡単にすることができますが、ここではこの演習を行います。;-)大変な作業は、主に特定の平日の残りを計算する方法を理解することでした。