博客

场景：

1. 
   需要对某表book按照paydate分组 然后sum(fee)并取opendate最大最小值
   

SELECT sum(CAST(accrualfee AS decimal (38, 4))) AS fee, MIN(opendate) AS sDate, MAX(opendate) AS eDate, paydate as paydate 
FROM book WHERE paydate  >=  '20210707' and paydate  <=  '20220707' GROUP BY paydate order by paydate desc

2. 
   项目运行的时候报错： Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
   

因为pagehelper分页的时候 会把上面sql前面拼

select count(0)

可以加入在句子前面 selec top 100 percent * 解决这个问题，测试可以解决

select count(0) from (  select top 100 percent *    from (SELECT sum(CAST(accrualfee AS decimal (38, 4))) AS fee, MIN(opendate) AS sDate, MAX(opendate) AS eDate, paydate as paydate 
FROM book WHERE paydate  >=  '20210707' and paydate  <=  '20220707' GROUP BY paydate ) as temp1 order by paydate desc  ) tmp_count

3. 
   虽然在dbeaver上面执行上述sql没有问题了，但是放到项目中依然报错了，最后找到最终方案
   

//修改前
PageHelper.startPage(pageNum,pageSize);
//修改后
PageHelper.startPage(pageNum,pageSize,"paydate DESC");

然后sql 如下完美解决问题

SELECT sum(CAST(accrualfee AS decimal (38, 4))) AS fee, MIN(opendate) AS sDate, MAX(opendate) AS eDate, paydate as paydate 
FROM book WHERE paydate  >=  '20210707' and paydate  <=  '20220707' GROUP BY paydate

4. 
   总结：
   

主要项目同时连接了oracle和sqlserver，形成了定式思维，比如如果sql server “AS”,如果你在后面加上引号 或者有的有有的没有又会报错

[5309] 开窗函数和 NEXT VALUE FOR 函数不支持常量作为 ORDER BY 子句表达式。

或者

[8155] 没有为 ‘PAGE_TABLE_ALIAS’ 的列 1 指定任何列名称。