mysql - 日期时间过滤器 mySQL

Question

我需要合并 2 个查询。两个查询中的第二个用于根据上次更新的传输日期 (Datetime) 筛选行。我正在使用 mySQL 数据库，并尝试使用过滤器来返回正确的结果。

查询1:

SELECT DISTINCT 
    F.client_license_ID
    , EM.create_DTM
    , EM.event_ID
    , CEQ.consumer_ID
    , EM.event_mapping_ID em_ID
    , EM.export_value campaign_number
    , EM.export_value_2 sequence_number
    , EM.export_value_3 campaign_number_2
    , EM.export_value_4 sequence_number_2
    , EM.export_value_5 ffs_event_id
    , EM.export_value_6
    , EM.export_value_7
    , EM.export_value_8
    , EM.export_value_9
    , EM.export_value_10
    , F.footprint_ID
    , F.event_token_ID
FROM data_transfer.Mappings EM
JOIN data_transfer.Event_Queue CEQ ON CEQ.event_ID = EM.event_ID
JOIN efn.Footprints F ON CEQ.consumer_ID = F.consumer_ID
JOIN data_transfer.DT_Runs as DR ON DR.data_transfer_ID = EM.data_transfer_ID
LEFT JOIN efn_data_transfer.CRM_Records LCR ON LCR.consumer_ID = CEQ.consumer_ID
WHERE EM.data_transfer_ID = 24
AND EM.mode = 'production'
AND EM.active_flag = 1
AND F.sample_flag = 0
AND LCR.failureCode = 0
AND EM.create_DTM > ?

查询2:

SELECT CAST(DATE_SUB(start,INTERVAL 3 DAY) AS CHAR) last_transfer
, CAST(DATE_FORMAT(NOW(),"%Y%m%d") AS CHAR) today
, CAST(DATE_FORMAT(NOW(),"%H%i%s") AS CHAR) "Time"
, CAST(DATE_FORMAT(NOW(),"%m%d%Y") AS CHAR) "Date"
, NOW() timeNow
FROM data_transfer.DT_Runs DTR
WHERE DTR.data_transfer_ID = 24
AND DTR.result = 1
AND DTR.mode = 'production'
ORDER BY DTR.dt_run_ID DESC
LIMIT 1;

我尝试为上次传输日期添加一个 where 过滤器(AND EM.create_DTM >= DR.start() - INTERVAL 3 DAY)，但它无法按预期工作。目前我有一个 ETL 作业，它处理两个查询，将查询 2 中的“最后传输”变量输入到 ? where 过滤器的变量。感谢帮助

Answer 1

您可以将其作为一个查询来实现。

SELECT DISTINCT 
    F.client_license_ID
    , EM.create_DTM
    , EM.event_ID
    , CEQ.consumer_ID
    , EM.event_mapping_ID em_ID
    , EM.export_value campaign_number
    , EM.export_value_2 sequence_number
    , EM.export_value_3 campaign_number_2
    , EM.export_value_4 sequence_number_2
    , EM.export_value_5 ffs_event_id
    , EM.export_value_6
    , EM.export_value_7
    , EM.export_value_8
    , EM.export_value_9
    , EM.export_value_10
    , F.footprint_ID
    , F.event_token_ID
FROM data_transfer.Mappings EM
JOIN data_transfer.Event_Queue CEQ ON CEQ.event_ID = EM.event_ID
JOIN efn.Footprints F ON CEQ.consumer_ID = F.consumer_ID
JOIN data_transfer.DT_Runs as DR ON DR.data_transfer_ID = EM.data_transfer_ID
LEFT JOIN efn_data_transfer.CRM_Records LCR ON LCR.consumer_ID = CEQ.consumer_ID
WHERE EM.data_transfer_ID = 24
AND EM.mode = 'production'
AND EM.active_flag = 1
AND F.sample_flag = 0
AND LCR.failureCode = 0
AND EM.create_DTM > (SELECT last_transfer FROM (SELECT CAST(DATE_SUB(start,INTERVAL 3 DAY) AS CHAR) last_transfer
, CAST(DATE_FORMAT(NOW(),"%Y%m%d") AS CHAR) today
, CAST(DATE_FORMAT(NOW(),"%H%i%s") AS CHAR) "Time"
, CAST(DATE_FORMAT(NOW(),"%m%d%Y") AS CHAR) "Date"
, NOW() timeNow
FROM data_transfer.DT_Runs DTR
WHERE DTR.data_transfer_ID = 24
AND DTR.result = 1
AND DTR.mode = 'production'
ORDER BY DTR.dt_run_ID DESC
LIMIT 1) )