求条sql语句

主表结构：
REPORTID NUMBER         N 报告编号
REPORTNAME VARCHAR2(100) Y 报告名称
REPORTYEAR NUMBER         Y 报告年份
UNIT         CHAR(1)         Y 报告单位(周W、月M、季度Q、年Y、日D、时间段O)
CYCLE1         VARCHAR2(10) Y 周期值1
CYCLE2         VARCHAR2(10) Y 周期值2
REPORTTYPE VARCHAR2(20) Y 报告类别
STATUS         CHAR(1)         Y '0' 状态(0草稿、1已提交)
CONTENT         BLOB         Y 报告内容
OPCOMPANY VARCHAR2(20) Y 报告人公司
OPOPERID VARCHAR2(20) Y 报告人操作点ID
OPROLEID VARCHAR2(20) Y 报告人权限ID
OPUSERID VARCHAR2(20) Y 报告人ID
TS         DATE         Y 报告时间
COMMITTS DATE         Y 提交时间
回复表结构：
REPLYID         NUMBER         N 回复编号
REPORTID NUMBER         Y 报告编号
TS         DATE         Y SYSDATE 回复时间
OPUSERID VARCHAR2(50) Y 回复人
CONTENT         BLOB         Y 回复内容
OPOPERID VARCHAR2(50) Y 回复人操作点
OPROLEID VARCHAR2(50) Y 回复人角色
OPCOMPANY VARCHAR2(50) Y 回复人公司主表数据：
报告编号  报告名称
31 asfsafasd
42 gdsfgdsfg
36 hsgdfgdfgds
40 tertwetwert
28 年终报告
55 总结
64 fasdfas
4 年终报告回复表数据：
回复编号 报告编号   回复时间                   回复人
6 28 2008-10-17 9:17:18 122001001
10 28 2008-10-17 10:11:26 122001001
14 28 2008-10-17 10:29:25 122001001
18 28 2008-10-17 11:01:08 122001001
16 31 2008-10-17 10:51:55 122001001
17 28 2008-10-17 10:58:23 122001001
19 28 2008-10-17 11:02:52 122001001
5 28 2008-10-17 9:16:30 122001001
7 28 2008-10-17 9:18:00 122001001
8 28 2008-10-17 10:05:44 122001001
23 4 2008-10-17 15:09:28 122001001
25 35 2008-10-17 17:07:53 122001001
15 31 2008-10-17 10:50:50 122001001
22 4 2008-10-17 15:09:19 122001001
24 4 2008-10-17 15:09:39 122001001
我想得到的数据:
报告编号  报告名称                      回复编号       报告编号   回复时间                   回复人
31  asfsafasd                  15          31        2008-10-17 10:51:55    122001001
42 gdsfgdsfg                   后四个为空
36 hsgdfgdfgds                 后四个为空
40 tertwetwert                  后四个为空
28 年终报告                         19          28    2008-10-17 11:02:52   122001001
55 总结                            后四个为空
64 fasdfas                     后四个为空
4 年终报告                         24           4    2008-10-17 15:09:39   122001001在线等及。。下班前测试通过晚上结贴   是在oracle中的

解决方案 »

  1.   

    主表:a
    回复表:b
    select * from a,b
    where a.报告编号(+) = b.报告编号.
    其实你需要的是个左右联接的问题.
      

  2.   

    SELECT * /*需要的字段请自已列出来*/
      FROM M/*主表*/, 
      (
      SELECT *
      FROM (
    SELECT REPLYID
        ,REPORTID
        ,TS
        ,OPUSERID
        ,CONTENT
        ,OPOPERID
        ,OPROLEID
        ,OPCOMPANY
        ,rownumber() over(partition by REPORTID order by ts desc) rn
      FROM C/*子表*/
       )
     WHERE rn = 1
     ) C
     WHERE M.REPORTID = C.REPORTID(+)
      

  3.   

    不好意思写反了:
    select * from a,b 
    where a.报告编号 = b.报告编号(+). 
      

  4.   

    2楼的朋友。。我需要在oracle中不是sql中的
    这句话在oracle中识别不了rownumber() over(partition by REPORTID order by ts desc) rn
      

  5.   

    row_number() over(partition by REPORTID order by ts desc) rn 
      

  6.   

    select *
      from 主表 c
      left join (select a.*
                   from 回复表 a,
                        (select 报告编号 bgbh, max(回复时间) hfsj
                           from 回复表
                          group by 报告编号) b
                  where a.报告编号 = b.bgbh
                    and a.回复时间 = b.hfsj)) d on c.报告编号 = d.报告编号
      

  7.   

    没测试数据，先调试下select a.REPORTID, a.REPORTNAME, b.REPLYID, b.REPORTID, b.TS, b.OPUSERID
      from table1 a
      left join (select REPLYID, REPORTID, TS, OPUSERID
                   from (select REPLYID,
                                REPORTID,
                                TS,
                                OPUSERID,
                                row_number() over(partition by REPORTID order by ts desc) rn
                           from table2)
                  where rn = 1) b on a.REPORTID = b.REPORTID
      

  8.   

    try it
    select a.REPORTID, a.REPORTNAME, b.REPLYID, b.REPORTID, b.TS, b.OPUSERID
      from table1 a
      left join (select REPLYID, REPORTID, max(TS), OPUSERID
                   from table2
                  group by REPORTID) b on a.REPORTID = b.REPORTID
      

  9.   


    SQL> select * from tblmst;  REPORTID REPORTNAME
    ---------- --------------------------------------------------------------------------------
            31 asfsafasd
            42 gdsfgdsfg
            36 hsgdfgdfgds
            40 tertwetwert
            28 年终报告
            55 总结
            64 fasdfas
             4 年终报告8 rows selectedSQL> select * from tblsec;   REPLYID   REPORTID TS          OPUSERID
    ---------- ---------- ----------- --------------------------------------------------
             6         28 10/17/08 9: 122001001
            10         28 10/17/08 10 122001001
            14         28 10/17/08 10 122001001
            18         28 10/17/08 11 122001001
            16         31 10/17/08 10 122001001
            17         28 10/17/08 10 122001001
            19         28 10/17/08 11 122001001
             5         28 10/17/08 9: 122001001
             7         28 10/17/08 9: 122001001
             8         28 10/17/08 10 122001001
            23          4 10/17/08 15 122001001
            25         35 10/17/08 17 122001001
            15         31 10/17/08 10 122001001
            22          4 10/17/08 15 122001001
            24          4 10/17/08 15 12200100115 rows selectedSQL> 
    SQL> select A.reportid,A.reportname,B.REPLYID,b.TS,b.OPUSERID FROM TBLMST A
      2  LEFT OUTER JOIN (
      3    select REPLYID,REPORTID,ts,OPUSERID from (
      4       select REPLYID,REPORTID,ts,OPUSERID,
      5       row_number()over(partition by REPORTID order by ts desc) rn
      6       from tblsec
      7    ) where rn=1
      8  ) B ON A.REPORTID=B.REPORTID;  REPORTID REPORTNAME                                                                          REPLYID TS          OPUSERID
    ---------- -------------------------------------------------------------------------------- ---------- ----------- --------------------------------------------------
             4 年终报告                                                                                 24 10/17/08 15 122001001
            28 年终报告                                                                                 19 10/17/08 11 122001001
            31 asfsafasd                                                                                16 10/17/08 10 122001001
            64 fasdfas                                                                                                 
            36 hsgdfgdfgds                                                                                             
            55 总结                                                                                                    
            40 tertwetwert                                                                                             
            42 gdsfgdsfg                                                                                               8 rows selectedSQL> 
    SQL> select A.reportid,A.reportname,B.REPLYID,b.TS,b.OPUSERID FROM TBLMST A
      2  LEFT OUTER JOIN (
      3    select REPLYID,REPORTID,ts,OPUSERID from (
      4       select REPLYID,REPORTID,ts,OPUSERID,
      5       row_number()over(partition by REPORTID order by ts) rn
      6       from tblsec
      7    ) where rn=1
      8  ) B ON A.REPORTID=B.REPORTID;  REPORTID REPORTNAME                                                                          REPLYID TS          OPUSERID
    ---------- -------------------------------------------------------------------------------- ---------- ----------- --------------------------------------------------
             4 年终报告                                                                                 22 10/17/08 15 122001001
            28 年终报告                                                                                  5 10/17/08 9: 122001001
            31 asfsafasd                                                                                15 10/17/08 10 122001001
            64 fasdfas                                                                                                 
            36 hsgdfgdfgds                                                                                             
            55 总结                                                                                                    
            40 tertwetwert                                                                                             
            42 gdsfgdsfg                                                                                               8 rows selectedSQL> 
      

  10.   

    你不是说row_number() over你版本不认嘛,我那个效率肯定没用函数这么快的噢.
      

  11.   

    都是高手啊。。我是联晕了。。
    row_number() over这个我能识别，rownumber() 这个是不认识，后来改好了。
    各位能解释一下这句话嘛？：row_number()over(partition by REPORTID order by ts) rn 
    网上搜索有点模糊，请各位指教