如何从 pl/sql 中的表中获取记录？

发布于 2024-08-17 04:52:29 字数 608 浏览 6 评论 0原文

我有一张桌子：Questionmaster。它存储 DisciplineId、QuestionId、QuestionText 等...

现在我的问题是：

我需要特定 DisciplineId 的 10 条记录、另一个 DisciplineId 的 20 条记录和其他 DisciplineId 的 30 条记录...我应该做什么？如何合并所有语句并仅选择 60(10+20+30) 行？

对于一门学科来说，它的工作原理如下：

create or replace function fun_trial(Discipline1,Disc1_NoOfQuestions)
 open cur_out for 
  select getguid() tmp,
  QuestionNo,QuestionText,
  Option1,Option2,
  Option3,Option4,
  Correctanswer,Disciplineid
  from  Questionmaster
  where DisciplineId=discipline1
  AND  rownum <= disc1_NoOfQuestions
   order by tmp ;
return (cur_out);

原文

I have one table : Questionmaster. it stores DisciplineId,QuestionId,QuestionText etc...

Now My Question is:

I need 10 records of particular DisciplineId, 20 records for another DisciplineId and 30 records for Someother DisciplineId.... What should I do for that? How can I club all statement and get just 60(10+20+30) rows selected?

For one Discipline,it is working as shown below:

create or replace function fun_trial(Discipline1,Disc1_NoOfQuestions)
 open cur_out for 
  select getguid() tmp,
  QuestionNo,QuestionText,
  Option1,Option2,
  Option3,Option4,
  Correctanswer,Disciplineid
  from  Questionmaster
  where DisciplineId=discipline1
  AND  rownum <= disc1_NoOfQuestions
   order by tmp ;
return (cur_out);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

゛清羽墨安 2024-08-24 04:52:29

以下查询使用分析函数 RANK() 对学科内的问题进行排序。然后，外部查询分别选择学科 1、2 和 3 的前 10 个问题、前 20 个问题和前 30 个问题。

select * from (
  select getguid() tmp
         , QuestionNo
         , QuestionText
         , Option1
         , Option2
         , Option3
         , Option4
         , Correctanswer
         , Disciplineid
         , rank () over (partition by Disciplineid order by QuestionNo ) as rn 
  from  Questionmaster
  where DisciplineId in (1, 2, 3)
)
where ( DisciplineId = 1 and rn <= 10 )
or    ( DisciplineId = 2 and rn <= 20 )
or    ( DisciplineId = 3 and rn <= 30 )
/

The following query uses the analytic function RANK() to sort the questions within discipline. The outer query then selects the first ten, first twenty and first thirty questions for disciplines 1, 2 and 3 respectively.

select * from (
  select getguid() tmp
         , QuestionNo
         , QuestionText
         , Option1
         , Option2
         , Option3
         , Option4
         , Correctanswer
         , Disciplineid
         , rank () over (partition by Disciplineid order by QuestionNo ) as rn 
  from  Questionmaster
  where DisciplineId in (1, 2, 3)
)
where ( DisciplineId = 1 and rn <= 10 )
or    ( DisciplineId = 2 and rn <= 20 )
or    ( DisciplineId = 3 and rn <= 30 )
/

回复收藏 0 原文

~没有更多了~