取整 & row_number & for & 动态DDL & 取随机值dbms_random.value

declare
begin
execute immediate "DDL"
end ;

drop table table1 purge;

 

 

查询数据库中的任意一条语句sql、 random .

mysql：

select * from table order by rand() limit 1

 

PostgreSQL:

select * from table order by random() limit 1

 

Microsoft SQL Server:

select  top 1 colimn from table order by newid()

 

IBM DB2:

select column,rand() as cd from table order by cd fetch first 1 rows only

 

Oracle:

select column from (select column from table order by dbms_random.value) where rownum=1

e.g:
   update table1
    set id=( select id from (
         select id from table1
     order by dbms_random.value )
         where rownum = 1 )

 

ORACLE:
  row_number() over(partition by colum order by column )
--------按组给行分序号  

 

关于Oracle取整的函数. .

关于Oracle取整的函数分别有以下几种:

　  1.取整(大) 
　　select ceil(-1.001) value from dual

　　2.取整（小）
　　select floor(-1.001) value from dual

　　3.取整（截取）
　　select trunc(-1.002) value from dual

       4.取整(舍入)
       select round(-1.001) value from dual

 

生成自然数列：
select rownum
  from daul
 connect by rownum < 1000 ;
e.g:
      select mod(rownum,50), rownum,rownum,rownum,rownum,rownum
       from dual
       connect by rownum<=10000

列合并：wmsys.wm_concat
select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum   from shopping   group by u_id 

u_id          goods_sum

____________________

1              苹果(2斤),西瓜(4斤),橘子(3斤)

2              梨子(5斤)

3              葡萄(1斤),香蕉(1斤