I want to list grouped by month and   the days remaining to complete the course in the next column. The Course has   10 days.

Example data

ID Name Date

1 Sandy 2015-05-06

2 Candy 2015-05-06

3 Sandy 2015-05-28

4 Candy 2015-05-29

5 Candy 2015-06-01

Preferred output

| Name | Month | Attended | Remaining|

| Sandy| May   |   2      |     8    |

| Candy| May   |   2      |     8    |

| Candy| June  |   1      |     7    |

If I use GROUP BY DATE_FORMAT(date,   '%Y%m'), Name and try to do the calculation it does not work.

正确答案：

两句SQL:

SELECT Name, YEAR(DATE) AS YY, MONTH(DATE) AS MM, COUNT(*) AS Attended

FROM test

GROUP BY Name, YEAR(DATE), MONTH(DATE)

SET @currcount = NULL, @currvalue = NULL;

SELECT Name

    , YY

    , MM

    , Attended

    , @currcount   := IF(@currvalue = Name, @currcount, 10) - Attended AS Remaining

    , @currvalue   := Name AS dontcare

FROM (

    SELECT Name,   YEAR(DATE) AS YY, MONTH(DATE) AS MM, COUNT(*) AS Attended

    FROM test

    GROUP BY Name,   YEAR(DATE), MONTH(DATE)

) AS whatever

ORDER BY Name, YY, MM

A