各位前辈,分组的数据应该怎么对相同的字段去拼接呢,问题如下:--查询语句及目前查询的结果
SQL> select o.order_no,t.ticket_no,ta.tax_name || ':' || ta.fare from et.et_order o
2 left join et.et_passenger pa on pa.order_id=o.id
3 left join et.et_passenger_segment ps on ps.passenger_id=pa.id
4 left join et.et_ticket t on t.id=ps.ticket_id
5 left join et.et_paxseg_tax ta on ta.passenger_segment_id=ps.id
6 where o.order_no='20110614074609359319'
7 ;ORDER_NO TICKET_NO TA.TAX_NAME||':'||TA.FARE
-------------------- ---------- -------------------------------------------------------------
20110614074609359319 2940000017 YR:230
20110614074609359319 2940000017 YQ:32
20110614074609359319 2940000017 CN:90
20110614074609359319 2940000017 YR:219
20110614074609359319 2940000017 CN:101
20110614074609359319 2940000017 YQ:32
20110614074609359319 2940000018 YQ:32
20110614074609359319 2940000018 CN:101
20110614074609359319 2940000018 YR:219
20110614074609359319 2940000018 YQ:32
20110614074609359319 2940000018 CN:90
20110614074609359319 2940000018 YR:23012 rows selected--想要的查询结果
20110614074609359319 2940000017 YR:230;YQ:32;CN:90;YR:219;CN:101;YQ:32
20110614074609359319 2940000018 YQ:32;CN:101;YR:219;YQ:32;CN:90;YR:230--SQL语句应该怎么改呢,先谢谢了!
--另外问个题外的问题,怎么查看自己发过的帖子啊,找了半天找不到
SQL> select o.order_no,t.ticket_no,ta.tax_name || ':' || ta.fare from et.et_order o
2 left join et.et_passenger pa on pa.order_id=o.id
3 left join et.et_passenger_segment ps on ps.passenger_id=pa.id
4 left join et.et_ticket t on t.id=ps.ticket_id
5 left join et.et_paxseg_tax ta on ta.passenger_segment_id=ps.id
6 where o.order_no='20110614074609359319'
7 ;ORDER_NO TICKET_NO TA.TAX_NAME||':'||TA.FARE
-------------------- ---------- -------------------------------------------------------------
20110614074609359319 2940000017 YR:230
20110614074609359319 2940000017 YQ:32
20110614074609359319 2940000017 CN:90
20110614074609359319 2940000017 YR:219
20110614074609359319 2940000017 CN:101
20110614074609359319 2940000017 YQ:32
20110614074609359319 2940000018 YQ:32
20110614074609359319 2940000018 CN:101
20110614074609359319 2940000018 YR:219
20110614074609359319 2940000018 YQ:32
20110614074609359319 2940000018 CN:90
20110614074609359319 2940000018 YR:23012 rows selected--想要的查询结果
20110614074609359319 2940000017 YR:230;YQ:32;CN:90;YR:219;CN:101;YQ:32
20110614074609359319 2940000018 YQ:32;CN:101;YR:219;YQ:32;CN:90;YR:230--SQL语句应该怎么改呢,先谢谢了!
--另外问个题外的问题,怎么查看自己发过的帖子啊,找了半天找不到
对于不定行列转换,需要使用存储过程动态拼接一个SQL参考一下wildwave的这个贴
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html
select order_no,max(content) from(
select order_no,wm_concat(...) over(partition by order_no order by order_no asc) content from t
) group by order_no;
看了行列转换的帖子,好像还不是那个意思--想要的查询结果,是三列,最后一列是好几行记录拼装起来的
ORDER_NO TICKET_NO TA.TAX_NAME||':'||TA.FARE
-------------------- ---------- -----------------------------------------------------
20110614074609359319 2940000017 YR:230;YQ:32;CN:90;YR:219;CN:101;YQ:32
20110614074609359319 2940000018 YQ:32;CN:101;YR:219;YQ:32;CN:90;YR:230