sql规范告诫 解决兼容性问题 经验记录

1：sqlserver2008与mysql兼容性问题2：groupby 组函数

group by：别给我这么写select * from T_STANDARD_SYSTEM group by SYSTEM_ID order by SYSTEM_ID虽然mysql支持但是大部分数据库不支持，更改数据库时能

搞死人。可以将*中的每一项用max()函数包围起来。

2：sqlserver2008与mysql兼容性问题2：order by groupby 组函数

select COUNT(*) from T_ASSESS_ASSIGN order by ID在sqlserver中行不通，消息 8127，级别 16，状态 1，第 1 行
ORDER BY 子句中的列 "T_ASSESS_ASSIGN.ID" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

select COUNT(*) from T_ASSESS_ASSIGN这个可以。select COUNT(*) from T_ASSESS_ASSIGN group by id order by ID这个也可以。

也就是在sqlserver中有order by 有聚合函数就必须有groupby

而在mysql中两条都行的通。

3：sqlserver2008与mysql兼容性问题3：排序问题

select * from ( select * ,ROW_NUMBER() as rowNum from T_SYS_MESSAGE where 1=1 and USER_TO = ? and USER_ID = ? order by state desc,send_time desc) as temp where temp.rowNum between 0 and 10];'ROW_NUMBER' 附近有语法错误，需要 'OVER'

使用例子：

public Page find(String arg0, Object[] params, int pageSize, int curPage) {
String sql = "select count(*) " + arg0.substring(arg0.indexOf("from"));
//用于sqlserver分页时的over函数。如果sql中不带排序则按自然顺序排序，否则按原来的排序方式。
String tem = "order by 1";
if(sql.contains("order") || sql.contains("ORDER")){
sql = sql.substring(0,sql.toLowerCase().indexOf("order"));
tem = arg0.substring(arg0.toLowerCase().indexOf("order"));
}

===============================================================

if("sqlserver".equals(dbType.trim().toLowerCase())){
arg0 = "select * from ( " + arg0.substring(0,arg0.indexOf("from")) +
" ,ROW_NUMBER() " + "OVER(" + tem + ") as rowNum "
+ arg0.substring(arg0.indexOf("from")) + ") as temp where temp.rowNum between "
+ pageSize * (curPage -1) + " and " + pageSize * curPage;
System.out.println("++++++++++" + arg0);
}

select * from ( select * ,ROW_NUMBER() OVER(order by state desc,send_time desc) as rowNum from T_SYS_MESSAGE where 1=1 and USER_TO = ? and USER_ID = ? order by state desc,send_time desc) as temp where temp.rowNum between 0 and 10

消息 1033，级别 15，状态 1，第 3 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

select * from ( select * ,ROW_NUMBER() OVER(order by state desc,
send_time desc) as rowNum from T_SYS_MESSAGE where 1=1
) as temp where temp.rowNum between 0 and 10

SELECT SYSTEM_ID,
MAX(SYSTEM_NAME) SYSTEM_NAME,
MAX(SYSTEM_ABRIDGE) SYSTEM_ABRIDGE,
MAX(SUB_SYSTEM_ID) SUB_SYSTEM_ID,
MAX(SUB_SYSTEM_NAME) SUB_SYSTEM_NAME,
MAX(CUSTOM_SYSTEM_ID) CUSTOM_SYSTEM_ID,
MAX(CUSTOM_SYSTEM_NAME) CUSTOM_SYSTEM_NAME FROM T_STANDARD_SYSTEM GROUP BY SYSTEM_ID ORDER BY SYSTEM_ID