mysql - 在 MariaDB 10.2.15 中使用 CASE WHEN DATE 语句

Question

我创建了一个查询来显示一个月内每个日期的 ScanIn 字段

代码:

SELECT
    Employee.Name,
    CASE WHEN DAY(LogsFormatted.DateIn) = 1 THEN LogsFormatted.ScanIn ELSE '-' END AS '1',
    CASE WHEN DAY(LogsFormatted.DateIn) = 2 THEN LogsFormatted.ScanIn ELSE '-' END AS '2',
    CASE WHEN DAY(LogsFormatted.DateIn) = 3 THEN LogsFormatted.ScanIn ELSE '-' END AS '3',
    CASE WHEN DAY(LogsFormatted.DateIn) = 4 THEN LogsFormatted.ScanIn ELSE '-' END AS '4',
    CASE WHEN DAY(LogsFormatted.DateIn) = 5 THEN LogsFormatted.ScanIn ELSE '-' END AS '5',
    CASE WHEN DAY(LogsFormatted.DateIn) = 6 THEN LogsFormatted.ScanIn ELSE '-' END AS '6',
    CASE WHEN DAY(LogsFormatted.DateIn) = 7 THEN LogsFormatted.ScanIn ELSE '-' END AS '7',
    CASE WHEN DAY(LogsFormatted.DateIn) = 8 THEN LogsFormatted.ScanIn ELSE '-' END AS '8',
    CASE WHEN DAY(LogsFormatted.DateIn) = 9 THEN LogsFormatted.ScanIn ELSE '-' END AS '9',
    CASE WHEN DAY(LogsFormatted.DateIn) = 10 THEN LogsFormatted.ScanIn ELSE '-' END AS '10'
FROM
    HrEmployee AS Employee
    LEFT JOIN HrAttLogsFormatted AS LogsFormatted ON Employee.FingerId = LogsFormatted.FingerId
WHERE
    LogsFormatted.DateIn BETWEEN '2019-03-01' AND '2019-03-31'
    AND Employee.Id = 14522228

在数据库中

| DateIn     | ScanIn   |
| 2019-03-04 | 06:58:09 |
| 2019-03-05 | 07:34:09 |
| 2019-03-06 | 07:12:05 |
| 2019-03-08 | 06:56:51 |

数据库结果Click Here

我想要显示查询结果

Name    1   2   3   4         5        6         7  8         9  10
Aldan   -   -   -   06:58:09  07:34:09 07:12:05  -  06:56:51  -  -

但是我查询不成功，有办法解决吗？

提前谢谢

Answer 1

我认为你想要条件聚合:

SELECT e.Name,
       MAX(CASE WHEN DAY(lf.DateIn) = 1 THEN lf.ScanIn END) AS '1',
       MAX(CASE WHEN DAY(lf.DateIn) = 2 THEN lf.ScanIn END) AS '2',
       MAX(CASE WHEN DAY(lf.DateIn) = 3 THEN lf.ScanIn END) AS '3',
       MAX(CASE WHEN DAY(lf.DateIn) = 4 THEN lf.ScanIn END) AS '4',
       MAX(CASE WHEN DAY(lf.DateIn) = 5 THEN lf.ScanIn END) AS '5',
       MAX(CASE WHEN DAY(lf.DateIn) = 6 THEN lf.ScanIn END) AS '6',
       MAX(CASE WHEN DAY(lf.DateIn) = 7 THEN lf.ScanIn END) AS '7',
       MAX(CASE WHEN DAY(lf.DateIn) = 8 THEN lf.ScanIn END) AS '8',
       MAX(CASE WHEN DAY(lf.DateIn) = 9 THEN lf.ScanIn END) AS '9',
       MAX(CASE WHEN DAY(lf.DateIn) = 10 THEN lf.ScanIn END AS '10'
FROM HrEmployee e LEFT JOIN
     HrAttLogsFormatted lf
     ON e.FingerId = lf.FingerId
WHERE lf.DateIn BETWEEN '2019-03-01' AND '2019-03-31' AND
      e.Id = 14522228
GROUP BY e.name;

我不建议使用破折号来表示丢失的数据。只需使用NULL——SQL 使用的内容。