在分离正负值的同时获得多个值

Question

create table s4(val number);
INSERT ALL  
  INTO s4 (val) VALUES (1) 
  INTO s4 (val) VALUES (2)  
  INTO s4 (val) VALUES (-1) 
  INTO s4 (val) VALUES (-2)
  select * from dual;


WITH p AS(SELECT val AS Pos FROM s4 WHERE val > 0),
 n  AS (SELECT val AS Neg FROM s4 WHERE val < 0)
 SELECT Pos,Neg FROM p,n 

Answer 1

我怀疑你可能在找这样的东西：

SQL> with
  2  p as
  3    (select val as pos,
  4            rownum rn
  5     from s4
  6     where val > 0
  7    ),
  8  n as
  9    (select val as neg,
 10            rownum rn
 11     from s4
 12     where val < 0
 13    )
 14  --
 15  select p.pos, n.neg
 16  from p left join n on n.rn = p.rn     -- more positive values
 17  union
 18  select p.pos, n.neg
 19  from p right join n on n.rn = p.rn;   -- more negative values

       POS        NEG
---------- ----------
         1         -1
         2         -2

SQL>

是干什么的呢？

• rownum在这里是为了让您可以通过something
• outer联接来连接行，以便在正值的个数不等于负值的情况下返回行(例如，如果插入值为3的行，则内部联接不会返回)
  • 就是union，因为可能存在更多的负值或更多的正值；无法判断happen

将在哪种情况下