时间戳

首页 > 解决方案 > Oracle子查询同时使用count和max与join

oracle - Oracle子查询同时使用count和max与join

问题描述

表COMPUTER：

表SUPPLIER：

如何显示拥有最多计算机的建筑物位置？我一直在尝试几种方法，包括子查询、连接、最大值、计数，但所有方法都不起作用并且错误不断发生

我追求的结果是

SUPPID SNAME           SADDRESS             MAKE                   COUNT(*)

   125 Apple Sdn.Bhd   18 Jalan Duta        Apple                         3

标签： oraclejoincountsubquerymax

解决方案

例如（样本数据在第 1 - 12 行；您可能感兴趣的查询从第 13 行开始）：

SQL> with
  2  -- sample data
  3  computer (compid, make, suppid, locid) as
  4    (select 13323, 'IBM'  , 124, 333 from dual union all
  5     select 13324, 'Apple', 125, 444 from dual union all
  6     select 13325, 'Apple', 125, 444 from dual union all
  7     select 13326, 'Apple', 125, 444 from dual
  8    ),
  9  supplier (suppid, sname, saddress) as
 10    (select 124, 'IBM Sdn.Bhd'  , '15 Jalan Duta' from dual union all
 11     select 125, 'Apple Sdn.Bhd', '18 Jalan Duta' from dual
 12    ),
 13  comp_loc as
 14    -- number of computers per location; RNK = 1 shows location with most computers
 15    (select locid,
 16            rank() over (order by count(*) desc) rnk,
 17            count(*) cnt
 18     from computer
 19     group by locid
 20    )
 21  select distinct s.suppid, s.sname, s.saddress, c.make, l.cnt
 22  from supplier s join computer c on c.suppid = s.suppid
 23  join comp_loc l on l.locid = c.locid
 24  where l.rnk = 1;

    SUPPID SNAME         SADDRESS      MAKE         CNT
---------- ------------- ------------- ----- ----------
       125 Apple Sdn.Bhd 18 Jalan Duta Apple          3

SQL>

推荐阅读