选择前10行，然后决定后面包含哪些行

I have 2 columns in table. The 1st column represents the name of different companies and the 2nd column displays the count of products.

表中有两列。第一列表示不同公司的名称，第二列显示产品的计数。

COL 1         COL 2
CompA         2323
CompB         2320
CompC         1999
CompD         1598
CompE         1400...so on

What i want to do is to display first 10 rows showing first 10 companies name and its count in desc order. Then I want to compare 10th company count with 11th company count. If they match, then display 11th company name and its count. If 10th company count doesn't match 11th company count, then display first 10 records only.

我要做的是显示前10行，显示前10家公司的名称及其计数。然后我想比较第十次公司统计和第十一次公司统计。如果匹配，则显示第11个公司名称及其计数。如果第10个公司计数不匹配第11个公司计数，则只显示前10条记录。

I have read only access so I can't update or insert new records in table.

我只读取访问权限，所以不能在表中更新或插入新记录。

How can this be done?

这是怎么做到的呢?

4 个解决方案

#1

If what you want to do is display the top ten counts, including ties. This is simple to solve with an ANALYTIC function such as RANK() or DENSE_RANK() ...

如果你想要做的是显示前十名，包括领带。这很容易用解析函数来解决，比如RANK()或DENSE_RANK()……

SQL> select * from
  2      ( select
  3          ename
  4             , sal
  5             , rank() over (order by sal desc) sal_rank
  6      from emp
  7      )
  8  where sal_rank <= 10
  9  /

ENAME             SAL   SAL_RANK
---------- ---------- ----------
QUASSNOI         6500          1
SCHNEIDER        5000          2
FEUERSTEIN       4500          3
VERREYNNE        4000          4
LIRA             3750          5
PODER            3750          5
KESTELYN         3500          7
TRICHLER         3500          7
GASPAROTTO       3000          9
ROBERTSON        2990         10
RIGBY            2990         10

11 rows selected.

SQL>

Note that if RIGBY had had the same salary as GASPAROTTO, their SAL_RANK would have been 9, ROBERTSON's would have been 11 and the result set would have comprised ten rows.

注意，如果里格比和加斯帕洛托的薪水一样，他们的SAL_RANK应该是9，罗伯逊的应该是11，结果集应该包含10行。

DENSE_RANK() differs from RANK() in that it always returns the top ten whatevers, instead of skipping ties...

DENSE_RANK()与RANK()的不同之处在于，它总是返回前十位的值，而不是跳过领带……

SQL> select * from
  2      ( select
  3          ename
  4             , sal
  5             , dense_rank() over (order by sal desc) sal_rank
  6      from emp
  7      )
  8  where sal_rank <= 10
  9  /

ENAME             SAL   SAL_RANK
---------- ---------- ----------
                               1
SCHNEIDER        5000          2
FEUERSTEIN       4500          3
VERREYNNE        4000          4
LIRA             3750          5
PODER            3750          5
KESTELYN         3500          6
TRICHLER         3500          6
GASPAROTTO       3000          7
ROBERTSON        2990          8
RIGBY            2990          8
SPENCER          2850          9
BOEHMER          2450         10

13 rows selected.

SQL>

#2

Try this:

试试这个:

SELECT col1, col2
FROM (
   SELECT col1, col2
     FROM Table
     WHERE col2 >= (SELECT col2
                      FROM (SELECT col2 FROM Table ORDER BY col2 DESC) t1
                     WHERE t1.ROWNUM = 10)
     ORDER BY col2 DESC) t2
WHERE t2.ROWNUM <= 11

#3

With Top10Co As
    (
    Select Col1 As CompanyName, Col2 As Cnt
        , Row_Number() Over ( Order By Col2 Desc ) As Num
    From MyTable
    )
Select CompanyName, Cnt
From Top10Co
Where Num <= 10
Union All
Select Col1, Col2
From MyTable
Where Exists    (
                Select 1
                From Top10Co As T2
                Where T2.Num = 10
                    And T2.CompanyName <> MyTable.Col1
                    And T2.Cnt = MyTable.Col2
                )

#4

I think that what you are saying can be done by writing a pl/sql block of code of top-n query. some thing like this would help

我认为您所说的可以通过编写一个*查询的pl/sql代码块来实现。像这样的事情会有帮助的

decalre
    v_col_1    companies.col_1%TYPE;
    v_col_2    companies.col_2%TYPE;
    count number;    
    col_2_all number;
    CURSOR companies is SELECT *
        FROM (select * from companies ORDER BY col_2) 
        WHERE rownum <= 10
        ORDER BY rownum;

begin

    loop 
      fetch companies into v_cal_1,v_col_2;
      count++;
      if count =10 then
        col_2_all=v_col_2
        dbms_output.put_line('company name'||v_cal_1||'company count'||v_cal_2);
      elsif count =11 then
        if col_2_all=v_col_2 then
          dbms_output.put_line('company name'||v_cal_1||'company count'||v_cal_2);
        end if;
      end if;
    EXIT WHEN count>11;

end;

i am not sure about the syntax but it has to be something like this :)

我不确定语法，但它必须是这样的:)

#1