22,209
社区成员
发帖
与我相关
我的任务
分享
declare @galenkeny table(ID INT IDENTITY(1,1),Num NUMERIC,D INT)
insert into @galenkeny
SELECT '636555','17' union all
select '636549','14' union all
select '636548','14' union all
select '636547','14' union ALL
select '636534','2' union ALL
select '636545','8' union ALL
select '636544','16' union ALL
select '636543','13' union ALL
select '636542','13' union ALL
select '636541','16' union ALL
select '636540','18' union ALL
select '636539','21' union ALL
select '636538','19' union ALL
select '636537','12' union ALL
select '636536','10' union ALL
select '636545','8' union ALL
select '636532','16' union ALL
select '636550','14' union ALL
select '636535','25' union ALL
select '636530','11'
--SELECT * FROM @galenkeny
;WITH cte AS
(
SELECT *,rn=DENSE_RANK()OVER(PARTITION BY D ORDER BY Num)
FROM @galenkeny
),
cte1 AS
(
SELECT * FROM cte a
WHERE a.D IN(SELECT D FROM cte GROUP BY D HAVING COUNT(D)>=2)
)
SELECT n.ID,n.Num,n.D,v.GS FROM
(
SELECT D,COUNT(*)AS GS FROM @galenkeny
WHERE D IN
(
SELECT g.D FROM
(
SELECT MAX(Num)ma_num ,MIN(Num)mi_num ,MAX(rn) ma_rn,MIN(rn)mi_rn,D D
FROM cte1
GROUP BY D
HAVING MAX(rn)<>MIN(rn) AND MAX(Num)-MIN(Num)=MAX(rn)-MIN(rn)
)g
)
GROUP BY D
)v,
@galenkeny n
WHERE v.D=n.D
ORDER BY n.D DESC
*****************************
ID Num D GS
18 636550 14 4
2 636549 14 4
3 636548 14 4
4 636547 14 4
8 636543 13 2
9 636542 13 2
*****************************