oracle.sqlserver和mysql常见的区别
解决方法：
1.字符拼接
mysql:
CONCAT('a','b')
sqlserver:
'a'+'b'
oracle:
'a'||'b'
2.分页
mysql：
select * from table_name limit {1},{2};//{1}=(page - 1) * rows {2}等于rows ，page当前页，rows每页行数
sqlserver:
select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, * from table_name) t ) tt where tempRowNumber > {2};// {1}等于(page-1)*rows，{2}等于(page-1)*rows+rows;
oracle：
select * from (select row_.*,rownum rownum_ from table_name row_ where rownum <= {1}) where rownum_>{2};//{1}等于(page-1)*rows，{2}等于(page-1)*rows+rows;
3.group by和order by
mysql:
select * from sys_log GROUP BY ip ORDER BY createtime DESC;//结果字段可以任意字段，GROUP BY后面可以跟order by
sqlserver和oracle：
select ip from sys_log GROUP BY ip;//结果字段只能是GROUP BY后面的字段，GROUP BY后面不可以跟order by
4.判断null设置默认值
expr1：可为空的字段或者表达式。
expr2：null的替代值。
mysql:
IFNULL(expr1,expr2)
sqlserver:
ISNULL(expr1,expr2)
oracle:
NVL(expr1,expr2)
5.递归循环
mysql:
暂时没有，一句话难实现，只能通过存储过程等
SET @sTemp = '$';
SET @sTempChd =cast(5 as CHAR);
WHILE @sTempChd is not null DO
SET @sTemp = concat(@sTemp,',',@sTempChd);
SELECT group_concat(id) into @sTempChd FROM account_item where FIND_IN_SET(parent_id,@sTempChd)>0;
END WHILE;
sqlserver:
with temp ( id, pid,name) as
(
select id, pid,name
from sys_menu
where pid = '5a4f0eace0e1ce32c489d839'
union all
select a.id, a.pid,a.name
from sys_menu a
inner join temp on a.pid = temp.id
) select * from temp
oracle:
SELECT * FROM tree START WITH id = 2 CONNECT BY pid = PRIOR id; -- 递归查询子节点
本文地址：http://www.yayihouse.com/yayishuwu/chapter/1351