MyBatisCodeHelper-Pro
MyBatisCodeHelper-Pro copied to clipboard
Published
20 hours ago •
gejun123456
gejun123456
column无法从resultmap对应的select语句中解析出来
如题，sql中使用了WITH... AS... 语法，查询的结果集中作为"临时表"中的字段，无法被识别到resultmap
WITH company_temp AS (
SELECT oc.id, oc.title, ucr.spa_num FROM t_system_organization_company oc
LEFT JOIN t_system_user_and_company_relation ucr ON ucr.org_company_id = oc.id
),
project_dept_temp AS (
SELECT opd.id, opd.title, udr.spa_num FROM t_system_organization_project_dept opd
LEFT JOIN t_system_user_and_dept_relation udr ON udr.org_dept_id = opd.id
),
user_temp AS (
SELECT u.spa_num, u.nickname, u.birthday, u.rylb, u.xmbrz, u.zwcjxl,
c.id company_id, c.title company_name, pd.id dept_id, pd.title dept_name
FROM t_system_user u
LEFT JOIN company_temp c ON c.spa_num = u.spa_num
LEFT JOIN project_dept_temp pd ON pd.spa_num = u.spa_num
),
user_temp_norepeat AS (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY spa_num ORDER BY spa_num) AS rn FROM user_temp
) t
WHERE t.rn = 1
),
examinee_temp AS (
SELECT e.*, u.nickname, u.birthday, u.rylb, u.xmbrz, u.zwcjxl
FROM t_assess_kpi_examinee e
LEFT JOIN user_temp_norepeat u ON e.spa_num = u.spa_num
)
SELECT e.id, e.spa_num, e.nickname, e.birthday, e.rylb, e.xmbrz, e.zwcjxl, e.org_company, e.org_project_dept,
g.id grade_id, g.grade level, g.gmt_modified date
FROM t_assess_kpi_grade g
LEFT JOIN examinee_temp e ON e.id = g.examinee_id
WHERE g.assess_id = #{assessId,jdbcType=BIGINT}
ORDER BY e.spa_num
OFFSET #{p.pageNum} ROWS FETCH NEXT #{p.pageSize} ROWS ONLY
