从SQL中其他列的表基的每一部分中选择最小和最大列值

Question

我有一个表格，显示每个员工在一个办公室的情况。示例SQL表EmpPTable名称如下：

>时间P06:1211399/02/01[/tr>23:11

id	员工id	日期P
1	11111	1397/01/02	01:30
2	111111	1398/05/09	05:30
3	111111	1398/06/07	05:10
4	22222	1398/08/09
5	22222	07:15
6	111111	11399/07/02	08:51
7	111111	11399/08/06	12:20
8	33333	1399/09/04	20:01
9	33333	1399/12/08	22:05
10	33333	1400/01
11	33333	1400/02/05	14:10
12	22222	1400/04/05	16:25

您应该构建一个group by列

mysql5.8

with tab1 as (
select 1 id, 1 user_id, '2021-11-01' dat from dual union all
select 2 id, 1 user_id, '2021-11-02' dat from dual union all
select 3 id, 1 user_id, '2021-11-03' dat from dual union all
select 4 id, 2 user_id, '2021-11-04' dat from dual union all
select 5 id, 2 user_id, '2021-11-05' dat from dual union all
select 6 id, 1 user_id, '2021-11-06' dat from dual union all
select 7 id, 1 user_id, '2021-11-07' dat from dual union all
select 8 id, 1 user_id, '2021-11-08' dat from dual union all
select 9 id, 3 user_id, '2021-11-09' dat from dual 
)
, tab2 as (
select t1.*,
case when lag(t1.user_id) over(order by t1.id) is null then 1
when lag(t1.user_id) over(order by t1.id) = t1.user_id then 0
else 1
end lg
from tab1 t1
)
, tab3 as (
select t1.*,
sum(t1.lg) over(order by t1.id) grp
from tab2 t1
)
select t1.user_id,
min(t1.dat),
max(t1.dat)
from tab3 t1
group by t1.user_id, t1.grp

我无法复制您的代码，因为缺少一些字段(serial1？(。但这是我的解决方案。

CREATE TABLE #tempEmployee (
ID INT,
EmployeeID INT,
EmpDate DATE,
EmpTime TIME
);
INSERT INTO #tempEmployee VALUES (1, '11111', '2021-01-10', '12:30');
INSERT INTO #tempEmployee VALUES (1, '11111', '2021-12-10', '12:33');
INSERT INTO #tempEmployee VALUES (1, '11111', '2021-11-10', '12:30');
INSERT INTO #tempEmployee VALUES (2, '22222', '2021-01-11', '12:30');
INSERT INTO #tempEmployee VALUES (2, '22222', '2021-11-11', '12:30');
SELECT * FROM #tempEmployee;
WITH MaxDate AS (SELECT EmployeeID, MAX(EmpDate) AS MaxEmpDate FROM #tempEmployee GROUP BY EmployeeID),
MinDate AS (SELECT EmployeeID, MIN(EmpDate) AS MinEmpDate FROM #tempEmployee GROUP BY EmployeeID)
SELECT n.EmployeeID,
n.MinEmpDate,
x.MaxEmpDate
FROM MinDate n 
INNER JOIN MaxDate x ON n.EmployeeID = x.EmployeeID
ORDER BY n.EmployeeID;
DROP TABLE #tempEmployee;

输出。。。。

EmployeeID  MinEmpDate  MaxEmpDate
11111       2021-01-10  2021-12-10
22222       2021-01-11  2021-11-11