PHP SQL有一个问题

Question

This is my table, I should fetch the MAX (id) of each status_id.

id      status_id
10          1
11          1
12          2
13          2
14          2
15          4
16          4

So, I use this sql query, it works true and fetchs me all max ID.

select status_id, max(id) as max FROM `table` 
where status_id in (1,2,3,4) group by status_id

This sql command fetchs me 3 MAX id using while.

11, 14, 16....

You see, there is not any suitable id to 3rd status_id. And if there is not any suitable id to 3rd status_id just mark it as zero. So I want that sql will bring these results:

11, 14, 0, 16

Answer 1

You can create a subquery which basically has all the ID's you need and have a left join against it.

SELECT  a.status_ID,
        IFNULL(MAX(b.id), 0) maxVal
FROM    
        (
            SELECT 1 status_ID UNION ALL 
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4 
        ) a
        LEFT JOIN `table` b ON a.status_id = b.status_id
GROUP   BY a.status_id

• SQLFiddle Demo