时间戳

首页 > 解决方案 > 将不同的列分组为一行

mysql - 将不同的列分组为一行

问题描述

如何在不同的行和列中制作一行

SELECT  
    internal_id, store_id, user_id, shift_info,
    DATE(log_datetime) dates,   
    (case when log_action = '0' then TIME(log_datetime) end) as time_in,
    (case when log_action = '0' then CONCAT(log_lat, ",", log_lng) end) as loc_in,  
    (case when log_action = '1' then TIME(log_datetime) end) as time_out,   
    (case when log_action = '1' then CONCAT(log_lat, ",", log_lng) end) as loc_out 
FROM
    attendance_store_user   
WHERE 
    user_id = "A4CBD64F-D21C-5612-CCF5-497892B62E76"

在此处输入图像描述

我想要这样的结果：

在此处输入图像描述

标签： mysqlgroup-bygrouping

解决方案

您可以尝试对空 time_out 和 time_in 使用同一个表过滤器的连接 

select  a.dates, a.store_id,  a,time_in, b.time_out 
FROM attendance_store_user a 
INNER JOIN attendance_store_user b on a.dates = b.dates 
  and a.user_id = b.user_id 
    and a.time_out is null 
      and b.time_in is null
WHERE a.user_id = "A4CBD64F-D21C-5612-CCF5-497892B62E76"

推荐阅读