sql语句在SQLserver可以运行,跑的时候报错,说by附近有问题,求大神指点。

哆啦阿梦 2019-05-09 04:22:43
sql语句在SQLserver可以运行,跑的时候报错,说by附近有问题,求大神指点。
下面是报错信息:

```
[ERROR] 2019-05-09 16:07:06.262 [http-bio-8080-exec-2] com.alibaba.druid.filter.stat.StatFilter - merge sql error, dbType sqlserver, druid-1.1.5, sql : SELECT count(DISTINCT jhta.EXECUTION_)AS num



FROM BUSINESS_WF_FILE bwf
LEFT JOIN BUSINESS_WHGL bw ON bwf.fwzh=CAST(bw.whid AS VARCHAR(8))
LEFT JOIN BUSINESS_GWFL bg ON bwf.gwfl=CAST(bg.gwflId AS VARCHAR(8))
LEFT JOIN JBPM4_HIST_TASK jhta ON substring(jhta.EXECUTION_,1,patindex('%[[0-9][.]]%',jhta.EXECUTION_+'.1.'))=(bwf.lcslKey+'.'+cast(bwf.ID AS VARCHAR(8)))
LEFT JOIN SYS_USER su ON jhta.ASSIGNEE_=(su.LOGIN_NAME+'/'+su.NAME)
WHERE jhta.EXECUTION_ IS NOT NULL AND bw.zhName IS NOT NULL
AND bg.mc IS NOT NULL



AND jhta.DBID_ IN (SELECT max(DBID_) FROM JBPM4_HIST_TASK WHERE SUPERTASK_ IS NULL GROUP BY EXECUTION_)
AND jhta.EXECUTION_ IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_)
AND substring(jhta.EXECUTION_,1,5) = 'fawen'
AND jhta.STATE_ = 'reading'
AND datediff(DAY,jhta.CREATE_,convert(VARCHAR(10),GETDATE(),120)) <= 90



GROUP BY



/* GROUP BY so.ORG_NAME,so.ORG_CODE
ORDER BY so.ORG_CODE*/
com.alibaba.druid.sql.parser.EOFParserException: EOF
at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:758)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerExprParser.primary(SQLServerExprParser.java:90)
at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:85)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseGroupByItem(SQLSelectParser.java:491)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseGroupBy(SQLSelectParser.java:397)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerSelectParser.query(SQLServerSelectParser.java:164)
at com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerSelectParser.select(SQLServerSelectParser.java:47)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseSelect(SQLStatementParser.java:2371)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:147)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:70)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:61)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:37)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:147)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:648)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:311)
```

下面是第二段
```
org.springframework.jdbc.UncategorizedSQLException:
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: “BY”附近有语法错误。
### The error may exist in com/harme/jbpm4workflow/entity/mapper/BusinessTongjiMapper.xml
### The error may involve com.harme.jbpm4workflow.entity.mapper.BusinessTongjiMapper.findFaWenTongji-Inline
### The error occurred while setting parameters
### SQL: SELECT count(DISTINCT jhta.EXECUTION_)AS num FROM BUSINESS_WF_FILE bwf LEFT JOIN BUSINESS_WHGL bw ON bwf.fwzh=CAST(bw.whid AS VARCHAR(8)) LEFT JOIN BUSINESS_GWFL bg ON bwf.gwfl=CAST(bg.gwflId AS VARCHAR(8)) LEFT JOIN JBPM4_HIST_TASK jhta ON substring(jhta.EXECUTION_,1,patindex('%[[0-9][.]]%',jhta.EXECUTION_+'.1.'))=(bwf.lcslKey+'.'+cast(bwf.ID AS VARCHAR(8))) LEFT JOIN SYS_USER su ON jhta.ASSIGNEE_=(su.LOGIN_NAME+'/'+su.NAME) WHERE jhta.EXECUTION_ IS NOT NULL AND bw.zhName IS NOT NULL AND bg.mc IS NOT NULL AND jhta.DBID_ IN (SELECT max(DBID_) FROM JBPM4_HIST_TASK WHERE SUPERTASK_ IS NULL GROUP BY EXECUTION_) AND jhta.EXECUTION_ IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_) AND substring(jhta.EXECUTION_,1,5) = 'fawen' AND jhta.STATE_ = 'reading' AND datediff(DAY,jhta.CREATE_,convert(VARCHAR(10),GETDATE(),120)) <= 90 GROUP BY /* GROUP BY so.ORG_NAME,so.ORG_CODE ORDER BY so.ORG_CODE*/
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: “BY”附近有语法错误。
; uncategorized SQLException for SQL []; SQL state [S0001]; error code [102]; “BY”附近有语法错误。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: “BY”附近有语法错误。

```

这是sql语句

```
<select id="findFaWenTongji" parameterType="map" resultType="map">

SELECT count(DISTINCT jhta.EXECUTION_)AS num
<if test="flag == 'gwfl' ">
,bg.mc as name
</if>
<if test="flag == 'zh' ">
,bw.zhName as name
</if>
<if test="flag == 'isgk' ">
,bwf.isgk as name
</if>
FROM BUSINESS_WF_FILE bwf
LEFT JOIN BUSINESS_WHGL bw ON bwf.fwzh=CAST(bw.whid AS VARCHAR(8))
LEFT JOIN BUSINESS_GWFL bg ON bwf.gwfl=CAST(bg.gwflId AS VARCHAR(8))
LEFT JOIN JBPM4_HIST_TASK jhta ON substring(jhta.EXECUTION_,1,patindex('%[[0-9][.]]%',jhta.EXECUTION_+'.1.'))=(bwf.lcslKey+'.'+cast(bwf.ID AS VARCHAR(8)))
LEFT JOIN SYS_USER su ON jhta.ASSIGNEE_=(su.LOGIN_NAME+'/'+su.NAME)
WHERE jhta.EXECUTION_ IS NOT NULL AND bw.zhName IS NOT NULL
AND bg.mc IS NOT NULL
<!-- <if test="year != null ">
and YEAR(bwf.fwdate) = #{year}
</if>
<if test="month != null ">
and MONTH(bwf.fwdate) = #{month}
</if>-->
<if test="state=='ended'">
AND jhta.execution_ NOT IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_)
</if>
<if test="state==zaiban">
AND jhta.DBID_ IN (SELECT max(DBID_) FROM JBPM4_HIST_TASK WHERE SUPERTASK_ IS NULL GROUP BY EXECUTION_)
AND jhta.EXECUTION_ IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_)
AND substring(jhta.EXECUTION_,1,5) = 'fawen'
AND jhta.STATE_ = 'reading'
AND datediff(DAY,jhta.CREATE_,convert(VARCHAR(10),GETDATE(),120)) <![CDATA[<=]]> 90
</if>
<if test="state=='daiban'">
AND jhta.DBID_ IN (SELECT max(DBID_) FROM JBPM4_HIST_TASK WHERE SUPERTASK_ IS NULL GROUP BY EXECUTION_)
AND jhta.EXECUTION_ IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_)
AND jhta.STATE_ IS NULL
AND datediff(DAY,jhta.CREATE_,convert(VARCHAR(10),getdate(),120)) <![CDATA[<=]]> 90
</if>
<if test="state=='yichang'">
AND jhta.DBID_ IN (SELECT max(DBID_) FROM JBPM4_HIST_TASK WHERE SUPERTASK_ IS NULL GROUP BY EXECUTION_)
AND jhta.EXECUTION_ IN (SELECT EXECUTION_ID_ FROM JBPM4_TASK GROUP BY EXECUTION_ID_)
AND jhta.DBID_ IS NOT NULL
AND datediff(DAY,jhta.CREATE_,convert(VARCHAR(10),getdate(),120)) <![CDATA[>]]> 90
</if>
GROUP BY
<if test="flag == 'gwfl' ">
bg.mc
</if>
<if test="flag == 'zh' ">
,bw.zhName
</if>
<if test="flag == 'isgk' ">
,bwf.isgk
</if>
/* GROUP BY so.ORG_NAME,so.ORG_CODE
ORDER BY so.ORG_CODE*/
</select>

```
...全文
58 点赞 收藏 1
写回复
1 条回复
freekaiQaQ 2019年05月10日

  GROUP BY
        <if test="flag == 'gwfl' ">
            bg.mc
        </if>
        <if test="flag == 'zh' ">
            ,bw.zhName
        </if>
        <if test="flag == 'isgk' ">
            ,bwf.isgk
        </if>
group by 后面的3个if分支 按照你的逻辑应该至多走进一个吧。 1> 如果一个都不符合,表达式即 group by 后面没有分组字段,会报错的。 2> 还有就是后面两个if中 也不应该有 逗号【,】吧
回复 点赞
发动态
发帖子
Web 开发
创建于2007-09-28

5.2w+

社区成员

34.1w+

社区内容

Java Web 开发
社区公告
暂无公告