Ruoyi链接数据源库，从mysql变成sqlserver后发生语句错误，找不到解决办法
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.WorkCountIncomMapper">
<resultMap type="WorkCountIncom" id="WorkCountIncomResult">
<result property="id" column="id" />
<result property="companyname" column="companyname" />
<result property="companyid" column="companyid" />
<result property="orgname" column="orgname" />
<result property="orgid" column="orgid" />
<result property="byorgname" column="byorgname" />
<result property="byorgid" column="byorgid" />
<result property="linename" column="linename" />
<result property="lineno" column="lineno" />
<result property="drivername" column="drivername" />
<result property="driverno" column="driverno" />
<result property="selfnumber" column="selfnumber" />
<result property="licensenumber" column="licensenumber" />
<result property="icincome" column="icincome" />
<result property="mvolume" column="mvolume" />
<result property="svolume" column="svolume" />
<result property="incom" column="incom" />
<result property="xvolume" column="xvolume" />
<result property="totalmileage" column="totalmileage" />
<result property="operatingmileage" column="operatingmileage" />
<result property="deadheadmileage" column="deadheadmileage" />
<result property="datadate" column="datadate" />
<result property="addtime" column="addtime" />
<result property="updatetime" column="updatetime" />
<result property="dataflag" column="dataflag" />
</resultMap>
<sql id="selectWorkCountIncomVo">
select id, companyname, companyid,orgname, orgid, byorgname, byorgid, linename, lineno, drivername, driverno, selfnumber, licensenumber, icincome, mvolume, svolume, incom, xvolume,totalmileage,operatingmileage,deadheadmileage, datadate, addtime, updatetime, dataflag from ry.work_count_incom
</sql>
<select id="selectWorkCountIncomList" parameterType="WorkCountIncom" resultMap="WorkCountIncomResult">
<include refid="selectWorkCountIncomVo"/>
<where>
<if test="byorgname != null and byorgname != ''"> and byorgname like concat('%', #{byorgname}, '%')</if>
<if test="companyname != null and companyname != ''"> and companyname like concat('%', #{companyname}, '%')</if>
<if test="linename != null and linename != ''"> and linename like concat('%', #{linename}, '%')</if>
<if test="drivername != null and drivername != ''"> and drivername like concat('%', #{drivername}, '%')</if>
<if test="selfnumber != null and selfnumber != ''"> and selfnumber = #{selfnumber}</if>
<if test="datadate != null and datadate != ''"> and datadate = #{datadate}</if>
</where>
</select>
<select id="selectWorkCountIncomById" parameterType="Long" resultMap="WorkCountIncomResult">
<include refid="selectWorkCountIncomVo"/>
where id = #{id}
</select>
<select id="selectWorkCountIncomByIdbyorgname" parameterType="Long" resultMap="WorkCountIncomResult">
<include refid="selectWorkCountIncomVo"/>
where id = #{id} and byorgname=#{username}
</select>
<select id="selectWorkCountIncomBydate" parameterType="WorkCountIncom" resultMap="WorkCountIncomResult">
<include refid="selectWorkCountIncomVo"/>
where datadate between #{starttime} and #{endtime}
</select>
<select id="selectWorkCountIncomBydate1" parameterType="WorkCountIncom" resultMap="WorkCountIncomResult">
<include refid="selectWorkCountIncomVo"/>
where byorgname=#{username} and datadate =#{datadate}
</select>
<select id="selectWorkCountIncomdetailedGroup" parameterType="String" resultMap="WorkCountIncomResult">
SELECT companyname,companyid,orgname,orgid,byorgname,byorgid,linename,lineno,drivername,driverno,selfnumber,licensenumber,
sum(CASE WHEN incometype = 'IC卡' THEN CONVERT ( incom, DECIMAL ( 18, 2 ) ) ELSE 0 END ) AS icincome,
sum(CASE WHEN incomeattr = '免费卡' THEN CONVERT ( volume, DECIMAL ( 18, 2 ) ) ELSE 0 END ) AS mvolume,
sum(CASE WHEN incomeattr = '收费卡' THEN CONVERT ( volume, DECIMAL ( 18, 2 ) ) ELSE 0 END ) AS svolume,
sum(CASE WHEN incometype = '现金' THEN CONVERT ( incom, DECIMAL ( 18, 2 ) ) ELSE 0 END ) AS incom,
sum(CASE WHEN incomeattr = '现金' THEN CONVERT ( volume, DECIMAL ( 18, 2 ) ) ELSE 0 END ) AS xvolume,
datadate
from ry.work_count_incomdetailed where datadate= #{datadate} and byorgname=#{username}
GROUP BY companyname,companyid,orgname,orgid,byorgname,byorgid,linename,lineno,drivername,driverno,selfnumber,licensenumber,datadate order by driverno
</select>
<insert id="insertWorkCountIncom" parameterType="WorkCountIncom" useGeneratedKeys="true" keyProperty="id">
insert into ry.work_count_incom
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="companyname != null">companyname,</if>
<if test="companyid != null">companyid,</if>
<if test="orgname != null">orgname,</if>
<if test="orgid != null">orgid,</if>
<if test="byorgname != null">byorgname,</if>
<if test="byorgid != null">byorgid,</if>
<if test="linename != null">linename,</if>
<if test="lineno != null">lineno,</if>
<if test="drivername != null">drivername,</if>
<if test="driverno != null">driverno,</if>
<if test="selfnumber != null">selfnumber,</if>
<if test="licensenumber != null">licensenumber,</if>
<if test="icincome != null">icincome,</if>
<if test="mvolume != null">mvolume,</if>
<if test="svolume != null">svolume,</if>
<if test="incom != null">incom,</if>
<if test="xvolume != null">xvolume,</if>
<if test="totalmileage != null">totalmileage,</if>
<if test="operatingmileage != null">operatingmileage,</if>
<if test="deadheadmileage != null">deadheadmileage,</if>
<if test="datadate != null">datadate,</if>
<if test="addtime != null">addtime,</if>
<if test="updatetime != null">updatetime,</if>
<if test="dataflag != null">dataflag,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="companyname != null">#{companyname},</if>
<if test="companyid != null">#{companyid},</if>
<if test="orgname != null">#{orgname},</if>
<if test="orgid != null">#{orgid},</if>
<if test="byorgname != null">#{byorgname},</if>
<if test="byorgid != null">#{byorgid},</if>
<if test="linename != null">#{linename},</if>
<if test="lineno != null">#{lineno},</if>
<if test="drivername != null">#{drivername},</if>
<if test="driverno != null">#{driverno},</if>
<if test="selfnumber != null">#{selfnumber},</if>
<if test="licensenumber != null">#{licensenumber},</if>
<if test="icincome != null">#{icincome},</if>
<if test="mvolume != null">#{mvolume},</if>
<if test="svolume != null">#{svolume},</if>
<if test="incom != null">#{incom},</if>
<if test="xvolume != null">#{xvolume},</if>
<if test="totalmileage != null">#{totalmileage},</if>
<if test="operatingmileage != null">#{operatingmileage},</if>
<if test="deadheadmileage != null">#{deadheadmileage},</if>
<if test="datadate != null">#{datadate},</if>
<if test="addtime != null">#{addtime},</if>
<if test="updatetime != null">#{updatetime},</if>
<if test="dataflag != null">#{dataflag},</if>
</trim>
</insert>
<update id="updateWorkCountIncom" parameterType="WorkCountIncom">
update ry.work_count_incom
<trim prefix="SET" suffixOverrides=",">
<if test="companyname != null">companyname = #{companyname},</if>
<if test="companyid != null">companyid = #{companyid},</if>
<if test="orgname != null">orgname = #{orgname},</if>
<if test="orgid != null">orgid = #{orgid},</if>
<if test="byorgname != null">byorgname = #{byorgname},</if>
<if test="byorgid != null">byorgid = #{byorgid},</if>
<if test="linename != null">linename = #{linename},</if>
<if test="lineno != null">lineno = #{lineno},</if>
<if test="drivername != null">drivername = #{drivername},</if>
<if test="driverno != null">driverno = #{driverno},</if>
<if test="selfnumber != null">selfnumber = #{selfnumber},</if>
<if test="licensenumber != null">licensenumber = #{licensenumber},</if>
<if test="icincome != null">ICincome = #{icincome},</if>
<if test="mvolume != null">mvolume = #{mvolume},</if>
<if test="svolume != null">svolume = #{svolume},</if>
<if test="incom != null">incom = #{incom},</if>
<if test="xvolume != null">xvolume = #{xvolume},</if>
<if test="totalmileage != null">totalmileage = #{totalmileage},</if>
<if test="operatingmileage != null">operatingmileage = #{operatingmileage},</if>
<if test="deadheadmileage != null">deadheadmileage = #{deadheadmileage},</if>
<if test="datadate != null">datadate = #{datadate},</if>
<if test="addtime != null">addtime = #{addtime},</if>
<if test="updatetime != null">updatetime = #{updatetime},</if>
<if test="dataflag != null">dataflag = #{dataflag},</if>
</trim>
where id = #{id}
</update>
<update id="updateWorkCountIncomByecah" parameterType="WorkCountIncom">
update ry.work_count_incom
<trim prefix="SET" suffixOverrides=",">
<if test="companyname != null">companyname = #{companyname},</if>
<if test="companyid != null">companyid = #{companyid},</if>
<if test="orgname != null">orgname = #{orgname},</if>
<if test="orgid != null">orgid = #{orgid},</if>
<if test="byorgname != null">byorgname = #{byorgname},</if>
<if test="byorgid != null">byorgid = #{byorgid},</if>
<if test="linename != null">linename = #{linename},</if>
<if test="lineno != null">lineno = #{lineno},</if>
<if test="drivername != null">drivername = #{drivername},</if>
<if test="driverno != null">driverno = #{driverno},</if>
<if test="selfnumber != null">selfnumber = #{selfnumber},</if>
<if test="licensenumber != null">licensenumber = #{licensenumber},</if>
<if test="icincome != null">ICincome = #{icincome},</if>
<if test="mvolume != null">mvolume = #{mvolume},</if>
<if test="svolume != null">svolume = #{svolume},</if>
<if test="incom != null">incom = #{incom},</if>
<if test="xvolume != null">xvolume = #{xvolume},</if>
<if test="totalmileage != null">totalmileage = #{totalmileage},</if>
<if test="operatingmileage != null">operatingmileage = #{operatingmileage},</if>
<if test="deadheadmileage != null">deadheadmileage = #{deadheadmileage},</if>
<if test="datadate != null">datadate = #{datadate},</if>
<if test="addtime != null">addtime = #{addtime},</if>
<if test="updatetime != null">updatetime = #{updatetime},</if>
<if test="dataflag != null">dataflag = #{dataflag},</if>
</trim>
where companyid= #{companyid} and orgid = #{orgid} and byorgid=#{byorgid}and lineno = #{lineno} and driverno = #{driverno} and selfnumber = #{selfnumber} and datadate=#{datadate}
</update>
<delete id="deleteWorkCountIncomById" parameterType="Long">
delete from ry.work_count_incom where id = #{id}
</delete>
<delete id="deleteWorkCountIncomByIds" parameterType="String">
delete from ry.work_count_incom where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<delete id="deleteWorkCountIncomByDate" parameterType="String">
delete from ry.work_count_incom where datadate = #{datadate} and byorgname=#{username}
</delete>
</mapper>
SQL: SELECT TOP 10 id, rundatadate, companyname, companyid, orgname, orgid, byorgname, byorgid, linename, lineno, selfno, powertype, powerid, drivername, driverno, stewardname, stewardno, totalmileage, operatingmileage, deadheadmileage, workingday, intactday, dailymileage, workingtime, workingtimetwo, businesstime, tripsnumber, punctualtimes, freeexam, earlytimes, latetimes, failuresnumber, failurescarnumber, failurescartime, failurescarmileage, syctime, sycman, addman, addtime, editman, edittime, datassource, datastatus, bumen_bh FROM (SELECT ROW_NUMBER() OVER (ORDER BY RAND()) PAGE_ROW_NUMBER, id, rundatadate, companyname, companyid, orgname, orgid, byorgname, byorgid, linename, lineno, selfno, powertype, powerid, drivername, driverno, stewardname, stewardno, totalmileage, operatingmileage, deadheadmileage, workingday, intactday, dailymileage, workingtime, workingtimetwo, businesstime, tripsnumber, punctualtimes, freeexam, earlytimes, latetimes, failuresnumber, failurescarnumber, failurescartime, failurescarmileage, syctime, sycman, addman, addtime, editman, edittime, datassource, datastatus, bumen_bh FROM (SELECT m.id, m.rundatadate, m.companyname, m.companyid, m.orgname, m.orgid, m.byorgname, m.byorgid, m.linename, m.lineno, m.selfno, m.powertype, m.powerid, m.drivername, m.driverno, m.stewardname, m.stewardno, m.totalmileage, m.operatingmileage, m.deadheadmileage, m.workingday, m.intactday, m.dailymileage, m.workingtime, m.workingtimetwo, m.businesstime, m.tripsnumber, m.punctualtimes, m.freeexam, m.earlytimes, m.latetimes, m.failuresnumber, m.failurescarnumber, m.failurescartime, m.failurescarmileage, m.syctime, m.sycman, m.addman, m.addtime, m.editman, m.edittime, m.datassource, m.datastatus, m.bumen_bh FROM ry.work_count_mileage m LEFT JOIN ry.sys_dept d ON m.bumen_bh = d.bumen_bh) AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS WHERE PAGE_ROW_NUMBER > 0 ORDER BY PAGE_ROW_NUMBER<EOL><EOL>### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'lineno' 附近有语法错误。<EOL>; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'lineno' 附近有语法错误。]
将lineno都用[]括起来，有两个PAGE_TABLE_ALIAS，将其中一个名字改一下
如果是切换数据库类型的话，要注意sql语句的语法，mysql 和sqlserver 语法有一些不同的地方
lineno是关键词，试一下
1、包sql语句拷贝出来，放在sqlserver中运行，看是不是报同样的错误
2、如果是同样错误，检查表字段和查询字段是否一一对应
3、如果没有报错，检查项目中数据库连接是否正确
报错提到：关键字 'lineno' 附近有语法，其实就可以定位到错，在SQL server中LINENO是关键字,不可作为表字段名，你可以将它别名一下即可。
若有帮助，还望采纳，点击回答右侧采纳即可。
解决方法：（按照下面方式进行全文替换，箭头左边的替换成右边）
```sql
datetime(3) ==> datetime
datetime(0) ==> datetime
TIMESTAMP(3) ==>TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ==> DEFAULT '0000-00-00 00:00:00'
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ==> DEFAULT '0000-00-00 00:00:00'
DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) ==> DEFAULT '0000-00-00 00:00:00'
DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ==> DEFAULT '0000-00-00 00:00:00'
如有帮助，请采纳，万分感谢！！！
lineno是关键词，试一下
`lineno`
sqlserver 关键词列表
https://blog.csdn.net/weishuai90/article/details/128813486
https://www.cnblogs.com/claricre/p/11401090.html
问题很明显了，SQL server中LINENO是关键字,不可作为表字段，设计表时不要使用关键字作为表字段，可以简写。
若有帮助，记得采纳哦。
lineno是关键字，如果某个字段是sqlserver关键字，可以用中括号把字段名称括起来，等同于mysql的倒单引号和oracle的双引号：
-- sqlserver报错
SELECT lineno FROM a;
-- sqlserver正确写法
SELECT [lineno] FROM a;
-- oracle报错
SELECT SELECT FROM a;
-- oracle正确写法
SELECT "SELECT" FROM a;
-- mysql报错
SELECT SELECT FROM a;
-- mysql正确写法
SELECT `SELECT` FROM a;
sqlserver 建表语句也发下