我怎样才能得到最小的行

Question

我怎么能从我生成的表中得到最小的行我的意思是生成的表，我将通过子查询生成一个表来计算记录然后我会得到最小的一行。 换句话说， 我试图解决的问题 我有一个表：works_on，其中包含所有针对由pnumber标识的项目工作的员工的ssn 我想确定包含min的​​项目的pnumber员工

感谢在事先

Answer 1

MAX数...保持

SELECT MAX(pnumber) KEEP (DENSE_RANK FIRST ORDER BY cnt DESC) 
FROM 
    (SELECT pnumber, count(*) cnt 
    FROM works_on 
    GROUP BY pnumber) 

如果你有多个项目相同的最低，就有点复杂了。

SELECT pnumber FROM works_on 
GROUP BY pnumber 
HAVING COUNT(*) = 
    (SELECT MAX(cnt) max_cnt 
    FROM 
    (SELECT pnumber, count(*) cnt 
    FROM works_on 
    GROUP BY pnumber)) 

Answer 2

其他一些选项供您尝试。

你可以尝试简单的方法，基于ROWNUM

select * 
from (
    SELECT pnumber, count(*) cnt 
    FROM works_on 
    GROUP BY pnumber 
    order by cnt 
) 
where rownum < 2 

或者使用RANK解析函数。该DENSE_RANK和ROW_NUMBER功能将同样的工作（但每个略有不同。

SELECT * 
FROM (
    SELECT pnumber, cnt, rank() over (order by cnt) as my_rank 
    from (
    SELECT pnumber, count(*) cnt 
    FROM works_on 
    GROUP BY pnumber 
) 
) 
WHERE my_rank = 1 

Answer 3

select * from (

select pnumber , count (pnumber)counter from works_on 

group by pnumber 

) 

where counter=(select min(counter) from (select pnumber , count (pnumber)counter from works_on 

group by pnumber 

))