oracle的初始情况(子查询内容)

问题描述

对于Oracle，在这些表中，

For Oracle,From these tables,

表1 CUSTOMER : has cust_fname, cust_lname, cust_id

Table 1 CUSTOMER : has cust_fname, cust_lname, cust_id

表2 SALESORDER : has so_number, so_custid

Table 2 SALESORDER : has so_number, so_custid

表3 ITEM : has item_qty, item_sonum

Table 3 ITEM : has item_qty, item_sonum

while CUST_ID = SO_CUSTID and SO_NUMBER = ITEM_SONUM (FK relationship)

while CUST_ID = SO_CUSTID and SO_NUMBER = ITEM_SONUM (FK relationship)

我想显示全名的客户名称(意思是cust_fname + cust_lname)，而这个客户(只有一位)是订购商品数量最多的客户(意思是必须与item_qty做些事情).

I want to show customer name with full name (means cust_fname+cust_lname) whereas this customer (just one) is the one who had ordered the highest numbers of items (means has to do something with item_qty).

如何为该任务编写代码?

How can I write a code for this task?

谢谢

推荐答案

尝试

select cust_fname, cust_lname
from
(select c.cust_fname, c.cust_lname
from customer c join salesorder so on so.so_custid = c.cust_id
join ITEM i on i.item_sonum = so.so_number
group by c.cust_fname, cust_lname
order by sum(i.item_qty) desc)
where rownum = 1

这是一个sqlfiddle演示

select cust_fname, cust_lname
from
(select c.cust_fname, c.cust_lname, rank() over (order by sum(i.item_qty) desc) rnk
from customer c join salesorder so on so.so_custid = c.cust_id
join ITEM i on i.item_sonum = so.so_number
group by c.cust_fname, cust_lname
)
where rnk = 1;

这篇关于oracle的初始情况(子查询内容)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！