mysql获取当月上月未处理的所有记录

Question

我有一个包含以下记录的表

slno | FirstName | LastName | Attended | Month  | Year |
-----|-----------|----------|----------|--------|------|
1    |  John     |  Smith   |   0      |   04   | 2017 |
2    |  Jack     |  Paul    |   1      |   04   | 2017 |
3    |  Jerry    |   Tom    |   1      |   04   | 2017 |
4    |  Beth     |  Home    |   0      |   04   | 2017 |
5    |  Julia    |  John    |   1      |   04   | 2017 |
6    |  Cris     |  joy     |   0      |   05   | 2017 |
7    |  Meiy     |  Rony    |   1      |   05   | 2017 |
8    |  Ronald   |   Jony   |   1      |   05   | 2017 |
9    |  Quest    |  Pio     |   0      |   05   | 2017 |
10   |  Mary     |  June    |   1      |   05   | 2017 |
11   |  Joy      |  Meth    |   0      |   03   | 2017 |
12   |  Ruby     |  Rony    |   1      |   03   | 2017 |
13   |  Royal    |   Jony   |   1      |   03   | 2017 |
14   |  Tweet    |  Pio     |   0      |   03   | 2017 |
15   |  Min      |  June    |   1      |   03   | 2017 |

我想显示基于月份和年份的数据。例如:04 2017 应该只显示 4 月份的数据，而参加的 3 月份=0。但是当我搜索 05 2017 时，它应该显示 5 月份的数据，包括前几个月的数据，即 Attended=0 [3 月和 4 月]。

我有以下声明:

select FirstName,LastName,Attended from UserTable 
where Attended in (0,1)
and ( Attended not in (1) or (Month='04' and Year='2017'));

我会得到我想要的记录。但是，如果我搜索 Month='03' Year='2017' ，它会显示 5 月和 4 月的所有 Attended=0。

if   Month='05' year='2017' -- current month

    6    |  Cris     |  joy     |   0      |   05   | 2017 |
    7    |  Meiy     |  Rony    |   1      |   05   | 2017 |
    8    |  Ronald   |   Jony   |   1      |   05   | 2017 |
    9    |  Quest    |  Pio     |   0      |   05   | 2017 |
    10   |  Mary     |  June    |   1      |   05   | 2017 |
    1    |  John     |  Smith   |   0      |   04   | 2017 |
    4    |  Beth     |  Home    |   0      |   04   | 2017 |
    11   |  Joy      |  Meth    |   0      |   03   | 2017 |
    14   |  Tweet    |  Pio     |   0      |   03   | 2017 |

如果月='04' 年='2017'

1    |  John     |  Smith   |   0      |   04   | 2017 |
2    |  Jack     |  Paul    |   1      |   04   | 2017 |
3    |  Jerry    |   Tom    |   1      |   04   | 2017 |
4    |  Beth     |  Home    |   0      |   04   | 2017 |
5    |  Julia    |  John    |   1      |   04   | 2017 |
11   |  Joy      |  Meth    |   0      |   03   | 2017 |
14   |  Tweet    |  Pio     |   0      |   03   | 2017 |

如果月='03' 年='2017'

11   |  Joy      |  Meth    |   0      |   03   | 2017 |
12   |  Ruby     |  Rony    |   1      |   03   | 2017 |
13   |  Royal    |   Jony   |   1      |   03   | 2017 |
14   |  Tweet    |  Pio     |   0      |   03   | 2017 |
15   |  Min      |  June    |   1      |   03   | 2017 |

我该怎么做？

Answer 1

您可以通过将月份和年份解析为日期来执行此操作，然后像这样查询:

SELECT * FROM your_table
WHERE
('05' = `Month` AND '2017' = `Year`) 
OR
(
    '05-2017' >= DATE_FORMAT(STR_TO_DATE(CONCAT(`Mont`, '-', `Year`), '%m-%Y'), '%m-%Y')
    AND
    Attended = 0
);