试图显示前2个平均价格

Question

我试图显示前2个平均价格，但是我有一个问题，因为我需要显示的两列来自不同的表，当我尝试从其他线程解决方案时，连接给我错误消息。例如:

Customer Table          Order Table
CID,CNAME               OID,CID,OPRICE    
1,  JOHN                 1,  1,  2.50
2,  JEFF                 2,  1,  3.40
3,  ROB                  3,  2,  4.20
                         4,  3,  3.50

这是我目前得到的结果，但它显示了所有结果，而不仅仅是前2个。

SELECT CNAME,AVG(OPRICE)
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CID=ORDER.CID
GROUP BY CNAME;

我想要的结果是:

CNAME,AVG(OPRICE)
JEFF,  4.20
ROB,   3.50

Answer 1

首先，学习正确的join语法。永远不要在FROM子句中使用逗号。总是使用JOIN和ON。

然后，在Oracle 12C+中，您可以:

SELECT c.CNAME, AVG(o.OPRICE)
FROM CUSTOMER c JOIN
     ORDER o
     ON c.CID = o.CID
GROUP BY c.CNAME
ORDER BY AVG(o.OPRICE) DESC
FETCH FIRST 2 ROWS ONLY;

早期版本需要子查询:

SELECT CNAME, avg_oprice
FROM (SELECT c.CNAME, AVG(o.OPRICE) as avg_oprice
      FROM CUSTOMER c JOIN
           ORDER o
           ON c.CID = o.CID
      GROUP BY c.CNAME
      ORDER BY AVG(o.OPRICE) DESC
     ) c
WHERE rownum <= 2;

Answer 2

最近的Oracle版本有FETCH FIRST:

SELECT CNAME,AVG(OPRICE) as AVGPRICE
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CID=ORDER.CID
GROUP BY CNAME
ORDER BY AVGPRICE DESC
FETCH FIRST 2 ROWS ONLY