【TiDB 使用环境】生产环境
【TiDB 版本】7.5.4
【操作系统】centos7
【部署方式】自建
问题：
SELECT
– ‘FTMC’ AS TENANT,
– B.PERSONID AS PERSON_ID,
B.EMPLOYEEID AS ‘工号’,
B.TRUENAME AS ‘姓名’,
COUNT(B.BSK) AS ‘合计病事旷工作天数’,
MAX(B.CONTSEQ) AS ‘连续病事旷最大工作天数’
FROM(
SELECT
A.PERSONID,
A.EMPLOYEEID,
A.TRUENAME,
A.BSK,
@COUNT := IF(@PREV_CODE = CONCAT(A.BSK,A.EMPLOYEEID) , @COUNT + 1, 1) AS CONTSEQ,
@PREV_CODE := CONCAT(A.BSK,A.EMPLOYEEID) AS PREV
FROM
(
SELECT
T1.PERSONID,
T3.CLASSCODE,
T1.TIMECARDDATE,
T1.BELONGDATE,
EMPLOYEEID,
TRUENAME,
MAX(CASE WHEN T3.CLASSCODE IN (‘B01’, ‘B02’, ‘C01’, ‘C02’, ‘H01’, ‘H02’, ‘F03’) AND T2.CALHOURS>0 AND T1.PAYHOURS>0 THEN 2 END) AS ‘BSK’,
ROW_NUMBER() OVER (PARTITION BY TRUENAME,MAX(CASE WHEN T3.CLASSCODE IN (‘B01’, ‘B02’, ‘C01’, ‘C02’, ‘H01’, ‘H02’, ‘F03’) AND T2.CALHOURS>0 AND T2.CALHOURS>0 THEN 2 END)) AS ROW_NUM
FROM
xx
INNER JOIN xx T2 ON T2.PERSONID = PERSONID
AND T2.ISDELETED = ‘0’ AND T2.CALENDARDATE BETWEEN ‘2025-01-01’ AND ‘2025-06-30’
INNER JOIN xx T1 ON T2.PERSONID = T1.PERSONID
AND T2.CALENDARDATE = T1.TIMECARDDATE – 时间
INNER JOIN xx T3 ON T3.ID = T1.PAYCODE
WHERE ACCESSIONSTATE=‘2’
AND T2.CALHOURS > 0
AND left(employeeid,4) !=‘FTMC’
GROUP BY PERSONID,T2.CALENDARDATE
ORDER BY PERSONID,T2.CALENDARDATE,T1.BELONGDATE ASC
) A)B GROUP BY B.PERSONID HAVING MAX(B.CONTSEQ)>=20
在执行这个SQL的时候，返回的结果是随机的，有办法解决吗
主要是这段
这段没看懂，@COUNT和@PREV_CODE是啥
产线排序之后的编号
order by 不应该放最外层吗？
tidb是分布式数据库，数据汇总时是从各个tikv节点取数据，如果不排序，无法保证各个节点的数据顺序，会导致每次获取不一致的情况，所以sql最外层要有RDER BY关键字
2 个赞
感谢两位啊，我再验证一下
sql中缺少最终的order by
需要手工在最外层加order by才能保证返回结果是固定的。
此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。