select name, 1 as id from t_user union all select name, 2 as id from t_user order by name, id
1楼和3楼结果:create table t (name varchar2(10));select * from (select name, 1 as id from t union all select name, 2 as id from t) order by name, id;select name, 1 as id from t union all select name, 2 as id from t order by name, id;NAME ID ---------- ---------- 李四 1 李四 2 王五 1 王五 2 张三 1 张三 2
select tt1.nn,tt2.ll from test tt1,(select level ll from dual connect by level <3) tt2 order by tt1.nn,tt2.ll;
level 用来调整重复的次数
验证过可以:select * from ( select name ,1 id from test union all select name ,2 id from test ) order by name,id;
with t as (select name from test) select name,1 from t union all select name,2 from t;
1楼的sql完全就可以解决楼主的问题啊
1L的就可以解决楼主的问题,那个SQL可以实现你的需求!
select level ll from dual connect by level <3; 这个好
from t_user
union all
select name, 2 as id from t_user
order by name, id
(name varchar2(10));select * from (select name, 1 as id from t
union all
select name, 2 as id from t) order by name, id;select name, 1 as id from t union all select name, 2 as id from t order by name, id;NAME ID
---------- ----------
李四 1
李四 2
王五 1
王五 2
张三 1
张三 2
from test tt1,(select level ll from dual connect by level <3) tt2
order by tt1.nn,tt2.ll;
select name ,1 id from test
union all
select name ,2 id from test
) order by name,id;
select name,1 from t
union all
select name,2 from t;