如何修改SQL语句仅展示Calendar_table中每月15日和30日数据？

Hey there! Let's figure out how to pull exactly the 15th and 30th of each month from your Calendar_table—and we'll also fix up that original last-day query you had for good measure.

First, let's address a quick issue with your current query for monthly last days: the subquery doesn't group by year and month, so it only returns the single latest date across the entire table, not the last day of each individual month. But let's focus on your actual goal first.

Basic Working Query #

To get all rows where the date is the 15th or 30th of any month, you can use the DAY() function (note: if you're using PostgreSQL, use EXTRACT(DAY FROM date) instead; for MySQL/SQL Server, DAY(date) works). This will automatically skip any non-existent dates like February 30th (since those won't be in your calendar table anyway):

SELECT * 
FROM Calendar_table 
WHERE DAY(date) IN (15, 30);

Handle Duplicate Entries (If Needed) #

If your table has multiple entries for the same date, and you want only one unique entry per (year, month, day), add DISTINCT or group by the date and other columns:

-- Option 1: Use DISTINCT to get unique rows
SELECT DISTINCT * 
FROM Calendar_table 
WHERE DAY(date) IN (15, 30);

-- Option 2: Group by date and other columns (better for aggregated data)
SELECT date, your_other_columns_here
FROM Calendar_table 
WHERE DAY(date) IN (15, 30)
GROUP BY date, your_other_columns_here;

Fixed Version of Your Original Last-Day Query #

Just in case you need this later, here's how to correctly get the last day of each month by grouping by year and month:

SELECT * 
FROM Calendar_table 
WHERE date IN (
    SELECT MAX(date) 
    FROM Calendar_table 
    GROUP BY YEAR(date), MONTH(date)
);

内容的提问来源于stack exchange，提问作者Girish