atitit。mssql sql server 转换mysql 及 分页sql ast的搭建
1. 主要的的转换::函数的转换,分页的转换
2. 思路::mssql sql >>ast>>mysql
3. sql主要的如下:::
作者:: 老哇的爪子 Attilax 艾龙， EMAIL:1466519819@qq.com
转载请注明来源： http://blog.csdn.net/attilax
SELECT groupid,
groupname,
convert(varchar, createtime, 120) AS createtime,
weixinuserCount,
remark
FROM
(SELECT row_number() over( ORDER BY createtime DESC) AS rowNum, d1.groupid,
d1.groupname,
d1.createtime,
d1.remark,
(SELECT count(d2.UserID)
FROM t_mb_weixinuser d2
WHERE d2.groupid=d1.groupid)AS weixinuserCount
FROM t_mb_group d1
WHERE 1=1)
AS groups
WHERE rowNum BETWEEN 1 AND 10
4. Mssql2MysqlConvertor (proj:wechatAdm4propt
public class Mssql2MysqlConvertor {
@Inject
SqlAstBuilderP32 astBldr;
SqlAstP32 ast;
/**
* @author attilax 老哇的爪子
* @since p33 j_t_37
*/
public static void main(String[] args) {
String txt = filex.read(pathx.classPath() + "/mssql1.sql");
txt = strUtil.replaceEnterChar2space(txt);
txt = txt.replaceAll("row_number.*?,", "");
Mssql2MysqlConvertor x = new Mssql2MysqlConvertor();
String sql_mysql = x.convert(txt);
System.out.println(JsonX.toJsonStrO88(x.ast));
System.out.println(sql_mysql);
}
/**
* @author attilax 老哇的爪子
* @return
* @since p33 m_j_45
*/
private String convert(String txt) {
String page_s = strUtil.find("rowNum(.+)BETWEEN.+and.+$", txt).get(0)
.toString();
page_s = strUtil.replaceDoubleSpace(page_s);
String[] a = page_s.toUpperCase().trim().split("BETWEEN");
String[] a2 = a[1].toLowerCase().trim().split("and");
SqlPageParam p = new SqlPageParam();
p.startIndex = Integer.parseInt(a2[0].trim()) - 1;
p.pagesize = Integer.parseInt(a2[1].trim());
ast = new SqlAstP32();
ast.preOther = txt.substring(0, txt.indexOf(page_s));
ast.pagePart = p;
if (ast.preOther.trim().toLowerCase().endsWith("where"))
ast.preOther = ast.preOther + " 1=1 ";
return ast.preOther + " limit " + p.startIndex + "," + p.pagesize;
}