oracle显示购买了最多汽车的客户，没有分析功能

Question

我目前正在尝试回答以下问题:

显示从Archie 's Luxury Motors购买汽车最多的顾客的名字。

Tables I'm working with:

客户

(custID, name, DOB, streetAddress, suburb, postcode,
gender, phoneNo, email, type)

SalesTransaction

(VIN, custID, agentID, dateOfSale, agreedPrice)

我查询

select * 
from (
select customer.name
from customer, salestransaction
where customer.custID = salestransaction.custID
group by (customer.name), salestransaction.custID
order by count(*) desc
)
where rownum=1;

现在我发现我不能使用解析函数(rownum &排名)

如果只使用纯事务性SQL，我该怎么做呢?

Answer 1

可以使用MAX和COUNT聚合函数:

WITH data AS
  (SELECT c.name cust_nm,
    COUNT(*) cnt
  FROM customer c,
    salestransaction s
  WHERE c.custID = s.custID
  GROUP BY c.NAME
  ORDER BY cnt DESC
  )
SELECT cust_nm FROM data WHERE cnt =
  (SELECT MAX(cnt) FROM DATA
  );

EMP和DEPT表中的一个示例:

SQL> WITH data AS
  2    (SELECT e.deptno,
  3      COUNT(*) cnt
  4    FROM emp e,
  5      dept d
  6    WHERE e.deptno = d.deptno
  7    GROUP BY e.deptno
  8    ORDER BY cnt DESC
  9    )
 10  SELECT deptno FROM DATA WHERE cnt =
 11    (SELECT MAX(cnt) FROM DATA
 12    );
    DEPTNO
----------
        30
SQL>