Question

有两个MySQL表:

table1

id |  time |  status
-----------------------
1  | 10:00 | conn     | 
1  | 10:01 | disconn  | 
2  | 10:02 | conn     | 
2  | 10:03 | disconn  | 
3  | 10:04 | conn     | 


table2

id |  time |
------------
3  | 10:05 |

If there is no disconn time value for ceratin id then take it from table2. What is sql query to get wished result:

id | conn | disconn|
--------------------
1  | 10:00| 10:01  |
2  | 10:02| 10:03  |
3  | 10:04| 10:05  |

Answer 1

您可使用<条码>LEFT JOIN和COALESCE。样本代码:

SELECT 
    t1.id,
    t1.time AS conn,
    COALESCE(t1_disconn.time, t2.time) AS disconn
FROM table1 t1
LEFT JOIN table1 t1_disconn ON t1.id = t1_disconn.id AND t1_disconn.status =  disconn 
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t1.status =  conn 
ORDER BY t1.id;

样本产出如下:

http://dbfiddle.uk/4vp-wO47“rel=“nofollow noreferer”>fiddlelink 。

Answer 2

或者, 也可使用<代码>。由<<0>无<>代码>的组别加入如下:

SELECT
    T1.ID,
    MAX(CASE WHEN T1.STATUS =  conn  THEN T1.TIME END) AS CONN,
    COALESCE(MAX(CASE WHEN T1.STATUS =  disconn  THEN T1.TIME END), T2.TIME) AS DISCONN
  FROM
    table1 T1
      LEFT JOIN table2 T2 ON T1.ID = T2.ID
 GROUP BY T1.ID, T2.TIME
 ORDER BY T1.ID;

友情链接