表HrAttLogs
:
| Id | MachineIp | FingerId | Date | Time | Status | Verified | DateCreated |
| ----- | ----------- | -------- | ---------- | -------- | ------ | -------- | ------------------- |
| 254 | 10.20.20.73 | 10 | 2020-04-21 | 08:18:03 | 0 | 1 | 2020-04-22 14:46:21 |
| 64 | 10.20.20.73 | 10 | 2020-04-22 | 07:57:08 | 0 | 1 | 2020-04-22 14:46:21 |
| 14470 | 10.20.20.73 | 10 | 2020-04-23 | 08:05:15 | 0 | 1 | 2020-04-23 08:39:23 |
| 14991 | 10.20.20.73 | 10 | 2020-04-23 | 15:27:06 | 1 | 1 | 2020-04-25 11:00:17 |
| 14972 | 10.20.20.73 | 10 | 2020-04-23 | 16:24:53 | 1 | 1 | 2020-04-25 11:00:16 |
| 14842 | 10.20.20.73 | 10 | 2020-04-24 | 08:46:21 | 0 | 1 | 2020-04-25 11:00:16 |
| 14764 | 10.20.20.73 | 10 | 2020-04-24 | 15:31:57 | 1 | 1 | 2020-04-25 11:00:15 |
| 16222 | 10.20.20.73 | 10 | 2020-04-27 | 17:29:13 | 1 | 1 | 2020-05-02 09:05:05 |
| 16103 | 10.20.20.73 | 10 | 2020-04-28 | 08:50:55 | 0 | 1 | 2020-05-02 09:05:04 |
| 16024 | 10.20.20.73 | 10 | 2020-04-28 | 16:42:14 | 1 | 1 | 2020-05-02 09:05:04 |
| 15894 | 10.20.20.73 | 10 | 2020-04-29 | 09:43:38 | 0 | 1 | 2020-05-02 09:05:03 |
我的查询中有一个案例,在HrAttLogs
表中的日期数据中,2020-04-27
只有 1 条记录Status = 1
。因为在下一个查询中,所选数据具有筛选器WHERE Status ! = 1
。如何SELECT
日期2020-04-27
上只有Status = 1
类似记录的 1 条记录?
结果查询 :
| FingerId | Status_Logs | ShiftId | Date | DateOut | Time |
| -------- | ----------- | ------- | ---------- | ---------- | -------- |
| 10 | 0 | 1 | 2020-04-21 | 2020-04-21 | 08:18:03 |
| 10 | 0 | 1 | 2020-04-22 | 2020-04-22 | 07:57:08 |
| 10 | 0 | 1 | 2020-04-23 | 2020-04-23 | 08:05:15 |
| 10 | 0 | 1 | 2020-04-24 | 2020-04-24 | 08:46:21 |
| 10 | 0 | 1 | 2020-04-28 | 2020-04-28 | 08:50:55 |
| 10 | 0 | 1 | 2020-04-29 | 2020-04-29 | 09:43:38 |
我需要从结果中显示 1 条记录,其条件类似于上面,该记录具有Status = 1
但没有结果Status = 0
:
| FingerId | Status_Logs | ShiftId | Date | DateOut | Time |
| -------- | ----------- | ------- | ---------- | ---------- | -------- |
| 10 | 0 | 1 | 2020-04-21 | 2020-04-21 | 08:18:03 |
| 10 | 0 | 1 | 2020-04-22 | 2020-04-22 | 07:57:08 |
| 10 | 0 | 1 | 2020-04-23 | 2020-04-23 | 08:05:15 |
| 10 | 0 | 1 | 2020-04-24 | 2020-04-24 | 08:46:21 |
| 10 | 0 | 1 | - | 2020-04-27 | - |
| 10 | 0 | 1 | 2020-04-28 | 2020-04-28 | 08:50:55 |
| 10 | 0 | 1 | 2020-04-29 | 2020-04-29 | 09:43:38 |
我有一个这样的现有查询,该查询的问题在于我无法显示我期望的输出。
[更新]这个问题已通过我标记为正确的答案得到解决。
SELECT
i.FingerId,
i.Status AS 'Status_Logs',
fs.ShiftId,
CASE WHEN i.Status = 0 THEN
MIN(i.Date)
END AS 'Date',
CASE WHEN i.Status = 0 AND MIN(i.Time) >= '19:00:00'
THEN DATE(DATE_ADD(i.Date, INTERVAL + 1 DAY))
ELSE
DATE(DATE_ADD(i.Date, INTERVAL + s.DayOut DAY))
END AS 'DateOut',
CASE WHEN i.Status = 0 THEN
i.Time
END AS 'Time'
FROM HrAttLogs AS i
INNER JOIN HrEmployee AS fs ON fs.FingerId = i.FingerId
INNER JOIN HrEmployeeShift AS s ON s.Id = fs.ShiftId
WHERE
i.Time >= s.ShiftIn_1
AND i.Date >= '2020-04-21'
AND i.Date <= '2020-05-10'
AND i.MachineIp = '10.20.20.73'
AND i.FingerId = 10
AND i.Status != 1
GROUP BY
i.Date
UNION
SELECT
i.FingerId,
j.Status AS 'Status_Logs',
fs.ShiftId,
NULL AS 'Date',
i.Date AS 'DateOut',
NULL AS 'Time'
FROM HrAttLogs AS i
LEFT JOIN HrAttLogs j ON j.Date = i.Date AND j.Status != 1
INNER JOIN HrEmployee AS fs ON fs.FingerId = i.FingerId
WHERE
i.Status = 1
AND j.Id IS NULL
ORDER BY
DateOut ASC;
有几十种方法可以做到这一点。这是其中之一:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,date DATE NOT NULL
,status TINYINT NOT NULL
);
INSERT INTO my_table VALUES
( 254,'2020-04-21',0),
( 64,'2020-04-22',0),
(14470,'2020-04-23',0),
(14991,'2020-04-23',1),
(14972,'2020-04-23',1),
(14842,'2020-04-24',0),
(14764,'2020-04-24',1),
(16222,'2020-04-27',1),
(16103,'2020-04-28',0),
(16024,'2020-04-28',1),
(15894,'2020-04-29',0);
SELECT x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.date = x.date
AND y.status = 0
WHERE x.status = 1
AND y.id IS NULL;
+-------+------------+--------+
| id | date | status |
+-------+------------+--------+
| 16222 | 2020-04-27 | 1 |
+-------+------------+--------+