代码之家 › 专栏 › 技术社区 › beginnerDeveloper

SQL PLUS仅返回列中最频繁的值

sqlplus oracle

beginnerDeveloper · 技术社区 · 7 年前

SQL PLUS: 我试图从一个表中只返回最频繁的“MAKE”,并从另一个表中返回这些客户的“NAME”。这就是我所拥有的:

 SELECT sv.make, c.first, c.MI, c.last
  FROM Sales s
   INNER JOIN Sale_Vehicles sv
    ON s.VIN = sv.VIN
   INNER JOIN Customers c
    ON s.cust_ID = c.cust_ID
  GROUP BY sv.make, c.first, c.MI, c.last
  ORDER BY sv.make, COUNT (*) DESC;

这将在结果顶部返回最频繁的“MAKE”,并在其下方返回第二个和第三个。如何只返回最频繁的?

3 回复 | 直到 7 年前

Younes El-karama 7 年前

WITH most_frequent AS (
SELECT *
FROM (SELECT sv.make
     FROM Sales s
     INNER JOIN Sale_Vehicles sv
       ON s.VIN = sv.VIN
     GROUP BY sv.make
     ORDER BY COUNT (*) DESC
    )
WHERE rownum = 1 
)
SELECT sv.make, c.first, c.MI, c.last
  FROM Sales s
   INNER JOIN Sale_Vehicles sv
    ON s.VIN = sv.VIN
   INNER JOIN Customers c
    ON s.cust_ID = c.cust_ID
   INNER JOIN most_frequent mf
    ON sv.make=mf.make;

ScaisEdge 7 年前

无法将结果用作rownum的子选择检查

select * from ( 
 SELECT sv.make, c.first, c.MI, c.last
  FROM Sales s
   INNER JOIN Sale_Vehicles sv
    ON s.VIN = sv.VIN
   INNER JOIN Customers c
    ON s.cust_ID = c.cust_ID
  GROUP BY sv.make, c.first, c.MI, c.last
  ORDER BY sv.make, COUNT (*) DESC) T 
  where rownum =1

Littlefoot 7 年前

如果您需要最常见的品牌(即数量最多的品牌),您应该先按数量订购,而不是按品牌订购。类似这样:

SELECT *
FROM (SELECT sv.make, c.first, c.MI, c.last
     FROM Sales s
     INNER JOIN Sale_Vehicles sv
       ON s.VIN = sv.VIN
     INNER JOIN Customers c
       ON s.cust_ID = c.cust_ID
     GROUP BY sv.make, c.first, c.MI, c.last
     ORDER BY COUNT (*) DESC               --> different from your ORDER BY
    )
WHERE rownum = 1;

[编辑]

正当如果多个MAKE满足条件,则秩分析函数可能会有所帮助。下面是一个基于Scott模式的示例:

工作频率:

SQL> select job, count(*)
  2  from emp
  3  group by job
  4  order by count(*) desc;

JOB         COUNT(*)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

让我们对他们进行排名:

SQL> select job, count(*),
  2    rank() over (order by count(*) desc) rn
  3  from emp
  4  group by job
  5  order by count(*) desc;

JOB         COUNT(*)         RN
--------- ---------- ----------
CLERK              4          1    --> these 2 should be returned as the 
SALESMAN           4          1    --> final result
MANAGER            3          3
ANALYST            2          4
PRESIDENT          1          5

最终结果:

SQL> select *
  2  from (select job, count(*), rank() over (order by count(*) desc) rn
  3        from emp
  4        group by job)
  5  where rn = 1;

JOB         COUNT(*)         RN
--------- ---------- ----------
CLERK              4          1
SALESMAN           4          1

应用于您的查询:

select *
  from (  select sv.make,
                 c.first,
                 c.mi,
                 c.last,
                 rank () over (order by count (*) desc) rn             --> new
            from sales s
                 inner join sale_vehicles sv on s.vin = sv.vin
                 inner join customers c on s.cust_id = c.cust_id
        group by sv.make,
                 c.first,
                 c.mi,
                 c.last)
 where rn = 1;

有什么改进吗?