针对特定场景的SQL内/子组明智查询
针对特定场景的SQL内/子组明智查询
提问于 2012-11-06 05:57:14
我有一张桌子如下
模式: ID _
编写一个sql，根据每个类别的投标价格提取前5个关键字。
详细信息：
Oracle10.x上考虑的表定义：
create table test (
ID number,
Category varchar (20),
Keyword varchar (20),
BidPrice number
);数据：
insert into test values (1, 'Category-A', 'Keyword-A1', 110);
insert into test values (2, 'Category-A', 'Keyword-A2', 121);
insert into test values (3, 'Category-A', 'Keyword-A3', 130);
insert into test values (4, 'Category-A', 'Keyword-A4', 125);
insert into test values (5, 'Category-A', 'Keyword-A5', 115);
insert into test values (6, 'Category-A', 'Keyword-A6', 133);
insert into test values (7, 'Category-B', 'Keyword-B1', 105);
insert into test values (8, 'Category-B', 'Keyword-B2', 111);
insert into test values (9, 'Category-B', 'Keyword-B3', 108);
insert into test values (10, 'Category-B', 'Keyword-B4', 128);
insert into test values (11, 'Category-B', 'Keyword-B5', 144);
insert into test values (12, 'Category-B', 'Keyword-B6', 101);
insert into test values (13, 'Category-C', 'Keyword-C1', 150);
insert into test values (14, 'Category-C', 'Keyword-C2', 137);
insert into test values (15, 'Category-C', 'Keyword-C3', 126);
insert into test values (16, 'Category-C', 'Keyword-C4', 121);
insert into test values (17, 'Category-C', 'Keyword-C5', 112);
insert into test values (18, 'Category-C', 'Keyword-C6', 118);预期输出：
KEYWORD CATEGORY BIDPRICE
-------------- ------------ ----------
Keyword-A6 Category-A 133
Keyword-A3 Category-A 130
Keyword-A4 Category-A 125
Keyword-A2 Category-A 121
Keyword-A5 Category-A 115
Keyword-B5 Category-B 144
Keyword-B4 Category-B 128
Keyword-B2 Category-B 111
Keyword-B3 Category-B 108
Keyword-B1 Category-B 105
Keyword-C1 Category-C 150
Keyword-C2 Category-C 137
Keyword-C3 Category-C 126
Keyword-C4 Category-C 121
Keyword-C6 Category-C 118 注意:答案必须仅在SQL中，而不使用任何Oracle或特定于数据库的函数。
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-11-06 08:50:55
我做了这个查询，但是如果它是最大的，它将保留第6行。有人能帮忙吗？
select a.keyword, a.category, a.bidprice
from
test a,
(
select b.id, b.category
from test b
where (select distinct(c.category) from test c where b.category=c.category) = b.category
) xx
where a.id in (select ss.id from test ss where a.category=ss.category and rownum<=5)
group by a.category, a.bidprice, a.keyword
order by a.category, a.bidprice desc;伙计们，
我解决了，请考虑一下：
select a.keyword, a.category, a.bidprice
from
test a,
(
select b.id, b.category
from test b
where (select distinct(c.category) from test c where b.category=c.category) = b.category
order by b.bidprice desc
) xx
where a.id in (
select id
from (select dd.id, dd.category from test dd order by dd.bidprice desc) ss
where a.category=ss.category
and rownum<=5
)
group by a.category, a.bidprice, a.keyword
order by a.category, a.bidprice desc;Stack Overflow用户
发布于 2012-11-06 06:00:52
利用ROW_NUMBER()
SELECT KEYWORD ,CATEGORY, BIDPRICE
FROM
(
SELECT KEYWORD ,CATEGORY, BIDPRICE,
ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY BIDPRICE DESC) rn
FROM test
) a
WHERE rn <= 5
ORDER BY CATEGORY, BIDPRICE DESC- SQLFiddle演示
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13245223
复制相关文章
相似问题
腾讯云开发者
