MySQL group and skip grouping by null values

Question

select * from dc_deal group by collection_id

In the collection_id column, I have values (1,3,3,4,4,5, NULL, NULL) . Above the query will return rows with (1,2,3,4, NULL) , but I want to skip the grouping by NULL and get the result as (1,2,3,4, NULL, NULL)

Answer 1

If you have a unique column (or set of columns) in the table, you can simply add another expression to GROUP BY. The expression must return a unique value for each line when collection_idnull, otherwise a constant is returned.

, id , - :

... GROUP BY collection_id, IF(collection_id IS NULL, id, 0)

GROUP BY , collection_id , , collection_id null. ( , id , , PRIMARY KEY - . , , .

... GROUP BY collection_id
           , IF(collection_id IS NULL, col1, '')
           , IF(collection_id IS NULL, col2, NULL)
           , IF(collection_id IS NULL, col3, collection_id)

Answer 2

:

SELECT * FROM dc_deal 
GROUP BY collection_id, 
case WHEN collection_id IS NULL THEN ID ELSE 0 END

ID , .

. SQL Fiddle.