Mysql查询合并两个条件成一行
问题描述:
我只是感到困惑。已经尝试搜索整个网站或谷歌,但没有找到'最近'的解决方案。Mysql查询合并两个条件成一行
好吧,让我们说我有这个表结构。
id date finger_id finger_time is_enter
1 2017-03-30 2 09:00 1
2 2017-03-30 2 17:13 0
3 2017-03-31 4 09:10 1
4 2017-03-31 3 09:01 1
5. 2017-03-31 3 17:00 0
我想使表格如下所示。
date finger_id enter_time exit_time
2017-03-30 2 09:00 17:13
2017-03-30 4 09:10
2017-03-31 3 09:10 17:00
我已经做了sql语句,但它变成这样。
date finger_id enter_time exit_time
2017-03-30 2 09:00
2017-03-30 2 17:13
2017-03-31 4 09:10
2017-03-31 3 09:01
2017-03-31 3 17:00
我只想知道如何在finger_id列的同一日期合并is_enter 1和is_enter 0。
这是我的sql查询引用。
SELECT *
FROM `tbl_fingerprint`
LEFT JOIN `tbl_employee` ON `tbl_employee`.`fingerprint_id`=`tbl_fingerprint`.`fingerprint_id`
LEFT JOIN `tbl_position` ON `tbl_position`.`position_id`=`tbl_employee`.`position_id`
WHERE `fingerprint_date` >= '2017-03-01'
AND `fingerprint_date` <= '2017-04-01'
GROUP BY `tbl_fingerprint`.`fingerprint_id`,
`tbl_fingerprint`.`fingerprint_date`,
`tbl_fingerprint`.`is_enter`
ORDER BY `fingerprint_date` ASC LIMIT 30
感谢您的帮助球员。
答
你可以做一个group by
date
和finger_id
字段和一个聚集函数中使用条件表达式(case
或if()
),以获得预期的结果。聚合函数中的条件语句只有在is_enter
字段中设置了正确的值时才确保它们返回值。我离开了员工的细节,因为那些没有形成你的问题的一部分:
SELECT date, fingerprint_id, max(if(is_enter=1,finger_time,null) as enter_time, max(if(is_enter=0,finger_time,null) as exit_time
FROM `tbl_fingerprint`
WHERE `fingerprint_date` >= '2017-03-01'
AND `fingerprint_date` <= '2017-04-01'
GROUP BY `tbl_fingerprint`.`fingerprint_id`,
`tbl_fingerprint`.`fingerprint_date`,
ORDER BY `fingerprint_date` ASC LIMIT 30
答
SELECT * FROM `tbl_fingerprint`
LEFT JOIN `tbl_employee` ON `tbl_employee`.`fingerprint_id`=`tbl_fingerprint`.`fingerprint_id`
LEFT JOIN `tbl_position` ON `tbl_position`.`position_id`=`tbl_employee`.`position_id`
LEFT JOIN (SELECT * FROM tbl_fingerprint WHERE is_enter = 0) a
ON a.finger_id = tbl_fingerprint.finger_id AND a.date = tbl_fingerprint.date
WHERE `fingerprint_date` >= '2017-03-01' AND `fingerprint_date` <= '2017-04-01' AND tbl_fingerprint.is_enter = 1
GROUP BY `tbl_fingerprint`.`fingerprint_id`, `tbl_fingerprint`.`fingerprint_date`, `tbl_fingerprint`.`is_enter`
ORDER BY `fingerprint_date` ASC LIMIT 30
答
试试这个(这将工作,如果finger_time
是time
类型): -
SELECT date, finger_id, min(finger_time) enter_time, if (min(finger_time) = max(finger_time), null, max(finger_time)) exit_time FROM xyz group by finger_id, date
答
SELECT a1.*, a3.time as time_out FROM attendance as a1
INNER JOIN (SELECT MIN(id) as id FROM attendance where is_enter = '1' group by date, f_id) as a2
ON a2.id = a1.id
LEFT JOIN attendance as a3 ON a3.date = a1.date AND a1.f_id = a3.f_id and a3.is_enter = '0'
你可能需要投射日期以不包含时间部分或用yyyy-mm-dd格式字符
男人,这太棒了!我刚刚尝试使用max,但我被卡住,直到我看到你的工作很棒!非常感谢。编辑:我只是将最大值改为最小值,因为我会得到低的结果,这太棒了! – DennisFrea
已接受,感谢您的帮助,但抱歉,由于我没有最低限度的信誉,我无法对您的答案满意 – DennisFrea