mysql - SQL - 使用有序组的前 N ​​行的 SUM 创建 View

Question

我的 MYSQL 数据库中有这个表，我想创建一个 View 作为标题。表:

ID | Result
---+-------
1  | 10
3  | 7
1  | 4
2  | 5
1  | 3

View 应按 ID、SUM(结果) 选择分组，并仅考虑每个 ID 的最佳 2 个结果，并按结果对所有内容进行排序。所以..查看:

ID | Result
---+--------
1  | 14
3  | 7
2  | 5

我尝试过:

CREATE OR REPLACE VIEW final_res AS 
SELECT  `ID` , SUM(  `result` ) 
FROM  `result_table`
GROUP BY  `ID`
HAVING COUNT(*)<=2
ORDER BY `result`

但这行不通。 :/

Answer 1

一种选择是在按结果 desc 排序的每个组中使用用户定义变量创建行号，然后限制总和 > 到每组中的前 2 行:

select id, sum(result)
from (
  select id, result, @rn:=if(@prevId=ID,@rn+1,1) rn, @prevId:=id
  from result_table join (select @rn:=0, @previd:=0) t
  order by id, result desc
  ) t
where rn <= 2
group by id
order by 2 desc

• SQL Fiddle Demo

<小时/>

或者，您可以使用相关子查询，但我认为行号方法更简洁:

select t.id, max(t.result) + coalesce((
  select max(t2.result)
  from result_table t2 
  where t.id = t2.id and t.result != t2.result
  ),0)
from result_table t
group by id
order by 2 desc

• Additional Fiddle