我在获取相关字段(provider_state
)的平均值max
时遇到问题。
我有一张桌子。其中average_total_payments
付款字段。每一行也有状态字段作为provider_state
。从下面的查询中,我得到了Avg
的Max
,但我没有得到相关的provider_state
。
此查询的主要目的是为我提供具有最大平均付款(包括付款金额)的州的名称。
mysql_query
SELECT provider_state
,MAX(average) AS max_average
FROM ( SELECT provider_state
,AVG(average_total_payments) AS average
FROM gnomics
WHERE drg_definition LIKE '%$search%'
GROUP BY provider_state ) AS abc
请给我建议,获得相关字段的最佳方式是什么?
如果你的问题是"给我一个有最高平均付款的州的名字,包括付款金额。 然后你可以这样解决它:
SELECT provider_state, AVG(average_total_payments) AS average
FROM gnomics
WHERE drg_definition LIKE '%$search%'
GROUP BY provider_state
ORDER BY average desc
LIMIT 1;
如果我理解这个问题,那么:
SELECT provider_state, field1, field2 ....
,MAX(average) AS max_average
FROM ( SELECT provider_state, field1, field2 ....
,AVG(average_total_payments) AS average
FROM gnomics
WHERE drg_definition LIKE '%$search%'
GROUP BY provider_state, field1, field2 ....
) AS abc
应该做这项工作