mysql - 在MYSQL中创建表时进行数据转换

创建表时出错，但是SQL正在检索结果

请找到下面的代码，下面的SQL本身可以正常检索结果！

SELECT
CASE WHEN J.JOBID = DJ.JOBNUMBER
     THEN DJ.JOBID ELSE J.JOBID
                 END AS JOBID,
J.DISTRICTID,
CASE WHEN D.DISTRICTID = J.DISTRICTID
     THEN D.NAME ELSE NULL
                 END AS DISTRICT_NAME,
D.SEGMENTID,
CASE WHEN D.SEGMENTID = S.SEGMENTID AND D.DISTRICTID = J.DISTRICTID
     THEN S.NAME ELSE NULL
                 END AS SEGMENT,
J.STARTTIME,
J.ENDTIME,
SJ.COMCATJOBTYPEID,
J.JOBTYPEID,
CASE WHEN J.JOBTYPEID = JT.JOBTYPEID
     THEN JT.NAME ELSE NULL
                 END AS JOBTYPENAME,
TS.SSSEGMENT AS SUBPL_NAME,
CONCAT(D.DISTRICTID,TS.SSSEGMENT) AS DISTRICTSUBPLID
FROM `EAR-AA-242`.JOB J
LEFT JOIN `EAR-AA-242`.DISTRICT D ON J.DISTRICTID = D.DISTRICTID
LEFT JOIN `EAR-AA-242`.DMJOB DJ ON DJ.JOBNUMBER = J.JOBID
LEFT JOIN `EAR-AA-242`.JOBTYPE JT ON JT.JOBTYPEID = J.JOBTYPEID
LEFT JOIN `EAR-AA-242`.STANDARDJOBTYPE SJ ON JT.STANDARDJOBTYPEID = SJ.STANDARDJOBTYPEID
LEFT JOIN `EAR-AA-239`.TBLJOBTYPE TJ ON SJ.COMCATJOBTYPEID = TJ.JOBTYPEID
LEFT JOIN (SELECT SS.SSSEGMENT, SS.NODEID FROM `EAR-AA-239`.TBLSSSEGMENT S,
                    `EAR-AA-239`.TBLSSSEGMENT SS WHERE S.SSS = SS.PARENTSSS AND SS.DELETED = 0) TS ON TJ.SSSEGMENT =TS.NODEID
LEFT JOIN `EAR-AA-242`.SEGMENT S ON S.SEGMENTID = D.SEGMENTID
WHERE DATE(J.STARTTIME) > DATE_SUB(DATE(SYSDATE()), INTERVAL 367 DAY)
ORDER BY J.JOBID;

创建表时，相同的代码会抛出一个
错误代码：1292。截断了错误的双值：'11COC0011'

CREATE TABLE ODS.JOB_LOCATION_IDISTRICT AS

SELECT
CASE WHEN J.JOBID = DJ.JOBNUMBER
     THEN DJ.JOBID ELSE J.JOBID
                 END AS JOBID,
J.DISTRICTID,
CASE WHEN D.DISTRICTID = J.DISTRICTID
     THEN D.NAME ELSE NULL
                 END AS DISTRICT_NAME,
D.SEGMENTID,
CASE WHEN D.SEGMENTID = S.SEGMENTID AND D.DISTRICTID = J.DISTRICTID
     THEN S.NAME ELSE NULL
                 END AS SEGMENT,
J.STARTTIME,
J.ENDTIME,
SJ.COMCATJOBTYPEID,
J.JOBTYPEID,
CASE WHEN J.JOBTYPEID = JT.JOBTYPEID
     THEN JT.NAME ELSE NULL
                 END AS JOBTYPENAME,
TS.SSSEGMENT AS SUBPL_NAME,
CONCAT(D.DISTRICTID,TS.SSSEGMENT) AS DISTRICTSUBPLID
FROM `EAR-AA-242`.JOB J
LEFT JOIN `EAR-AA-242`.DISTRICT D ON J.DISTRICTID = D.DISTRICTID
LEFT JOIN `EAR-AA-242`.DMJOB DJ ON DJ.JOBNUMBER = J.JOBID
LEFT JOIN `EAR-AA-242`.JOBTYPE JT ON JT.JOBTYPEID = J.JOBTYPEID
LEFT JOIN `EAR-AA-242`.STANDARDJOBTYPE SJ ON JT.STANDARDJOBTYPEID = SJ.STANDARDJOBTYPEID
LEFT JOIN `EAR-AA-239`.TBLJOBTYPE TJ ON SJ.COMCATJOBTYPEID = TJ.JOBTYPEID
LEFT JOIN (SELECT SS.SSSEGMENT, SS.NODEID FROM `EAR-AA-239`.TBLSSSEGMENT S,
                    `EAR-AA-239`.TBLSSSEGMENT SS WHERE S.SSS = SS.PARENTSSS AND SS.DELETED = 0) TS ON TJ.SSSEGMENT =TS.NODEID
LEFT JOIN `EAR-AA-242`.SEGMENT S ON S.SEGMENTID = D.SEGMENTID
WHERE DATE(J.STARTTIME) > DATE_SUB(DATE(SYSDATE()), INTERVAL 367 DAY)
ORDER BY J.JOBID;

任何帮助绝对赞赏！

最佳答案

您应该显式指定列的数据类型。没有这些，MySQL必须弄清楚类型是什么。当您使用CASE表达式返回列的值时，它假定列数据类型将与第一个THEN值的类型相同，但是如果其他情况返回不同的值，则将是错误的类型。在您的代码中，您有一些CASE表达式，其中第一个结果是DOUBLE，但是另一个结果是无法转换为CHAR的DOUBLE字符串。

所以应该

CREATE TABLE ODS.JOB_LOCATION_IDISTRICT (
    JOBID VARCHAR(30),
    DISTRICTID INT,
    ...
) AS
SELECT
...