项目上有一个查询sql
SELECT
count(1)
FROM
(
SELECT
T.RID,
T.INS_UNIT_UID,
T.BHK_CODE,
T.AGE,
T.RCD_DATE,
T.PE_DATE,
T.MARITAL_STATUS,
T.FAMILY_ADDR,
T.MOBILE_TEL,
T.CUR_ADDR_ZONE_CODE,
T.SETT_METHOD,
T.SETT_STATS,
T.RHK_TYPE,
T1.CRPT_NAME AS WORK_CRPT_NAME,
T2.CRPT_NAME AS PER_CRPT_NAME,
T.PERSON_NAME,
T3.IDC,
MIN(D.JDGDAT) AS JDGDAT,
MIN(D.EXAM_DOCTOR) AS EXAM_DOCTOR,
MIN(D.EXVE_TIME) AS EXVE_TIME,
MIN(D.EXVE_DOCTOR) AS EXVE_DOCTOR,
MIN(T10.FIRST_PRINT_DATE) AS FIRST_PRINT_DATE,
MIN(T4.PE_TYPE) AS PE_TYPE,
GROUP_CONCAT(DISTINCT T4.PE_TYPE) AS PE_TYPES,
GROUP_CONCAT(DISTINCT T4.ORG_NAME) AS ORG_NAME,
GROUP_CONCAT(DISTINCT T4.WORK_TYPE) AS WORK_TYPES,
GROUP_CONCAT(DISTINCT T4.WORK_NUM) AS WORK_NUM,
GROUP_CONCAT(DISTINCT T4.WORK_STATE) AS WORK_STATE,
GROUP_CONCAT(DISTINCT T5.HAZARDS) AS HAZARDS_STR,
GROUP_CONCAT(DISTINCT T6.PRFRAYSRT) AS PRFRAYSRTS,
GROUP_CONCAT(DISTINCT T8.PROG_NAME) AS BHKSCH_NAMES,
SUM(T9.PROG_AGREE_AMOUNT) AS PROG_AGREE_AMOUNT_SUM,
SUM(T9.AMOUT_RECEIVABLE) AS AMOUT_RECEIVABLE_SUM,
GROUP_CONCAT(DISTINCT T9.SETT_METHOD ORDER BY T.SETT_METHOD) AS SETT_METHOD_NAMES,
CASE
WHEN T.COM_TAG != 0
AND EXISTS (
SELECT
1
FROM
td_tj_dgn dgn
WHERE
dgn.IF_DELETE = FALSE
AND dgn.PE_ID = T.RID
AND dgn.COM_TAG IN (
5, 6
)
)
AND EXISTS (
SELECT
1
FROM
TD_TJ_BHKRPT bhk
WHERE
bhk.IF_DELETE = FALSE
AND bhk.PE_ID = T.RID
AND bhk.IF_PRINT = TRUE
) THEN 6
WHEN T.COM_TAG != 0
AND EXISTS (
SELECT
1
FROM
td_tj_dgn dgn
WHERE
dgn.IF_DELETE = FALSE
AND dgn.PE_ID = T.RID
AND dgn.COM_TAG IN (
5, 6
)
)
AND NOT EXISTS (
SELECT
1
FROM
TD_TJ_BHKRPT bhk
WHERE
bhk.IF_DELETE = FALSE
AND bhk.PE_ID = T.RID
AND bhk.IF_PRINT = TRUE
) THEN 5
WHEN T.PE_DATE IS NOT NULL
AND NOT EXISTS (
SELECT
1
FROM
TD_TJ_FLWCNT F
WHERE
F.IF_DELETE = FALSE
AND F.BHK_ID = T.RID
AND F.IF_COMPLETE = FALSE
)
AND EXISTS (
SELECT
1
FROM
td_tj_dgn dgn
WHERE
dgn.IF_DELETE = FALSE
AND dgn.PE_ID = T.RID
AND dgn.COM_TAG IN (
2, 3
)
) THEN 4
WHEN T.PE_DATE IS NOT NULL
AND NOT EXISTS (
SELECT
1
FROM
TD_TJ_FLWCNT F
WHERE
F.IF_DELETE = FALSE
AND F.BHK_ID = T.RID
AND F.IF_COMPLETE = FALSE
)
AND EXISTS (
SELECT
1
FROM
td_tj_dgn dgn
WHERE
dgn.IF_DELETE = FALSE
AND dgn.PE_ID = T.RID
AND dgn.COM_TAG IN (
1, 4
)
) THEN 3
WHEN T.PE_DATE IS NOT NULL
AND EXISTS (
SELECT
1
FROM
TD_TJ_FLWCNT F
WHERE
F.IF_DELETE = FALSE
AND F.BHK_ID = T.RID
AND F.IF_COMPLETE = FALSE
) THEN 2
WHEN T.PE_DATE IS NULL THEN 1
END AS BHK_TYPE
FROM
TD_TJ_BHK T
LEFT JOIN TB_TJ_CRPT T1 ON
T.WORK_CRPT_ID = T1.RID
LEFT JOIN TB_TJ_CRPT T2 ON
T.PER_CRPT_ID = T2.RID
LEFT JOIN TD_TJ_EMP T3 ON
T.EMP_ID = T3.RID
LEFT JOIN TD_TJ_WORK_INFO T4 ON
T4.BHK_ID = T.RID
AND T4.IF_DELETE = FALSE
LEFT JOIN TD_TJ_BADRSN T5 ON
T5.WORK_INFO_ID = T4.RID
AND T5.IF_DELETE = FALSE
LEFT JOIN TD_TJ_PRFRAYSRT T6 ON
T6.WORK_INFO_ID = T4.RID
AND T6.IF_DELETE = FALSE
LEFT JOIN TD_TJ_BHKSCH T7 ON
T7.BHK_ID = T.RID
AND T7.IF_DELETE = FALSE
LEFT JOIN TB_TJ_BHKSCH T8 ON
T8.RID = T7.BHKSCH_ID
LEFT JOIN TD_TJ_BHKCHG T9 ON
T9.BHK_ID = T.RID
AND T9.IF_DELETE = FALSE
LEFT JOIN (
SELECT
A.PE_ID,
A.JDGDAT,
A.EXAM_DOCTOR,
A.EXVE_TIME,
A.EXVE_DOCTOR
FROM
TD_TJ_DGN A
WHERE
A.IF_DELETE = FALSE
GROUP BY
A.PE_ID
) AS D ON
D.PE_ID = T.RID
LEFT JOIN TD_TJ_BHKRPT T10 ON
T4.RID = T10.WORK_INFO_ID
AND T4.BHK_ID = T10.PE_ID
AND T10.IF_DELETE = FALSE
LEFT JOIN TD_TJ_BEBHK T11 ON
T11.BHK_ID = T.RID
AND T11.IF_DELETE = FALSE
LEFT JOIN TB_TJ_BHKLABEL T12 ON
T12.BHK_ID = T.RID
AND T12.IF_DELETE = FALSE
WHERE
T.IF_DELETE = FALSE
AND T.INS_UNIT_UID = ‘C797257679BD11ECB932000C29FC9E70’
AND DATE_FORMAT(T.RCD_DATE, ‘%Y-%m-%d’)>= DATE_FORMAT(‘2024-08-30 00:00:00.0’, ‘%Y-%m-%d’)
AND DATE_FORMAT(T.RCD_DATE, ‘%Y-%m-%d’)<= DATE_FORMAT(‘2024-08-30 00:00:00.0’, ‘%Y-%m-%d’)
GROUP BY
T.RID
) tmp
tmp子查询执行的结果为1条数据
那么基于tmp子查询的结果count(1)。最终结果应该是1才对。但是实际运行的结果是100。同样的sql，在mysql上执行结果就是为1。tidb与mysql在执行过程中是有区别吗？哪位大神帮忙解释解释？感谢。