mysql-复杂的sql分组和排序

2023-07-29 08:43:34

我在将日常时间记录中的数据进行分组和排序时遇到问题
自动生成报告. DTR的表结构如下：

LogDate     LogTime     EmployeeName            LogType
2012-09-14  10:48:04    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:39:29    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:39:19    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-14  09:35:25    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:35:13    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-14  08:10:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-13  17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-13  08:05:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-12  17:05:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-12  08:05:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-07-10  17:00:00    MAG-ISA, MAYBELLE       LOGOUT
2012-07-10  17:00:00    BELO, RIO               LOGOUT
2012-07-10  17:00:00    CANSINO, PAUL           LOGOUT
2012-07-10  17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-07-10  17:00:00    AURENO, LEAH            LOGOUT
2012-07-10  17:00:00    GARCIA, ALVIN           LOGOUT
2012-07-10  17:00:00    TARINE, KAREN           LOGOUT
2012-07-10  17:00:00    REYES, ANDREA           LOGOUT
2012-07-10  17:00:00    NAVARRO, KRISTINA       LOGOUT
2012-07-10  10:30:00    MAG-ISA, MAYBELLE       LOGIN
2012-07-10  08:00:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-07-10  08:00:00    CANSINO, PAUL           LOGIN
2012-07-10  08:00:00    BELO, RIO               LOGIN
2012-07-10  07:40:00    AURENO, LEAH            LOGIN
2012-07-10  07:30:00    GARCIA, ALVIN           LOGIN
2012-07-10  07:25:00    TARINE, KAREN           LOGIN
2012-07-10  07:10:00    NAVARRO, KRISTINA       LOGIN
2012-07-10  07:10:00    REYES, ANDREA           LOGIN

与此SQL：

SELECT 
    DATE_FORMAT(LogDate, '%d/%c/%Y') AS LogDate, 
    EmployeeName,
    (GROUP_Concat(CASE LogType WHEN 'LOGIN' THEN LogTime END)) AS LOGIN,
    (GROUP_Concat(CASE LogType WHEN 'LOGOUT' THEN LogTime END)) AS LOGOUT
FROM myTable
GROUP BY LogDate, EmployeeName
ORDER BY LogDate desc;

我能够产生这个结果

LogDate     EmployeeName            Login                       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00,09:35:13,09:39:19  09:35:25,09:39:29,10:48:04
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00                    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00                    17:05:00
2012-07-10  REYES, ANDREA           07:10:00                    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00                    17:00:00
2012-07-10  TARINE, KAREN           07:25:00                    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00                    17:00:00
2012-07-10  AURENO, LEAH            07:40:00                    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00                    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00                    17:00:00
2012-07-10  BELO, RIO               08:00:00                    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00                    17:00:00

根据答案之一,这是代码：

SELECT DATE_FORMAT(t1.LogDate, '%d/%c/%Y') AS LogDate, t1.EmployeeName
     , t1.LogTime AS Login
     , ( SELECT MIN(t2.LogTime) FROM myTable t2
          WHERE t2.LogType = 'LOGOUT'
            AND t2.LogDate = t1.LogDate
            AND t2.EmployeeName = t1.EmployeeName
            AND t2.LogTime > t1.LogTime ) AS Logout
  FROM myTable t1
 WHERE t1.LogType = 'LOGIN'

结果如下：

LogDate     EmployeeName            Login       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:35:13    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:39:19    09:39:29
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00    17:05:00
2012-07-10  REYES, ANDREA           07:10:00    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00    17:00:00
2012-07-10  TARINE, KAREN           07:25:00    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00    17:00:00
2012-07-10  AURENO, LEAH            07:40:00    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00    17:00:00
2012-07-10  BELO, RIO               08:00:00    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00    17:00:00

有什么办法可以使结果像这样排序吗？

LogDate     EmployeeName            Login       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00    NULL
2012-09-14  SALITA, LYNYRD ANTONIO  09:35:13    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:39:19    09:39:29
2012-09-14  SALITA, LYNYRD ANTONIO  NULL        10:48:04
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00    17:05:00
2012-07-10  REYES, ANDREA           07:10:00    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00    17:00:00
2012-07-10  TARINE, KAREN           07:25:00    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00    17:00:00
2012-07-10  AURENO, LEAH            07:40:00    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00    17:00:00
2012-07-10  BELO, RIO               08:00:00    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00    17:00:00

解决方法:

像这样：

SELECT DATE_FORMAT(t1.LogDate, '%d/%c/%Y') AS LogDate, t1.EmployeeName
     , t1.LogTime AS Login
     , ( SELECT MIN(t2.LogTime) FROM myTable t2
          WHERE t2.LogType = 'LOGOUT'
            AND t2.LogDate = t1.LogDate
            AND t2.EmployeeName = t1.EmployeeName
            AND t2.LogTime > t1.LogTime ) AS Logout
  FROM myTable t1
 WHERE t1.LogType = 'LOGIN'

确实没有必要使用GROUP BY,因为您实际上并未对任何内容进行分组.

码农公寓

相关文章