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 *****************************
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
*****************************
那就按照where条件一个一个的屏蔽掉然后再查