mysql 日期 null_如何将NULL视为最大日期而不是在MySQL中忽略它？

Lets say that I have a table named test like this:

ID DATE

1 '2013-01-26'

1 NULL

1 '2013-03-03'

2 '2013-02-23'

2 '2013-04-12'

2 '2013-05-02'

And I would like to get from this table :

ID DATE

1 NULL

2 '2013-05-02'

Here is my query:

select ID, max(DATE)

from test

group by ID

Problem is that MYSQL ignores NULL values and returns me

ID DATE

1 '2013-03-03'

2 '2013-05-02'

How can i do so when there is a NULL it takes the MAX as NULL?

解决方案

Give this a shot:

SELECT ID, case when MAX(DATE IS NULL) = 0 THEN max(DATE) END AS DATE

FROM test

GROUP BY ID;