文章/答案/技术大牛

发布

社区首页 >问答首页 >在MYSQL中建表时的数据转换

问在MYSQL中建表时的数据转换
EN

Stack Overflow用户

提问于 2019-08-27 10:51:11

回答 1查看 33关注 0票数 0

创建表时出错，但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

mysql-workbench

error-code

回答 1

Stack Overflow用户

发布于 2019-08-27 10:58:35

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

所以它应该是

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

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/57667114

复制

相似问题

问在MYSQL中建表时的数据转换
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MYSQL中建表时的数据转换EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MYSQL中建表时的数据转换
EN