示例:
record_date 日期;
record_time 时间
中间加空格符合日期时间格式
AND CONVERT ( DATETIME, record_date + ' ' + record_time ) >= '2022-03-08 14:44:13'
下面是完整代码:
SELECT
c.work_shift,
c.pid ,
c.num,
d.lose ,
gp.username,
c.recordName
FROM
(
SELECT
a.work_shift,
a.pid,
a.recordName,
COUNT ( a.date ) num
FROM
(
SELECT
pid,
record_date DATE,
work_shift,
recordName
FROM
greenRecord
WHERE
1 = 1
**AND CONVERT ( DATETIME, record_date + ' ' + record_time ) >= '2022-03-08** 14:44:13'
AND CONVERT ( DATETIME, record_date + ' ' + record_time ) <= '2022-03-22 14:44:21'
GROUP BY
record_date,
pid,
work_shift,
recordName
) a
GROUP BY
a.pid,
a.work_shift,
a.recordName
) c
LEFT JOIN (
SELECT COUNT
( b.date ) lose,
b.pid
FROM
(
SELECT
pid,
record_date,
COUNT ( record_date ) DATE
FROM
greenRecord
WHERE
1 = 1
AND CONVERT ( DATETIME, record_date + ' ' + record_time ) >= '2022-03-08 14:44:13'
AND CONVERT ( DATETIME, record_date + ' ' + record_time ) <= '2022-03-22 14:44:21'
GROUP BY
record_date,
pid
HAVING
COUNT ( record_date ) < 2
) b
GROUP BY
b.pid
) d ON c.pid = d.pid
LEFT JOIN greenPerson gp ON gp.identity_card= c.pid
WHERE
1 =1