这个sql目前报错:导致 MySQL 抛出 Data truncation: Data too long for column 'FRONTLINEHQ_CODE' at row 4665 异常。具体来说,第 4665 行的数据长度超过了字段允许的最大长度限制。,如何添加日志,知道更详细的行数据:
SELECT
GROUP_CONCAT(
IF(
info.FapprovalAuthorityorg IN ('公司CEO', '常务董事会'),
info.FapprovalAuthorityorg,
'-'
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FAPPROVALAUTHORITYORG,
GROUP_CONCAT(
ifnull(FpositionDept, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FpositionDept,
GROUP_CONCAT(
ifnull(fappointedpostion, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) fappointedpostion,
GROUP_CONCAT(
ifnull(fmaxgrades, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) fmaxgrades,
GROUP_CONCAT(
ifnull(fmingrades, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) fmingrades,
GROUP_CONCAT(
ifnull(DATE(fappointeddate), '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) fappointeddate,
GROUP_CONCAT(
ifnull(IF(FPOSITIONID = 0, NULL, FPOSITIONID), '-')
ORDER BY
info.FMANAGERFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FPOSITIONID,
GROUP_CONCAT(
CONCAT(
ifnull(IF(fmanagerflag = 'Y', '管理岗', '非管理岗'), ''),
' | ',
ifnull(fappointedjobfamilycn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobclasscn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclasscn, ''),
' | ',
ifnull(fappointedpostion, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) fappointinfo,
GROUP_CONCAT(
CONCAT(
ifnull(
IF(
fmanagerflag = 'Y',
'Management position',
'Non-management position'
),
''
),
' | ',
ifnull(fappointedjobfamilyen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobclassen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclassen, ''),
' | ',
ifnull(fappointedpostionen, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) fappointinfoEn,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', 1), '-'),
' | ',
ifnull(fappointedpostion, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) fappointinfo2,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', 1), '-'),
' | ',
ifnull(fappointedpostion, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
)
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) FAPPOINTINFOSIMPLE,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', - 1), '-'),
' | ',
ifnull(FAPPOINTEDPOSTIONEN, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
)
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) FAPPOINTINFOSIMPLEEN,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', - 1), '-'),
' | ',
ifnull(FAPPOINTEDPOSTIONEN, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) fappointinfoEn2,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', 1), '-'),
'~',
ifnull(fappointedpostion, '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FAPPOINTINFOEXPORT,
GROUP_CONCAT(
CONCAT(
ifnull(substring_index(FpositionDept, '/', - 1), '-'),
'~',
ifnull(FAPPOINTEDPOSTIONEN, '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FAPPOINTINFOEXPORTEN,
GROUP_CONCAT(
CONCAT(
ifnull(IF(fmanagerflag = 'Y', '管理岗', '非管理岗'), ''),
' | ',
ifnull(substring_index(FpositionDept, '/', 1), '-'),
' | ',
ifnull(fappointedjobfamilycn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobclasscn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclasscn, ''),
' | ',
ifnull(fappointedpostion, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) FAPPOINTINFO_EXPAND_CN,
GROUP_CONCAT(
CONCAT(
ifnull(
IF(
fmanagerflag = 'Y',
'Management position',
'Non-management position'
),
''
),
' | ',
ifnull(substring_index(FpositionDept, '/', - 1), '-'),
' | ',
ifnull(fappointedjobfamilyen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobclassen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclassen, ''),
' | ',
ifnull(fappointedpostionen, '-'),
' | ',
IF(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
AND fmaxgrades = fmingrades,
fmaxgrades,
concat(
ifnull(fmingrades, ''),
IF(
(
fmingrades IS NOT NULL
AND fmaxgrades IS NOT NULL
)
OR (
fmingrades IS NULL
AND fmaxgrades IS NULL
),
'-',
''
),
ifnull(fmaxgrades, '')
)
),
' | ',
ifnull(DATE_FORMAT(fappointeddate, '%Y-%m-%d'), '-')
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '\n'
) FAPPOINTINFO_EXPAND_EN,
GROUP_CONCAT(
ifnull(
IF(
fmanagerflag = 'Y',
fappointedjobfamilyCn,
CONCAT(
ifnull(fappointedjobfamilycn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobclasscn, ''),
IF(
fappointedjobclasscn IS NULL
AND fappointedjobsubclasscn IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclasscn, '')
)
),
'-'
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FAPPOINTEDJOBCOMPTCN,
GROUP_CONCAT(
ifnull(
IF(
fmanagerflag = 'Y',
fappointedjobfamilyEn,
CONCAT(
ifnull(fappointedjobfamilyen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobclassen, ''),
IF(
fappointedjobclassen IS NULL
AND fappointedjobsubclassen IS NULL,
'',
'-'
),
ifnull(fappointedjobsubclassen, '')
)
),
'-'
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FAPPOINTEDJOBCOMPTEN,
GROUP_CONCAT(
ifnull(info.fpositionType, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR '|'
) fpositionType,
GROUP_CONCAT(
ifnull(info.FJobLevel, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FJobLevel,
GROUP_CONCAT(
ifnull(FISSATISFIEDREQUIREMENT, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FISSATISFIEDREQUIREMENT,
GROUP_CONCAT(
ifnull(
(
SELECT
IF(
LENGTH(ifnull(trim(opti.CQ_LEVEL), '')) = 0,
NULL,
opti.CQ_LEVEL
)
FROM
t_hr_core_org_position_ti opti
WHERE
opti.POSITION_ID = info.FPOSITIONID
AND opti.POSITION_ID != 0
LIMIT
1
),
'-'
)
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FCQREQUIREMENTS,
GROUP_CONCAT(
ifnull(FRONTLINEHQ_CODE, '-')
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
) FRONTLINEHQ_CODE,
CASE
WHEN COUNT(FRONTLINEHQ_ZH_NAME) = 0 THEN '-' -- 分组全空时返回单个-
ELSE GROUP_CONCAT(
IFNULL(FRONTLINEHQ_ZH_NAME, '-') -- 部分为空时替换单个-
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
)
END AS FRONTLINEHQ_ZH_NAME,
CASE
WHEN COUNT(FRONTLINEHQ_EN_NAME) = 0 THEN '-' -- 分组全空时返回单个-
ELSE GROUP_CONCAT(
IFNULL(FRONTLINEHQ_EN_NAME, '-') -- 部分为空时替换单个-
ORDER BY
info.FMANAGERFLAG DESC,
info.FREFERENCEFLAG,
info.FAPPOINTMENTSUBTYPE ASC,
info.fmaxgrades DESC,
info.FAPPOINTEDDATE DESC SEPARATOR ' | '
)
END AS FRONTLINEHQ_EN_NAME,
p.HW_HR_ID fid,
info.FEMPLOYEENUMBER FEMPNO
FROM
t_epm_appointed_info info
JOIN t_epm_person_ti p ON info.FEMPLOYEENUMBER = p.employee_number
AND p.HW_HR_ID IS NOT NULL
AND p.LABORREL_STATUS = 'EMP'
GROUP BY
info.FEMPLOYEENUMBER