我在表格中有 2 列。第一列表示不同公司的名称,第二列显示产品计数。
COL 1 COL 2
CompA 2323
CompB 2320
CompC 1999
CompD 1598
CompE 1400...so on
我想做的是显示前 10 行,按描述顺序显示前 10 家公司名称及其计数。然后我想将第 10 家公司数量与第 11 家公司数量进行比较。如果它们匹配,则显示第 11 个公司名称及其计数。如果第 10 家公司计数与第 11 家公司计数不匹配,则仅显示前 10 条记录。
我具有只读访问权限,因此无法在表中更新或插入新记录。
如何做到这一点?
如果要显示前十个计数,包括领带。 这很容易用分析函数来解决,例如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>
请注意,如果RIGBY的薪水与GASPAROTTO相同,他们的SAL_RANK将是9,ROBERTSON的将是11,结果集将包含十行。
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>
试试这个:
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
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
)
我认为你所说的可以通过编写top-n查询的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;
我不确定语法,但它必须是这样的:)