set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(fphm int,kshm varchar(15))
insert tb select 2014,'00000001'
insert tb select 2014,'00000002'
insert tb select 2014,'00000003'
insert tb select 2014,'00000004'
insert tb select 2014,'00000005'
insert tb select 2014,'00000007'
insert tb select 2014,'00000008'
insert tb select 2014,'00000009'
insert tb select 2013,'00000120'
insert tb select 2013,'00000121'
insert tb select 2013,'00000122'
insert tb select 2013,'00000124'
insert tb select 2013,'00000125'
select fphm,case when min(t.kshm)<>max(t.kshm) then cast(min(t.kshm) as varchar)+','+cast(max(t.kshm) as varchar) else cast(min(t.kshm) as varchar) end kshm
from
(select fphm,kshm,cast(kshm as int)-(select count(*) from tb b where a.kshm>=b.kshm) cnt
from tb a)T group by fphm,cnt
/*fphm kshm
----------- -------------------------------------------------------------
2014 00000001,00000005
2014 00000007,00000009
2013 00000120,00000122
2013 00000124,00000125
*/
if object_id('tb')is not null drop table tb
go
create table tb(fphm int,kshm varchar(15))
insert tb select 2014,'00000001'
insert tb select 2014,'00000002'
insert tb select 2014,'00000003'
insert tb select 2014,'00000004'
insert tb select 2014,'00000005'
insert tb select 2014,'00000007'
insert tb select 2014,'00000008'
insert tb select 2014,'00000009'
insert tb select 2013,'00000120'
insert tb select 2013,'00000121'
insert tb select 2013,'00000122'
insert tb select 2013,'00000124'
insert tb select 2013,'00000125'
select fphm,case when min(t.kshm)<>max(t.kshm) then cast(min(t.kshm) as varchar)+','+cast(max(t.kshm) as varchar) else cast(min(t.kshm) as varchar) end kshm
from
(select fphm,kshm,cast(kshm as int)-(select count(*) from tb b where a.kshm>=b.kshm) cnt
from tb a)T group by fphm,cnt
/*fphm kshm
----------- -------------------------------------------------------------
2014 00000001,00000005
2014 00000007,00000009
2013 00000120,00000122
2013 00000124,00000125
*/
INSERT @TB
SELECT 2014, '00000001' UNION ALL
SELECT 2014, '00000002' UNION ALL
SELECT 2014, '00000003' UNION ALL
SELECT 2014, '00000004' UNION ALL
SELECT 2014, '00000005' UNION ALL
SELECT 2014, '00000007' UNION ALL
SELECT 2014, '00000008' UNION ALL
SELECT 2014, '00000009' UNION ALL
SELECT 2013, '00000120' UNION ALL
SELECT 2013, '00000121' UNION ALL
SELECT 2013, '00000122' UNION ALL
SELECT 2013, '00000124' UNION ALL
SELECT 2013, '00000125'
SELECT A.FPHM,A.KSHM,MIN(B.KSHM) AS KSHM
FROM (
SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE FPHM=A.FPHM AND CAST(KSHM AS INT)=CAST(A.KSHM AS INT)-1)) A JOIN (
SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE FPHM=A.FPHM AND CAST(KSHM AS INT)=CAST(A.KSHM AS INT)+1)) B
ON A.FPHM=B.FPHM AND CAST(B.KSHM AS INT)>CAST(A.KSHM AS INT)
GROUP BY A.FPHM,A.KSHM
/*
FPHM KSHM KSHM
----------- ---------- ----------
2014 00000001 00000005
2014 00000007 00000009
2013 00000120 00000122
2013 00000124 00000125
*/
INSERT @a SELECT 2014,'00000001'
UNION ALL SELECT 2014,'00000002'
UNION ALL SELECT 2014,'00000003'
UNION ALL SELECT 2014,'00000004'
UNION ALL SELECT 2014,'00000005'
UNION ALL SELECT 2014,'00000007'
UNION ALL SELECT 2014,'00000008'
UNION ALL SELECT 2014,'00000009'
UNION ALL SELECT 2013,'00000120'
UNION ALL SELECT 2013,'00000121'
UNION ALL SELECT 2013,'00000122'
UNION ALL SELECT 2013,'00000124'
UNION ALL SELECT 2013,'00000125' SELECT *,(SELECT min(kshm) FROM @a b WHERE fphm=a.fphm AND kshm>a.kshm AND NOT EXISTS(SELECT 1 FROM @a WHERE cast(kshm AS int)=cast(b.kshm AS int)+1)) x
FROM @a a WHERE NOT EXISTS(SELECT 1 FROM @a WHERE fphm=a.fphm AND cast(kshm AS int)=cast(a.kshm AS int)-1)
--result
/*fphm kshm x
----------- -------------------- --------------------
2014 00000001 00000005
2014 00000007 00000009
2013 00000120 00000122
2013 00000124 00000125(所影响的行数为 4 行)*/
from(
select * from Grade as A
where not exists(select 1 from Grade where A.fphm=Grade.fphm and Cast(A.kshm as int)=Cast(Grade.kshm as int)+1))B
join
(select * from Grade as A
where not exists(select 1 from Grade where A.fphm=Grade.fphm and Cast(A.kshm as int)=Cast(Grade.kshm as int)-1))C
on B.fphm=C.fphm and cast(B.kshm as int)<cast(C.kshm as int)
group by B.fphm,B.kshm
where not exists(select 1 from Grade where A.fphm=Grade.fphm and Cast(A.kshm as int)=Cast(Grade.kshm as int)+1)是
2014 00000001
2014 00000007
2013 00000120
2013 00000124
select * from Grade as A
where not exists(select 1 from Grade where A.fphm=Grade.fphm and Cast(A.kshm as int)=Cast(Grade.kshm as int)-1)是
2014 00000005
2014 00000009
2013 00000122
2013 00000125
最外一层sql是将这两组数据进行组合。写的太好了,顶。
INSERT @TB
SELECT 2014, '00000001' UNION ALL
SELECT 2014, '00000002' UNION ALL
SELECT 2014, '00000003' UNION ALL
SELECT 2014, '00000004' UNION ALL
SELECT 2014, '00000005' UNION ALL
SELECT 2014, '00000007' UNION ALL
SELECT 2014, '00000008' UNION ALL
SELECT 2014, '00000009' UNION ALL
SELECT 2013, '00000120' UNION ALL
SELECT 2013, '00000121' UNION ALL
SELECT 2013, '00000122' UNION ALL
SELECT 2013, '00000124' UNION ALL
SELECT 2013, '00000125'SELECT *,IDENTITY(INT,1,1)SORT INTO #A FROM @TBSELECT FPHM,MIN(KSHM)KSHM1,MAX(KSHM)KSHM2
FROM #A
GROUP BY FPHM,SORT-CAST(KSHM AS INT)
ORDER BY FPHM DESC,KSHM1DROP TABLE #A