高难度：在一条select语句中，如何实现重复显示任何一条纪录3次？？

Question

做一个表，里面有三条记录，然后做一个交集查询
SQL> create table aa (a int);Table createdSQL> insert into aa values (1);1 row insertedSQL> insert into aa values (2);1 row insertedSQL> select * from aa;                                      A
---------------------------------------
                                      1
                                      2SQL> create table bb (b int);Table createdSQL> insert into bb values (5);1 row insertedSQL> insert into bb values (5);1 row insertedSQL> insert into bb values (5);1 row insertedSQL> select * from bb;                                      B
---------------------------------------
                                      5
                                      5
                                      5SQL> select * from aa,bb ;                                      A                                       B
--------------------------------------- ---------------------------------------
                                      1                                       5
                                      2                                       5
                                      1                                       5
                                      2                                       5
                                      1                                       5
                                      2                                       56 rows selectedSQL> select * from aa;                                      A
---------------------------------------
                                      1
                                      2SQL> select aa.* from aa,bb ;                                      A
---------------------------------------
                                      1
                                      2
                                      1
                                      2
                                      1
                                      26 rows selectedSQL> select aa.* from aa,bb order by a ;                                      A
---------------------------------------
                                      1
                                      1
                                      1
                                      2
                                      2
                                      26 rows selectedSQL>

Answer 1

select * from aa_c
union all
select * from aa_c
union all
select * from aa_c

Answer 2

SELECT C1.NO,C1.NUM,C1.NUM1 FROM C C1 ,C C2 ,C C3 ORDER BY C1.NO DESC;

Answer 3

(select * from a)
union all
(select * from a)
union all
(select * from a)

Answer 4

我基本同意beckhambobo的看法.楼主的这个需求不知道是自己做着玩还是项目的需要?如果是后者我觉得没什么讨论的价值。

Answer 5

我写这些只是为了 不排序，并且对表只进行一次 scan 但他不是最快的
[email protected] >select * from t;         N V
---------- --------------------
         1 lg
        12 [email protected] >create or replace type my_object as object
  2  (nn number, vv varchar2(20));
  3  /类型已创建。[email protected] >create or replace type my_type as table of my_object;
  2  /类型已创建。  1  create or replace function f(p_cursor in sys_refcursor) return my_type
  2  pipelined
  3  as
  4     type array is table of t%rowtype index by binary_integer;
  5     l_data array;
  6     l_object my_object:=my_object(null,null);
  7  begin
  8     loop
  9         fetch p_cursor bulk collect into l_data limit 100;
 10         for i in 1..l_data.count loop
 11             l_object.nn:=l_data(i).n;
 12             l_object.vv:=l_data(i).v;
 13             pipe row(l_object);   -- 这里你要每行重复的次数 我没用loop
 14             pipe row(l_object);
 15             pipe row(l_object);
 16         end loop;
 17         exit when p_cursor%notfound;
 18     end loop;
 19     close p_cursor;
 20     return;
 21* end;
[email protected] >/函数已创建。[email protected] >set autotrace on
[email protected] >select * from table(f(cursor(select * from t)));        NN VV
---------- --------------------
         1 lg
         1 lg
         1 lg
        12 lg2
        12 lg2
        12 lg2已选择6行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'F'
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        482  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows [email protected] >

Answer 6

union 不行，union all可以，
(select * from table where xxx order by yyy) union all (select * from table where xxx order by yyy) union all (select * from table where xxx order by yyy) order by zzz
其中zzz是你原来sql语句的order by的列数，从一开始的。
昨天已经帮你解决了，今天才有空来回复，给分吧。