2000系统 表格如下:id time numa 1:00 111
a 2:00 15
.. ... ..
a 9:00 22
a 10:00 213
a 11:00 21
a 12:00 99
a 13:00 181
.. . ......a 16:00 15
... b 1:00 111
b 2:00 15
.. ... ..
b 9:00 22
b 10:00 213
b 11:00 21
b 12:00 99
b 13:00 181
.. . ......b 16:00 16
... a和b 在 16:00 前 完全一致,希望得到
a 16:00 b
a 2:00 15
.. ... ..
a 9:00 22
a 10:00 213
a 11:00 21
a 12:00 99
a 13:00 181
.. . ......a 16:00 15
... b 1:00 111
b 2:00 15
.. ... ..
b 9:00 22
b 10:00 213
b 11:00 21
b 12:00 99
b 13:00 181
.. . ......b 16:00 16
... a和b 在 16:00 前 完全一致,希望得到
a 16:00 b
INSERT INTO test
SELECT 'a', '1:00', 111
UNION ALL
SELECT 'a', '2:00', 15
UNION ALL
SELECT 'b', '1:00', 111
UNION ALL
SELECT 'b', '2:00', 15
UNION ALL
SELECT 'a', '3:00', 121
UNION ALL
SELECT 'b', '3:00', 15
UNION ALL
SELECT 'a', '4:00', 121
UNION ALL
SELECT 'b', '4:00', 15
SELECT MIN (a.id) id,MIN(a.[time])[time],MAX(CASE WHEN a.id=a.id THEN a.id END ) b
FROM test a LEFT JOIN test b ON a.id<>b.id AND a.[time]=b.[time] AND a.num=b.num
WHERE b.id IS NULL
/*
id time b
---------- ---------------- ----------
a 03:00:00.0000000 b
(1 行受影响)
*/
;with cet1 as(
select *,row_number()over(order by time) rn from tb where id = 'a'
),cet2 as(
select *,row_number()over(order by time) rn from tb where id = 'b'
)
select a.id,a.time,'b' from cet1 a where a.rn = (select top(1)rn from cet1 b, cet2 c
where a.rn = b.rn and a.time <> b.time order by rn)-1
INSERT INTO test
SELECT 'a', '1:00', 111
UNION ALL
SELECT 'a', '2:00', 15
UNION ALL
SELECT 'b', '1:00', 111
UNION ALL
SELECT 'b', '2:00', 15
UNION ALL
SELECT 'a', '3:00', 121
UNION ALL
SELECT 'b', '3:00', 15
UNION ALL
SELECT 'a', '4:00', 121
UNION ALL
SELECT 'b', '4:00', 15
SELECT MIN (a.id) id,MIN(a.[time])[time],MAX (a.id) b
FROM test a LEFT JOIN test b ON a.id<>b.id AND a.[time]=b.[time] AND a.num=b.num
WHERE b.id IS NULL
/*
id time b
---------- ---------------- ----------
a 03:00:00.0000000 b
(1 行受影响)
*/