一个oracle sql查询，用于从属性中查找值

Suppose I have the following table 'player' in oracle database:

假设我在oracle数据库中有以下表'player'：

  P_ID P_NAME       C_ID      DEBUT      MATCH       RUNS

   101 amla          204       2003        190       5000
   102 mushi         200       2001        240       7500
   103 sakib         200       1999        150       5000
   104 ricky         205       1993        180       7000
   105 sachin        203       1990        250       8000
   106 yuvi          203       1999        150       6900

I need a query to display the c_id, total runs done by all batsmen of the country which has the maximum run scorer. (in this case maximum run scorer is sachin. so the query should return : c_id = 203, runs = 14900).

我需要一个查询来显示c_id，这个国家的所有击球手都有最大的得分手。（在这种情况下，最大运行记分器是sachin。因此查询应该返回：c_id = 203，runs = 14900）。

I have only been able to find the maximum run scorer and the country which he belongs to. the query: select c_id, runs from player where runs = (select max(runs) from player); does that. However, I am not been able to proceed further.

我只能找到最大的得分手和他所属的国家。查询：选择c_id，从运行中的运行器运行=（从运动员中选择最大值（运行））;那样做。但是，我无法继续下去。

1 个解决方案

#1

When you need condition on aggregate function, you must use sub-query or having clause. This return always one row, but it's wrong when more then one groups have same total:

当您需要聚合函数的条件时，您必须使用子查询或having子句。这总是返回一行，但是当多于一个组具有相同的总数时，这是错误的：

SELECT * 
FROM (
  SELECT c_id, Sum(runs) total 
  FROM player 
  GROUP BY c_id
  ORDER BY total DESC 
) WHERE ROWNUM =1;

But you can get all using this:

但你可以使用这个：

SELECT c_id, Sum(runs) total 
FROM player 
GROUP BY c_id 
HAVING Sum(runs) = (
  SELECT Max(t) from (
    SELECT Sum(runs) t 
    FROM player 
    GROUP BY c_id))
;

#1