postgresql - 选择 1 行到 2 列查询 postgresql

Question

我有这样的 table

user_id |          date |        time|
"1"     |   "2017-01-03"|   "06:59:35"
"1"     |   "2017-01-03"|   "07:01:17"
"1"     |   "2017-01-03"|   "12:03:21"
"1"     |   "2017-01-03"|   "16:06:14"
"2"     |   "2017-01-03"|   "07:10:52"
"2"     |   "2017-01-03"|   "07:11:38"
"2"     |   "2017-01-03"|   "07:12:04"
"3"     |   "2017-01-03"|   "07:12:06"
"3"     |   "2017-01-03"|   "09:12:33"
"3"     |   "2017-01-03"|   "16:13:29"

这是我的mysql查询

SELECT  
    col_user_id as user_id, 
    col_date as tanggal, 
    if(MAKETIME(HOUR(MIN(col_jam),MINUTE(MIN(col_jam),00) <= '12:00', MAKETIME(HOUR(MIN(col_jam),MINUTE(MIN(col_jam),00), NULL) as jam_masuk, 
    if(MAKETIME(HOUR(MAX(col_jam),MINUTE(MAX(col_jam),00) > '12:00', MAKETIME(HOUR(MAX(col_jam),MINUTE(MAX(col_jam),00), NULL) as jam_keluar 
    FROM tb_kehadiran_temp
    WHERE 
    col_date >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH
    AND 
    col_date < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
    GROUP BY col_user_id,col_date

我希望在 postgre 查询中得到这样的结果:

user_id | date       | in       | out 
1       | 2017-01-03 | 06:59:35 | 16:06:14 
2       | 2017-01-03 | 07:10:52 | null 
3       | 2017-01-03 | 07:12:04 | 16:13:29

如果数据存在，我想移动到其他有条件插入或更新的表

Answer 1

我认为一个简单的 GROUP BY 查询应该在这里工作，假设你的时间列是一个实际的时间类型，或者如果它是文本，它是固定宽度的:

SELECT
    user_id,
    date,
    MIN(time) AS "in",   -- DON'T use in to name a column; it's a keyword
    CASE WHEN MAX(time) < '12:00:00' THEN NULL ELSE MAX(time) END AS out
FROM tb_kehadiran_temp
GROUP BY
    user_id,
    date;

编辑:

如果您需要根据上述查询的结果填充另一个表，您可以尝试使用 INSERT INTO ... SELECT 语法，例如

INSERT INTO otherTable (user_id, date, min_time, max_time)
SELECT
    user_id,
    date,
    MIN(time) AS "in",
    CASE WHEN MAX(time) < '12:00:00' THEN NULL ELSE MAX(time) END AS out
FROM tb_kehadiran_temp
GROUP BY
    user_id,
    date;