截断Bigquery中值的前5个值

尝试将两列强制转换为int:

SELECT *
FROM
(
    SELECT
        SUBSTR(CAST(CAST(Serial AS INT) AS STRING), 1, 5) AS Serial,
        Latest_Use,
        Total_Hours,
        Devices_Connected
    FROM [dataworks-356fa:FirebaseArchive.PT_Results]
) AS Model_Results
JOIN
(
    SELECT
       SUBSTR(CAST(CAST(Serial_Number AS INT) AS STRING), 1, 5) AS Serial_Number,
       Status,
       Model_Number
    FROM [dataworks-356fa:FirebaseArchive.Master_List_PT]
) AS Master_List
    ON Model_Results.Serial = Master_List.Serial_Number

Serial 从比较的角度来看,to integer应该去掉前导零。注意,我使用了一个派生表来进行转换,因此 ON 子句只涉及纯列,不涉及函数。

如果要比较 之后 前导零已经去掉,你可以尝试双重施放:

SUBSTR(CAST(CAST(Serial AS INT) AS STRING), 1, 5)