mysql - SQL 连接添加缺失的时间和标识符数据

Question

我有两个表:表 a 包含每分钟每个 c_id 的值。 (如果 c_id 和分钟数据丢失 - 行丢失)。

表a:

---------------------------------
| c_id  | TIME_SEC    | C_VALUE |
---------------------------------
|    1  | 1431943200  |      10 |
---------------------------------
|    1  | 1431943260  |      11 |
---------------------------------
|    2  | 1431943200  |      12 |
---------------------------------

包含所有分钟的表时间:

------------
| TIME_SEC |
------------
|1431943140|
------------
|1431943200|
------------
|1431943260|
------------
|1431943320|
------------

我想获取 a 中的所有数据，包括给定 c_ids 之间的缺失数据。例如对于:c_id 位于 (1,2) 且 TIME_SEC 介于 1431943140 和 1431943320 之间

预期结果是:

---------------------------------
| c_id  | TIME_SEC    | C_VALUE |
---------------------------------
|    1  | 1431943140  |    null |
---------------------------------
|    1  | 1431943200  |      10 |
---------------------------------
|    1  | 1431943260  |      11 |
---------------------------------
|    1  | 1431943320  |    null |
---------------------------------
|    2  | 1431943140  |    null |
---------------------------------
|    2  | 1431943200  |      12 |
---------------------------------
|    2  | 1431943260  |    null |
---------------------------------
|    2  | 1431943320  |    null |
---------------------------------

向表 a 添加缺失的数据不是一个选项，因为它会添加大量数据。

我尝试使用这个 SQL:

select * 
from 
(
select * from 
a where c_id IN ( 1,2 ) AND TIME_SEC between 1431943140 and 1431943320
) m 
right join times t
on (m.TIME_SEC = t.TIME_SEC);

但它会在全局范围内返回每分钟缺失的数据 - 对于所有 c_id:

---------------------------------
| c_id  | TIME_SEC    | C_VALUE |
---------------------------------
| null  | 1431943140  |    null |
---------------------------------
|    1  | 1431943200  |      10 |
---------------------------------
|    1  | 1431943260  |      11 |
---------------------------------
|    2  | 1431943200  |      12 |
---------------------------------
| null  | 1431943320  |    null |
---------------------------------

有什么帮助吗？

Answer 1

创建所需 c_id 和时间的派生表，然后将其左连接到值:

select m.c_id, m.TIME_SEC, a.C_VALUE
from (
  SELECT DISTINCT a.c_id, t.TIME_SEC
  FROM a
  CROSS JOIN times t
  where a.c_id IN ( 1,2 ) AND t.TIME_SEC between 1431943140 and 1431943320
) m
LEFT OUTER JOIN a
  ON m.c_id=a.c_id
  AND m.TIME_SEC=a.TIME_SEC;