MySQL中的GROUP BY特征 [英] GROUP BY characteristic in mysql

问题描述

我有以下SQL表和此sqlfiddle中所示的查询: http://sqlfiddle.com/#!2/37eda/1/0 .

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

当前结果如下:

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

我需要将查询限制为每个条目仅显示一个definition_id.如果有两个definition_id，则应将其与非NULL service_id一起使用.正确的结果应该是:

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

这里正确的SQL查询是什么?

What would be the correct SQL query here?

推荐答案

尝试用子选择先对它们进行排序，然后对它们进行分组，常规group by将使用记录的第一个出现位置，因此使其像第一个ORDER BY service_id DESC然后使用分组依据

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 

小提琴

这篇关于MySQL中的GROUP BY特征的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！