mysql - 使用什么样的查询来获得这个(左连接不起作用)

Question

table : transmission
--------------------------------------------------------
   ID       ReqString          Timestamp          Actif
-------  -------------  ---------------------   --------
   a         O21         2016-05-02 10:03:27       1
   a         O20         2016-05-01 11:07:47       1
   a         O11         2016-05-02 09:27:53       1

   b         O20         2016-05-02 12:27:45       1
   b         O21         2016-05-01 09:32:55       1

我需要为相同的 id 检索 ReqString LIKE O2% AND LIKE O1% 的最新值

我已经尝试过这个左连接。当我在 t1 中有一个值时，此查询有效，但当我在表 t1 中没有值时，此查询不起作用...

SELECT t1.ReqString AS O1, t2.ReqString AS O2, t1.Timestamp AS T1, t2.Timestamp AS T2
FROM transmission t1
LEFT JOIN transmission t2 ON t2.ID = t1.ID 
                         AND t2.ReqString LIKE 'O2%' 
                         AND t2.Actif=1
WHERE t1.ID = 'b' 
  AND t1.ReqString LIKE 'O1%'
  AND t1.Actif = 1
ORDER BY t1.Timestamp DESC, t2.Timestamp DESC
LIMIT 1

因此，如果我运行 ID = 'a' 的查询，我需要获取

------------------------------------------------------------------------
   O1       O2               T1                         T2
-------  ---------  -----------------------   -------------------------
  O11       O21       2016-05-02 09:27:53        2016-05-02 10:03:27

如果我为 ID = 'b' 运行它，我想要的结果是

------------------------------------------------------------------------
   O1       O2               T1                         T2
-------  ---------  -----------------------   -------------------------
 NULL      O20              NULL                 2016-05-02 12:27:45

Answer 1

select t1.ReqString AS O1, t2.ReqString AS O2, t1.Timestamp AS T1, t2.Timestamp AS T2 from
(SELECT ReqString , Timestamp  
FROM transmission where ReqString LIKE 'O1%'  AND Actif=1 and ID = 'a'
limit 1 order by Timestamp DESC)t1,
(SELECT ReqString , Timestamp  
FROM transmission where ReqString LIKE 'O2%'  AND Actif=1 and ID = 'a'
limit 1 order by Timestamp DESC)t2