mysql - 获取当月的所有日期

Question

我有 table

userID | date | time
===================
1 | 2015-02-08 | 06:32
1 | 2015-02-08 | 05:36
1 | 2015-02-08 | 17:43
1 | 2015-02-08 | 18:00
1 | 2015-02-09 | 06:36
1 | 2015-02-09 | 15:43
1 | 2015-02-09 | 19:00
1 | 2015-02-10 | 05:36
1 | 2015-02-10 | 17:43
1 | 2015-02-10 | 18:00
2 | 2015-02-08 | 06:32
2 | 2015-02-08 | 05:36
2 | 2015-02-08 | 17:43
2 | 2015-02-08 | 18:00
2 | 2015-02-09 | 06:36
2 | 2015-02-09 | 15:43
2 | 2015-02-09 | 19:00
2 | 2015-02-10 | 05:36
2 | 2015-02-10 | 17:43
2 | 2015-02-10 | 18:00

但我希望返回的记录数与当月的天数完全相同，并获得最短输入时间和最长时间输出。如果当月有28天并且只有两条记录应该带上:

userID | date | in | out
========================
1 | 2015-02-01 | |
1 | 2015-02-02 | |
1 | 2015-02-03 | |
1 | 2015-02-04 | |
1 | 2015-02-05 | |
1 | 2015-02-06 | |
1 | 2015-02-07 | |
1 | 2015-02-08 | 06:32 | 18:00
1 | 2015-02-09 | 06:36 | 19:00
1 | 2015-02-10 | 05:36 | 18:00
1 | 2015-02-11 | |
1 | 2015-02-12 | |
1 | 2015-02-13 | |
1 | 2015-02-14 | |
1 | 2015-02-15 | |
1 | 2015-02-16 | |
1 | 2015-02-17 | |
1 | 2015-02-18 | |
1 | 2015-02-19 | |
1 | 2015-02-20 | |
1 | 2015-02-21 | |
1 | 2015-02-22 | |
1 | 2015-02-23 | |
1 | 2015-02-24 | |
1 | 2015-02-25 | |
1 | 2015-02-26 | |
1 | 2015-02-27 | |
1 | 2015-02-28 | |

如何修改我的查询以获得上述结果？
这是我的查询:

$sql = "SELECT 
                    colUserID, 
                    colDate, 
                    if(min(colJam) < '12:00:00',min(colJam), '') as in, 
                    if(max(colJam) > '12:00:00',max(colJam), '') as out
                FROM tb_kehadiran
                    WHERE colDate > DATE_ADD(MAKEDATE($tahun, 31),
                    INTERVAL($bulan-2) MONTH)
                    AND
                    colDate < DATE_ADD(MAKEDATE($tahun, 1),
                    INTERVAL($bulan) MONTH)
                    AND
                    colUserID = $user_id
        GROUP BY colUserID,colDate";

Answer 1

我不得不考虑这个。但可能是迄今为止最简单的答案:

WITH AllMonthDays as (
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM AllMonthDays WHERE n + 1 <= DAY(EOMONTH(GETDATE())) 
)

SELECT 
    DISTINCT datefromparts(YEAR(GETDATE()), MONTH(GETDATE()), n) As dates
    , MIN(d.time) as 'In'
    , MAX(d.time) as 'Out'
FROM AllMonthDays as A
    LEFT OUTER JOIN
    table as d on 
        DAY(d.date) = A.n
GROUP BY n,(d.date);

--- 在此环境中测试和尝试:---

use Example;
CREATE TABLE demo (
     ID int identity(1,1)
    ,date date
    ,time time
    );

INSERT INTO demo (date, time) VALUES 
    ('2015-12-08', '06:32'),
    ('2015-12-08', '05:36'),
    ('2015-12-08', '17:43'),
    ('2015-12-08', '18:00'),
    ('2015-12-09', '06:36'),
    ('2015-12-09', '15:43'),
    ('2015-12-09', '19:00'),
    ('2015-12-10', '05:36'),
    ('2015-12-10', '17:43'),
    ('2015-12-10', '18:00')
    ;

WITH AllMonthDays as (
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM AllMonthDays WHERE n + 1 <= DAY(EOMONTH(GETDATE())) 
)

SELECT 
    DISTINCT datefromparts(YEAR(GETDATE()), MONTH(GETDATE()), n) As dates
    , MIN(d.time) as 'In'
    , MAX(d.time) as 'Out'
FROM AllMonthDays as A
    LEFT OUTER JOIN
    demo as d on 
        DAY(d.date) = A.n
GROUP BY n,(d.date);

DROP table demo;