mysql - 查询 1 行 2 条记录

Question

我有两个如下表:

表“ Hook ”:

|  id  |  name  |  kode  |
--------------------------
|  P01 |  david | 101    |

表“缺席”:

|  kode |  time               |  ket |
----------------------------------------
|  101 |  2018-01-25 07:00:00 | IN   |
|  101 |  2018-01-25 16:00:00 | OUT  |

请帮助我如何获得如下查询结果:

|  id  |  name  | kode |      time_1          | ket| time_2             | ket |
-------------------------------------------------------------------------------
|  P01 |  david | 101  |  2018-01-25 07:00:00 | IN |2018-01-25 16:00:00 |OUT  |

Answer 1

这是一个例子 sqlfiddle .您可以进行 2 次左连接(为不存在的表设置别名)，以获得结果:

SELECT peg.*, ab1.time as time_1, ab1.ket as ket, ab2.time as time_2, 
ab2.ket as ket2  FROM peg
LEFT JOIN absen ab1 ON peg.kode = ab1.kode AND ab1.ket = 'IN'
LEFT JOIN absen ab2 ON peg.kode = ab2.kode AND ab2.ket = 'OUT'