select * from ( select t2.rgstrid, t5.possessor,t5.ownernum,t2.rgstrnum,
t1.roomlabel from re_room t1
left join re_register t2 on t1.rgstrid = t2.rgstrid
left join re_ownrshpdtl t3 on t1.roomid = t3.roomid
left join re_ownership t4 on t3.ownrshpid = t4.ownrshpid
left join re_possessor t5 on t5.ownrshpid = t4.ownrshpid
where 1=1 and t2.rgstrnum like '%000%' order by t1.roomid asc ) where rownum <= 5;上面的SQL查询结果如下:
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司 0012950 0000165 34
155907 123 4258697 0000165 34
155907 洒脱 8524963584 0000165 34现在需求如下,用SQL语句怎么实现? 该SQL语句要求Oracle和MSSQL都适用。
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司、123、洒脱 0012950、4258697、8524963584 0000165 34
t1.roomlabel from re_room t1
left join re_register t2 on t1.rgstrid = t2.rgstrid
left join re_ownrshpdtl t3 on t1.roomid = t3.roomid
left join re_ownership t4 on t3.ownrshpid = t4.ownrshpid
left join re_possessor t5 on t5.ownrshpid = t4.ownrshpid
where 1=1 and t2.rgstrnum like '%000%' order by t1.roomid asc ) where rownum <= 5;上面的SQL查询结果如下:
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司 0012950 0000165 34
155907 123 4258697 0000165 34
155907 洒脱 8524963584 0000165 34现在需求如下,用SQL语句怎么实现? 该SQL语句要求Oracle和MSSQL都适用。
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司、123、洒脱 0012950、4258697、8524963584 0000165 34
oracle可以wmsys.wm_concat()函数完成
而mssql一般自己写一个函数完成