mysql - 复杂的sql分组排序

Question

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

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

with this 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                       Logout2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00,09:35:13,09:39:19  09:35:25,09:39:29,10:48:042012-09-13  SALITA, LYNYRD ANTONIO  08:05:00                    17:00:002012-09-12  SALITA, LYNYRD ANTONIO  08:05:00                    17:05:002012-07-10  REYES, ANDREA           07:10:00                    17:00:002012-07-10  NAVARRO, KRISTINA       07:10:00                    17:00:002012-07-10  TARINE, KAREN           07:25:00                    17:00:002012-07-10  GARCIA, ALVIN           07:30:00                    17:00:002012-07-10  AURENO, LEAH            07:40:00                    17:00:002012-07-10  CANSINO, PAUL           08:00:00                    17:00:002012-07-10  SALITA, LYNYRD ANTONIO  08:00:00                    17:00:002012-07-10  BELO, RIO               08:00:00                    17:00:002012-07-10  MAG-ISA, MAYBELLE       10:30:00                    17:00:00

Based from one of the answers this is the code:

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       Logout2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00    09:35:252012-09-14  SALITA, LYNYRD ANTONIO  09:35:13    09:35:252012-09-14  SALITA, LYNYRD ANTONIO  09:39:19    09:39:292012-09-13  SALITA, LYNYRD ANTONIO  08:05:00    17:00:002012-09-12  SALITA, LYNYRD ANTONIO  08:05:00    17:05:002012-07-10  REYES, ANDREA           07:10:00    17:00:002012-07-10  NAVARRO, KRISTINA       07:10:00    17:00:002012-07-10  TARINE, KAREN           07:25:00    17:00:002012-07-10  GARCIA, ALVIN           07:30:00    17:00:002012-07-10  AURENO, LEAH            07:40:00    17:00:002012-07-10  CANSINO, PAUL           08:00:00    17:00:002012-07-10  SALITA, LYNYRD ANTONIO  08:00:00    17:00:002012-07-10  BELO, RIO               08:00:00    17:00:002012-07-10  MAG-ISA, MAYBELLE       10:30:00    17:00:00

有没有办法让结果像这样排序？

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

Answer 1

像这样:

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 真的是必要的，因为你实际上并没有对任何东西进行分组。