SELECT查询结果集中,如何能够动态添加一个标识列
比如通过
select * from t_category c1 start with c1.id=79350 connect by prior c1.id=c1.parent_id order by resource_order
此查询语句可以获取如下数据
ID RESOURCE_TYPE RESOURCE_ID RESOURCE_NAME PARENT_ID ...
79351 1 _A1000127724 浮沉1 79350 ...
79352 1 _A1000127732 浮沉2 79350 ...
能否在查询结果集后面动态添加一列
如RESOURCE_ID这列,内容是79350,要达到的效果是
ID RESOURCE_TYPE RESOURCE_ID RESOURCE_NAME PARENT_ID RESOURCE_ID...
79351 1 _A1000127724 浮沉1 79350 79350...
79352 1 _A1000127732 浮沉2 79350 79350...
只是想通过union把需要的数据一次加载上来,并能加以区分,如:
select * from t_category c1 start with c1.id=79350 connect by prior c1.id=c1.parent_id order by resource_order
union
select * from t_category c1 start with c1.id=79351 connect by prior c1.id=c1.parent_id order by resource_order
union
select * from t_category c1 start with c1.id=79352 connect by prior c1.id=c1.parent_id order by resource_order
union
select * from t_category c1 start with c1.id=79353 connect by prior c1.id=c1.parent_id order by resource_order
不知这样是否可行
select id,resource_type,resource_id,resource_name,parent_id,resource_order,resource_path,resource_show_path,resource_stencil,state,template_id,product_id,is_adv,business_type,is_focus,is_commend,create_time,79350 as source_id from (select * from t_category c1 start with c1.id=79350 connect by prior c1.id=c1.parent_id order by resource_order)
union
select id,resource_type,resource_id,resource_name,parent_id,resource_order,resource_path,resource_show_path,resource_stencil,state,template_id,product_id,is_adv,business_type,is_focus,is_commend,create_time,105620 as source_id from (select * from t_category c1 start with c1.id=105620 connect by prior c1.id=c1.parent_id order by resource_order)
union
select id,resource_type,resource_id,resource_name,parent_id,resource_order,resource_path,resource_show_path,resource_stencil,state,template_id,product_id,is_adv,business_type,is_focus,is_commend,create_time,105621 as source_id from (select * from t_category c1 start with c1.id=105621 connect by prior c1.id=c1.parent_id order by resource_order)
据说也可采用存储过程或者临时表解决此问题,后续会对以上问题进行改造。