如何在oracle11g中连接两个表并分配动态coloum [英] how to join two tables in oracle11g and assign a dynamic coloum

问题描述

亲爱的朋友们，



这里我是Oracle 11g的新手，所以我想知道如何在oracle11g中连接两个表并为该查询分配动态coloum



1：查询：从tbl_isrequest中选择ISREQUEST，ISREQUESTTYPEID，ISREQUESTDATE，其中status = 3或status = 2且igapppersonmid = 787670;





输出：ISREQUEST ISREQUESTTYPEID ISREQUESTDATE



3,1,18-OCT-13 06.39.32.000000000 PM


1,1,18-OCT-13 06.39.32.000000000 PM





2,1,18-OCT-13 06.39.32.000000000 PM





7,2,18-OCT-13 06.39.32.000000000 PM





9,2,18-OCT-13 06.39.32.000000000 PM





2：查询：来自asas.tbl_isinte的SELECT count（*） rreators where isquest = 1 and persontype = 1



OutPut：数量

1



从这两个查询我想要这样的输出



输出：ISREQUEST ISREQUESTTYPEID ISREQUESTDATE CheckCondiction



3,1,18-OCT-13 06.39.32.000000000 PM，False



1,1,18-OCT-13 06.39.32.000000000 PM，True




2,1,18-OCT-13 06.39.32.000000000 PM，False





7,2,18-OCT-13 06.39.32.000000000 PM，False





9 ，2，18-OCT-13 06.39.32.000000000 PM，False





CheckCondiction是另一个应该通过查询生成的coloumn如果你们知道解决方案与我分享，那么isrequest就是这两个问题的共同点。







问候，



AnilKumar.D

Dear Friends,

Here iam new to Oracle 11g and so i want to know how to join two tables in oracle11g and assign dynamic coloum to that query

1:Query: select ISREQUEST,ISREQUESTTYPEID,ISREQUESTDATE from tbl_isrequest where status=3 OR status=2 and igapppersonmid=787670;


Output:ISREQUEST ISREQUESTTYPEID ISREQUESTDATE

3 , 1 , 18-OCT-13 06.39.32.000000000 PM

1 , 1 , 18-OCT-13 06.39.32.000000000 PM


2 , 1 , 18-OCT-13 06.39.32.000000000 PM


7 , 2 , 18-OCT-13 06.39.32.000000000 PM


9 , 2 , 18-OCT-13 06.39.32.000000000 PM


2:Query:SELECT count(*) from asas.tbl_isinterrogators where isrequest =1 and persontype = 1

OutPut:Count
1

From this two queries i want the output like this

Output:ISREQUEST ISREQUESTTYPEID ISREQUESTDATE CheckCondiction

3 , 1 , 18-OCT-13 06.39.32.000000000 PM , False

1 , 1 , 18-OCT-13 06.39.32.000000000 PM , True


2 , 1 , 18-OCT-13 06.39.32.000000000 PM , False


7 , 2 , 18-OCT-13 06.39.32.000000000 PM , False


9 , 2 , 18-OCT-13 06.39.32.000000000 PM , False


CheckCondiction is another coloumn which should generate through query and isrequest is the common coloumn for the both queries.if you people know the solution share with me.



Regards,

AnilKumar.D

推荐答案

你好b $ b你可以使用嵌套查询和 DECODE（）函数来解决这个问题，如下所示：

Hi you can use nested query and DECODE() function for solve this problem, like this :

select i.ISREQUEST,
       i.ISREQUESTTYPEID,
       i.ISREQUESTDATE,
       decode((SELECT count(*)
                from asas.tbl_isinterrogators s
               where s.isrequest = i.ISREQUEST
                 and s.persontype = 1),
              0,
              'FALSE',
              'TRUE') as isinterrogators
  from tbl_isrequest i
 where i.status = 3
    OR i.status = 2
   and i.igapppersonmid = 787670;

这篇关于如何在oracle11g中连接两个表并分配动态coloum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！