码上敲享录 > sql增删改查之常见报错异常解答 > oracle.sqlserver和mysql最常见的区别

oracle.sqlserver和mysql最常见的区别

上一章章节目录下一章 2018-07-25已有2675人阅读 评论(0)

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


向大家推荐《Activiti工作流实战教程》:https://xiaozhuanlan.com/activiti
0

有建议,请留言!

  • *您的姓名:

  • *所在城市:

  • *您的联系电话:

    *您的QQ:

  • 咨询问题:

  • 提 交