时间戳

首页 > 解决方案 > SQL select inside select 使用日期时间列作为参考

mysql - SQL select inside select 使用日期时间列作为参考

问题描述

我有一张如下所示的表格：

╔═══════════╦═════════╦═════════════╦═══════════════╦═════════════════════╦═════════════════════╗
║ id_object ║ Name    ║ Activity    ║ Object_Prefix ║ Start_DateTime      ║ End_DateTime        ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Junior  ║ Fixing      ║ G35           ║ 06-11-2020 09:35:06 ║ 06-11-2020 16:39:34 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Junior  ║ End_turn    ║               ║ 06-11-2020 17:01:25 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Richard ║ Auxiliating ║ G35           ║ 06-11-2020 09:35:06 ║ 06-11-2020 16:39:34 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 213       ║ Richard ║ End_turn    ║               ║ 06-11-2020 17:04:07 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 456       ║ Philip  ║ Fixing      ║ G08           ║ 02-11-2020 13:35:09 ║ 02:11:2020 21:58:59 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 456       ║ Philip  ║ End_turn    ║               ║ 02-11-2020 22:37:51 ║                     ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 102       ║ Julia   ║ Auxiliating ║ N901          ║ 31-10-2020 20:01:30 ║ 01-11-2020 08:09:41 ║
╠═══════════╬═════════╬═════════════╬═══════════════╬═════════════════════╬═════════════════════╣
║ 102       ║ Julia   ║ End_turn    ║               ║ 01-11-2020 08:13:45 ║                     ║
╚═══════════╩═════════╩═════════════╩═══════════════╩═════════════════════╩═════════════════════╝

我一直在尝试选择 End turn Times 作为新专栏，尽管到目前为止我尝试但没有成功的是：

 select  
  t1.id,
  t1.name,
  t1.activity,
  DATE_FORMAT( t1.Start_DateTime, "%d/%m-%Y %H:%i:%s") as Start,
  (Select DATE_FORMAT(t1.Start_DateTime, "%d/%m-%Y %H:%i:%s")
    from Activities t2 
     WHERE t2.Activities = "End_turn" AND t2.id_object = t1.id_object) as end_activity
  FROM 
   Activities t1 
 WHERE 
 t1.Object_Prefix != ""
GROUP BY
t.Start_DateTime

我期待的结果是这样的： 预期结果

但是 end_turn 列总是充满了空值。我没有弄清楚我错过它的地方。

标签： mysqlsqldatetimesubquerywhere-clause

解决方案

  1. 您的示例数据和查询之间存在不一致。
select  
  t1.id, <--- there is no column named as `id`. Instead it was `id_object`
  t1.name,
  t1.activity,
  DATE_FORMAT( t1.Start_DateTime, "%d/%m-%Y %H:%i:%s") as Start,
  (Select DATE_FORMAT(t1.Start_DateTime, "%d/%m-%Y %H:%i:%s")
    from Activities t2 
     WHERE t2.Activities = "End_turn" <---- there is no column named as `activities`. Instead it was `activity`.
   AND t2.id_object = t1.id_object) as end_activity
  FROM 
   Activities t1 
 WHERE 
 t1.Object_Prefix != ""
GROUP BY
t1.Start_DateTime
  1. 我已经使用您提供的示例数据尝试了您的查询，我得到了：

错误代码：1242 子查询返回多于 1 行

  1. 列上的GROUP BYist1.Start_DateTime没有意义，因为它似乎清楚地显示在您的预期结果中，您希望他们按id_productand对它们进行分组name。

  2. Start_DateTime和End_DateTime列中的日期格式是dd-mm-yyyy，而日期相关数据类型的标准 MySQL 日期是yyyy-mm-dd.

最后，如果 Start_DateTime 和 End_DateTime 列的数据类型是日期/日期时间，我建议使用以下查询：

SELECT id_object, `Name`, 
       SUBSTRING_INDEX(GROUP_CONCAT(Activity ORDER BY Start_DateTime ASC),',',1) ac, 
       MAX(CASE WHEN Object_Prefix <> '' THEN Object_Prefix END) op,
       MIN(CASE WHEN activity <> 'End_turn' THEN Start_DateTime END) sdt,
       MAX(CASE WHEN activity = 'End_turn' THEN Start_DateTime END) edt
FROM activities
GROUP BY id_object, `Name`;

你可以在这里看到演示：https ://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=de2af89c39e6636cfb45ee179cfb1461

推荐阅读