我在 MySQL 中有一個表，其中包含包含 astart_date和 an 的記錄end_date. 我想知道在這兩個日期之間的時間段內，一個月的第一天和第十五天出現了多少次。
即，如果start_date是 2021-07-28 和end_date2021-10-21，則結果應為 6，因為所有這些日期都包含在該期間內：
2021-08-01
2021-08-15
2021-09-01
2021-09-15
2021-10-01
2021-10-15
知道我怎么能做到這一點嗎？謝謝
uj5u.com熱心網友回復：
select count(day_of_month) from (select day(single_date) as day_of_month from
(select (select end_date from test_table where id=1) - INTERVAL (a.a (10 * b.a) (100 * c.a)) DAY as single_date
from (select 0 as a 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 a
cross join (select 0 as a 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 b
cross join (select 0 as a 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 c
) all_days, test_table
where all_days.single_date >= test_table.start_date and all_days.single_date <= test_table.end_date and test_table.id=1) all_day_of_month where (day_of_month = 1 or day_of_month = 15);
SQL 小提琴鏈接，它為給定的輸入回傳正確的輸出 6 http://www.sqlfiddle.com/#!9/8054d5/19/0
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/534419.html
標籤：数据库
上一篇：MySQL：從字串中獲取ID（分解）并在NOTIN子句中使用它
下一篇：為什么我在嘗試與sequelize、mysql和Nodejs實作一對多關系時收到“UnhandledPromiseRejectionWarning:E??rroratQuery.run”？