Code Word SQL (7)

The WITH clause supports starting with Oracle 9i

  
 

   
  

  
--不用connect by，只用dual表，构造出1到128
   
  

  
with   a  as     (  select    1   from   dual  union   all  select    1    from   dual  )   
   
  

  
select rownum from a, a, a, a, a, a, a;
   
  

  

   
  

  
--做一个5*5的乘法表
   
  

  
with   multiplier  as     ( select   rownum n   From   dual connect  by   rownum  <  6  )   
   
  

  
select   a   n  | |   ' * '  | |  b   n  | |   ' = '  | | ( a   n  *  b   n     from   multiplier a    multiplier b  ;   
  
 

For the second SQL to use Connect by, now just know it is possible, do not need to know why. If you have a brave heart, you can ask Niang. It is estimated that a day or a day is needed to ascertain the changes. It is also worth mentioning that this type of writing in different database support is different, preferably not nested, although in 11g no problem, but in 9i and 10g is hard to say. Nesting refers to a similar: with a as below to see an application problem with SQL solution there are cows and geese on the edge of the pond, and Xiao Hua sees 15 head and 42 legs in total, how many cows and geese are there?


From for notes (Wiz)

Code Word SQL (7)