分组查询最小值和最大值问题

Question

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--已用编号分布查询
SELECT col1,start_col2=col2,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>=a.col2 
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2+1))
FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          2           3
a          6           8
b          3           3
b          5           7
--*/

Answer 1

SELECT a,cast(b as varchar) +'-'+
    cast((SELECT MIN(b) FROM t1 aa WHERE a=T.a AND b>=T.b AND NOT EXISTS(SELECT * FROM t1 WHERE a=aa.a AND b=aa.b+1)) as varchar) 
FROM t1 T
WHERE NOT EXISTS(
    SELECT * FROM t1 WHERE a=T.a and b=T.b-1)

Answer 2

/**
a1 1-3
a1 5-6
b1 1-3
b1 5-6**/

Answer 3

--2000我习惯用临时表处理的(已测)
select *,idd=identity(int,1,1) into #t1 from t1 a where not exists(select 0 from t1 b where a.a=b.a and b.b=a.b-1)
select *,idd=identity(int,1,1) into #t2 from t1 a where not exists(select 0 from t1 b where a.a=b.a and b.b=a.b+1)
select a.a,rtrim(a.idd)+'-'+rtrim(b.idd) b from #t1 a full join #t2 b on a.idd=b.idd
----如果是2005可以试下这个，没有2005环境
select a.a,rtrim(a.idd)+'-'+rtrim(b.idd) b from
(select *,idd=ROW_NUMBER() OVER (ORDER BY id) from t1 a where not exists(select 0 from t1 b where a.a=b.a and b.b=a.b-1))a
full join
(select *,idd=ROW_NUMBER() OVER (ORDER BY id) from t1 a where not exists(select 0 from t1 b where a.a=b.a and b.b=a.b+1))b 
on a.idd=b.idd

Answer 4

create table #t1(id int,a varchar(10),b int);insert into #t1(id,a,b) values(1,'a1',1);
insert into #t1(id,a,b) values(2,'a1',2);
insert into #t1(id,a,b) values(3,'a1',3);
insert into #t1(id,a,b) values(4,'a1',8);
insert into #t1(id,a,b) values(4,'a1',9);
insert into #t1(id,a,b) values(4,'a1',10);
insert into #t1(id,a,b) values(5,'a1',6);
insert into #t1(id,a,b) values(6,'b1',1);
insert into #t1(id,a,b) values(7,'b1',2);
insert into #t1(id,a,b) values(8,'b1',3);
insert into #t1(id,a,b) values(9,'b1',5);
insert into #t1(id,a,b) values(10,'b1',6);
goSELECT a,convert(varchar,b)+'-'+convert(varchar,(
        SELECT MIN(b) FROM #t1 aa
        WHERE a=a.a AND b>=a.b 
        AND NOT EXISTS(SELECT * FROM #t1 WHERE a=aa.a AND b=aa.b+1))) as c
FROM #t1 a
WHERE NOT EXISTS(
    SELECT * FROM #t1 WHERE a=a.a and b=a.b-1)
order by a,c