将JOIN与DISTINCT一起使用并确定一个表的优先级(Using JOIN with DISTINCT and prioritize one table)

 我试图结合2个表的数据。 
 这两个表都包含来自同一传感器的数据（比方说，传感器测量二氧化碳，每10分钟输入一次）。 
 
 第一个表包含经过验证的数据。 我们称之为station1_validated 。 第二个表包含原始数据。 我们称之为station1_nrt 。 
 
 原始数据表包含实时数据，而经验证的表仅包含至少1个月的数据点。 （它需要一些时间来验证这些数据并在之后手动控制它，这种情况每个月只发生一次）。 
 
 我现在要做的是将这两个表的数据结合起来在网站上显示实时数据。 但是，当验证数据可用时，它应该优先考虑原始数据点上的数据点。 
 
 相关的列是： 
 
 
 
 timed [bigint（20）]：包含日期时间作为unix时间戳，以毫秒为单位，从1.1.1970开始 
 
 
 CO2 [双倍]：包含测量的CO2浓度（ppm）（百万分率） 
 
 
 我写了这个基本的SQL： 
 
SELECT 
    *
FROM
    (SELECT 
        timed, CO2, '2' tab
    FROM
        station1_nrt
    WHERE
        TIMED >= 1386932400000
            AND TIMED <= 1386939600000
            AND TIMED NOT IN (SELECT 
                timed
            FROM
                station1_nrt
            WHERE
                CO2 IS NOT NULL
                    AND TIMED >= 1386932400000
                    AND TIMED <= 1386939600000) UNION SELECT 
        timed, CO2, '1' tab
    FROM
        station1_validated
    WHERE
        CO2 IS NOT NULL
            AND TIMED >= 1386932400000
            AND TIMED <= 1386939600000) a
ORDER BY timed
 
 这不能正常工作，因为它只选择两个表都有条目的数据点。 但是我现在想用JOIN来做这件事，因为它会更快。 但是我不知道如何使用DISTINCT（或类似的东西）来加入表格的优先级。 有人可以帮我解决这个问题（或解释一下吗？） 

I am trying to combine data from 2 tables.
 Those 2 tables both contain data from the same sensor (lets say a sensor that measures CO2 with 1 entry per 10 minutes).
 
The first table contains validated data. Let's call it station1_validated. The 2nd table contains raw data. Let's call this one station1_nrt.
 
While the raw-data table contains live data, the validated table contains only data points that are at least 1 month old. (It needs some time to validate those data and to control it manually afterwards, this happens only once every month).
 
What I am trying to do now is to combine the data of those 2 tables to display live data on a website. However when validated data is available it should prioritize that data point over the raw data-point.
 
The relevant columns for this are:
 
 
 
timed [bigint(20)]: Contains the datetime as a unix timestamp in milliseconds from 1.1.1970
 
 
CO2 [double]: Contains the measured concentration of CO2 in ppm (parts per million)
 
 
I wrote this basic SQL:
 
SELECT 
    *
FROM
    (SELECT 
        timed, CO2, '2' tab
    FROM
        station1_nrt
    WHERE
        TIMED >= 1386932400000
            AND TIMED <= 1386939600000
            AND TIMED NOT IN (SELECT 
                timed
            FROM
                station1_nrt
            WHERE
                CO2 IS NOT NULL
                    AND TIMED >= 1386932400000
                    AND TIMED <= 1386939600000) UNION SELECT 
        timed, CO2, '1' tab
    FROM
        station1_validated
    WHERE
        CO2 IS NOT NULL
            AND TIMED >= 1386932400000
            AND TIMED <= 1386939600000) a
ORDER BY timed
 
This does not work correctly as it selects only those data points where both tables have an entry. However I want to do this with a JOIN now as it would be much faster. However I don't know how to a JOIN with a DISTINCT (or something similar) with prioritizing a table. Could someone help me out with this (or explain it?)

原文：https://stackoverflow.com/questions/20659686