如何执行这个sql select查询?

I have the following table:

我有下表:

+----+-----------+-----+
| p1 | p2 | model| res |
+----+-----------+-----+
|  1 | a  | dog  |  1  |
|  2 | a  | dog  |  2  |
|  1 | b  | dog  |  3  |
|  2 | b  | dog  |  4  |
|  1 | a  | cat  |  10 |
|  2 | a  | cat  |  2  |
|  1 | b  | cat  |  10 |
|  2 | b  | cat  |  1  |
+----+-----------+-----+

What I want, is for each combination of p1 and p2, select the one that gets the lowest sum for res over for all models). The sum scores in this case are:

我想要的是，对于p1和p2的每一个组合，选择一个在所有模型中得到最小值的值。本例的总分为:

p1=1, p2=a: 1+10=11
p1=2, p2=a: 2+2=4
p1=1, p2=b: 3+10=13
p1=2, p2=b: 4+1=5

So I would like to get the following table:

所以我想要下表:

+----+-----------+-----+
| p1 | p2 | model| res |
+----+-----------+-----+
|  2 | a  | dog  |  2  |
|  2 | a  | cat  |  2  |
+----+-----------+-----+

Note: There may be more p columns (i.e p3, p4,...)

注意:可能有更多的p列(i)。e p3,p4,…)

What sql select query should I use in order to get the desired table above?

为了得到所需的表，我应该使用什么sql select查询?

2 个解决方案

#1

http://sqlfiddle.com/#!7/af26b/5

http://sqlfiddle.com/ ! 7 / af26b / 5

SELECT t1.*
FROM TEST t1
JOIN (
  SELECT p1, p2
  FROM TEST
  GROUP BY p1, p2
  ORDER BY SUM(res)
  LIMIT 1) t2 ON t1.p1 = t2.p1 AND t1.p2 = t2.p2

#2

Would something like this help?

有这样的帮助吗?

select * from the_table
group by model          # do it for each distinct model
order by res ascending  # to get the lower ones first so that limit can pick them
limit 1                 # for each group, i think it will do that.

#1