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现在的需求如下:
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司、123、洒脱 0012950、4258697、8524963584 0000165 34请教各位大虾,用SQL语句如何实现?
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现在的需求如下:
96626 杨开龙 03342 0000025 001
155907 固原兴远商贸有限公司、123、洒脱 0012950、4258697、8524963584 0000165 34请教各位大虾,用SQL语句如何实现?
select rgstrid,wmsys.wm_concat(possessor) possessor,wmsys.wm_concat(ownernum) ownernum,wmsys.wm_concat(rgstrnum) rgstrnum,
wmsys.wm_concat(roomlabel ) roomlabel
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
group by rgstrid;
substr(max(sys_connect_by_path(possessor ,',')),2) possessor,
substr(max(sys_connect_by_path(ownernum ,',')),2) ownernum,
substr(max(sys_connect_by_path(rgstrnum ,',')),2) rgstrnum,
substr(max(sys_connect_by_path(roomlabel ,',')),2) roomlabel
from ( select
t2.rgstrid, t5.possessor,t5.ownernum,t2.rgstrnum,
t1.roomlabel , row_number() over(order by t2.rgstrid) rn
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 )
)
start with rn=1
connect by rn-1=prior rn
group by rgstridORCALE 9i
上面的SQL只能在Oracle里执行,在MSSQL里面就行不通了。