mysql与mssql、oracle的异同 (一点小笔记)

yukunming

                mysql与mssql、oracle的异同

2008.4.5
1.在where子句中引用取别名的列
select * from (
select c1 as column1,c2 as column2
from tbl1) x
where column1 <2000

2.连接列值
mysql：
select concat(c1,c2) from tbl1
mssql:
select c1 + c2 from tbl1
oracle:
select c1 || c2 from tbl1

3.限制返回的行数
mysql:
select * from tbl1 limit 5
mssql:
select top 5 * from tbl1
oracle:
select * from tbl1 where rownum<=5

4.随机返回n条数据
mysql:
select c1,c2 from tbl1 order by rand() limit 5
mssql:
select top 5 c1,c2 from tbl1 order by newid()
oracle:
select * from (
select c1,c2 from tbl1 order by dbms_random.value()
) where rownum<=5

5.将空值转化为实际值
select coalesce(c1,0)
from tbl1

select case when c1 is null then 0
else c1 end from tbl1
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/67138/showart_573017.html