mysql - sql 语法错误以防万一

Question

我有一个格式如下的表格。

EMP NUMBER, ATT DATE,   ATT TIME
00001   01-03-2018  08:45
00001   01-03-2018  20:50
00001   02-03-2018  08:00
00001   02-03-2018  18:50

我被要求得到以下结果：

EMP NUMBER,  ATT DATE,    IN TIME,   OUT TIME
00001      01-03-2018   08:45     20:50
00001      02-03-2018   08:00     18:50

我设法使用以下查询获得：

SELECT EMP_NUMBER, ATT_DATE,MIN(ATT_TIME) AS IN_TIME,MAX(ATT_TIME) AS OUT_TIME 
        INTO TABLE1
FROM FJ 
GROUP BY EMP_NUMBER,ATT_DATE

即使我得到了我想要的答案，但我担心员工在晚上 10 点登录并在第二天早上 5 点离开时的情况，因此我尝试了以下操作：

SELECT EMP_NUMBER, ATT_DATE,
        CASE
            WHEN ATT_TIME >= 12
            THEN MIN(ATT_TIME)
            ELSE MAX(ATT_TIME)
            AS IN_TIME,
        CASE
            WHEN ATT_TIME < 12
                THEN MIN(ATT_TIME)
                ELSE MAX(ATT_TIME)
         AS OUT_TIME,
         INTO TABLE1
    FROM FJ 
    GROUP BY EMP_NUMBER,ATT_DATE

这会在“AS”附近引发语法错误，你能帮我弄清楚我哪里出错了吗？提前致谢。

标签： mysqlsqlsql-server

Answer 1

不确定查询逻辑，但您的两个 CASE 语句都缺少“END”子句。

SELECT EMP_NUMBER, ATT_DATE,
        CASE
            WHEN ATT_TIME >= 12
            THEN MIN(ATT_TIME)
            ELSE MAX(ATT_TIME)
         END               --<-- You are missing "END" here 
            AS IN_TIME,
        CASE
            WHEN ATT_TIME < 12
                THEN MIN(ATT_TIME)
                ELSE MAX(ATT_TIME)
         END                    --<-- and here 
         AS OUT_TIME  --<-- also you had an extra comma here
         INTO TABLE1
    FROM FJ 
    GROUP BY EMP_NUMBER,ATT_DATE