34,587
社区成员
发帖
与我相关
我的任务
分享
-- 加了id怎么有次序
create table #kim(id int identity(1,1),closeprice decimal(18, 3), FLG int)
insert #kim(closeprice,FLG)
select 6.11,NULL union all
select 6.39,NULL union all
select 6.51,NULL union all
select 6.4,NULL union all
select 6.39,NULL union all
select 5.5,NULL union all
select 5.63,NULL union all
select 5.64,NULL union all
select 5.62,NULL union all
select 5.64,NULL union all
select 5.64,NULL union all
select 5.65,NULL union all
select 5.65,NULL union all
select 5.48,NULL union all
select 5.4,NULL
go
WITH p1 AS (
SELECT id, closeprice, p1id
FROM (
SELECT c.id, c.closeprice,
p1.id p1id, p1.closeprice p1closeprice,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p1.id DESC) n
FROM #kim c
JOIN #kim p1
ON p1.id < c.id
AND p1.closeprice <> c.closeprice
) T
WHERE n = 1
AND p1closeprice < closeprice
),
p2 AS (
SELECT id, closeprice, p2id
FROM (
SELECT c.id, c.closeprice,
p2.id p2id, p2.closeprice p2closeprice,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p2.id DESC) n
FROM p1 c
JOIN #kim p2
ON p2.id < c.p1id
AND p2.closeprice <> c.closeprice
) T
WHERE n = 1
AND p2closeprice < closeprice
),
n1 AS (
SELECT id, closeprice, n1id
FROM (
SELECT c.id, c.closeprice,
n1.id n1id, n1.closeprice n1closeprice,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY n1.id) n
FROM #kim c
JOIN #kim n1
ON n1.id > c.id
AND n1.closeprice <> c.closeprice
) T
WHERE n = 1
AND n1closeprice < closeprice
),
n2 AS (
SELECT id, closeprice, n2id
FROM (
SELECT c.id, c.closeprice,
n2.id n2id, n2.closeprice n2closeprice,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY n2.id) n
FROM n1 c
JOIN #kim n2
ON n2.id > c.n1id
AND n2.closeprice <> c.closeprice
) T
WHERE n = 1
AND n2closeprice < closeprice
)
/*
SELECT k.*,
p1.p1id, p2.p2id,
n1.n1id, n2.n2id
FROM #kim k
FULL OUTER JOIN p1 ON p1.id = k.id
FULL OUTER JOIN p2 ON p2.id = k.id
FULL OUTER JOIN n1 ON n1.id = k.id
FULL OUTER JOIN n2 ON n2.id = k.id
*/
UPDATE #kim
SET FLG = 1
FROM #kim, p2, n2
WHERE #kim.id = p2.id
AND #kim.id = n2.id
SELECT *
FROM #kim
id closeprice FLG
----------- --------------------------------------- -----------
1 6.110 NULL
2 6.390 NULL
3 6.510 1
4 6.400 NULL
5 6.390 NULL
6 5.500 NULL
7 5.630 NULL
8 5.640 NULL
9 5.620 NULL
10 5.640 NULL
11 5.640 NULL
12 5.650 1
13 5.650 1
14 5.480 NULL
15 5.400 NULL