27,582
社区成员




create table #a(kh varchar(5),yi varchar(6),er varchar(4),sa varchar(4),si varchar(4),kc int)
insert into #a
select 'D0211','000002','0001', '0000', '0002', 1
union
select 'D0211', '000002', '0001', '0000', '0002', 7
union
select 'D0211', '000002', '0001', '0000', '0002',12
union
select 'D0211', '000002', '0001', '0000', '0002', 19
union
select 'D0211', '000002', '0001', '0000', '0002', 26
union
select 'D0211', '000002', '0001', '0000', '0002', 27
union
select 'D0211', '000002', '0001', '0000', '0003', 28
union
select 'D0211', '000002', '0001', '0000', '0003', 29
union
select 'D0211', '000002', '0001', '0000', '0003', 30
union
select 'D0211', '000002', '0001', '0000', '0003', 34
union
select 'D0211', '000002', '0001', '0000', '0003', 40
union
select 'D0211', '000002', '0001', '0000', '0003', 45
union
select 'D0211', '000002', '0001', '0000', '0003', 52
union
select 'D0211', '000002', '0001', '0000', '0002', 53
union
select 'D0211', '000002', '0001', '0000', '0002', 59
select kh as ykkh,yi,er,sa,si,MIN(kc) as [min],MAX(kc) as [max]
from
(
select *,1 as [sortnum]
from #a
where kc between 1 and 27
union
select *,2 as [sortnum]
from #a
where kc between 28 and 52
union
select *,3 as [sortnum]
from #a
where kc between 53 and 59
) as t
group by kh,yi,er,sa,si,[sortnum];
create table #a(kh varchar(5),yi varchar(6),er varchar(4),sa varchar(4),si varchar(4),kc int)
insert into #a
select 'D0211','000002','0001', '0000', '0002', 1
union
select 'D0211', '000002', '0001', '0000', '0002', 7
union
select 'D0211', '000002', '0001', '0000', '0002',12
union
select 'D0211', '000002', '0001', '0000', '0002', 19
union
select 'D0211', '000002', '0001', '0000', '0002', 26
union
select 'D0211', '000002', '0001', '0000', '0002', 27
union
select 'D0211', '000002', '0001', '0000', '0003', 28
union
select 'D0211', '000002', '0001', '0000', '0003', 29
union
select 'D0211', '000002', '0001', '0000', '0003', 30
union
select 'D0211', '000002', '0001', '0000', '0003', 34
union
select 'D0211', '000002', '0001', '0000', '0003', 40
union
select 'D0211', '000002', '0001', '0000', '0003', 45
union
select 'D0211', '000002', '0001', '0000', '0003', 52
union
select 'D0211', '000002', '0001', '0000', '0002', 53
union
select 'D0211', '000002', '0001', '0000', '0002', 59
select kh ,yi ,er, sa, si,MIN(kc)[min],MAX(kc)[max] from #a group by kh ,yi ,er, sa, si
/*
kh yi er sa si min max
----- ------ ---- ---- ---- ----------- -----------
D0211 000002 0001 0000 0002 1 59
D0211 000002 0001 0000 0003 28 52
use tempdb;
/*
create table #a(kh varchar(5),yi varchar(6),er varchar(4),sa varchar(4),si varchar(4),kc int)
insert into #a
select 'D0211','000002','0001', '0000', '0002', 1
union
select 'D0211', '000002', '0001', '0000', '0002', 7
union
select 'D0211', '000002', '0001', '0000', '0002',12
union
select 'D0211', '000002', '0001', '0000', '0002', 19
union
select 'D0211', '000002', '0001', '0000', '0002', 26
union
select 'D0211', '000002', '0001', '0000', '0002', 27
union
select 'D0211', '000002', '0001', '0000', '0003', 28
union
select 'D0211', '000002', '0001', '0000', '0003', 29
union
select 'D0211', '000002', '0001', '0000', '0003', 30
union
select 'D0211', '000002', '0001', '0000', '0003', 34
union
select 'D0211', '000002', '0001', '0000', '0003', 40
union
select 'D0211', '000002', '0001', '0000', '0003', 45
union
select 'D0211', '000002', '0001', '0000', '0003', 52
union
select 'D0213', '000002', '0001', '0000', '0002', 53
union
select 'D0213', '000002', '0001', '0000', '0002', 59
*/
select kh as ykkh,yi,er,sa,si,MIN(kc) as [min],MAX(kc) as [max]
from #a
group by kh,yi,er,sa,si;
create table #a(kh varchar(5),yi varchar(6),er varchar(4),sa varchar(4),si varchar(4),kc int)
insert into #a
select 'D0211','000002','0001', '0000', '0002', 1
union
select 'D0211', '000002', '0001', '0000', '0002', 7
union
select 'D0211', '000002', '0001', '0000', '0002',12
union
select 'D0211', '000002', '0001', '0000', '0002', 19
union
select 'D0211', '000002', '0001', '0000', '0002', 26
union
select 'D0211', '000002', '0001', '0000', '0002', 27
union
select 'D0211', '000002', '0001', '0000', '0003', 28
union
select 'D0211', '000002', '0001', '0000', '0003', 29
union
select 'D0211', '000002', '0001', '0000', '0003', 30
union
select 'D0211', '000002', '0001', '0000', '0003', 34
union
select 'D0211', '000002', '0001', '0000', '0003', 40
union
select 'D0211', '000002', '0001', '0000', '0003', 45
union
select 'D0211', '000002', '0001', '0000', '0003', 52
union
select 'D0211', '000002', '0001', '0000', '0002', 53
union
select 'D0211', '000002', '0001', '0000', '0002', 59
select kh ykkh, yi, er, sa, si, min(kc) [min], max(kc) [max]
from #a
group by kh , yi, er, sa, si
/*
ykkh yi er sa si min max
----- ------ ---- ---- ---- ----------- -----------
D0211 000002 0001 0000 0002 1 59
D0211 000002 0001 0000 0003 28 52
(所影响的行数为 2 行)
*/
drop table #a