mysql group by 特性,MySQL中的GROUP BY特征

I have the following SQL table and query as shown in this sqlfiddle: http://sqlfiddle.com/#!2/37eda/1/0.

The current result look like this:

id definition_id service_id provider_id amount

2 1 25 24 200.00

3 1 NULL 24 300.00

20 3 25 24 700.00

30 4 NULL 24 800.00

I need to restrict the query to only show one definition_id per entry. If there are two definition_ids, it should use the one with the non-NULL service_id. The correct result should be:

id definition_id service_id provider_id amount

2 1 25 24 200.00

20 3 25 24 700.00

30 4 NULL 24 800.00

What would be the correct SQL query here?

解决方案

Try with a subselect first order them then group them,normal group by will use the first occurrence of the record , So make it like first ORDER BY service_id DESC then use group by

SELECT t.* FROM (

select * from billing_billingmatrix

where (provider_id=24

or provider_id is null)

and (service_id=25 or service_id is null)

ORDER BY service_id DESC

) t GROUP BY t.definition_id