select min(字段1),max(字段2) from tb group by left(字段1,1),len(字段1)
use Tempdb go --> -->
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([ID1] int,[ID2] int) Insert #T select 63200,63320 union all select 63320,63422 union all select 63422,63500 union all select 63500,63650 union all select 63650,63740 union all select 63740,63820 union all select 63820,63900 union all select 63900,64000 union all select 77000,77055 union all select 77055,77078 union all select 77078,77128 union all select 77128,77163 union all select 77163,77213 union all select 77213,77299 union all select 77299,77370 union all select 77370,77469 union all select 77469,77551 union all select 77551,77660 union all select 77660,77680 union all select 77680,77780 union all select 77780,77880 union all select 77880,77982 union all select 196570,196700 union all select 196700,196820 union all select 196820,196900 union all select 196900,196990 union all select 196990,197000 Go Select [ID1],Type='ID1' from #T AS a WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [ID2]=a.[ID1]) UNION Select [ID2],Type='ID2' from #T AS a WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [ID1]=a.[ID2]) /* ID1 Type 63200 ID1 64000 ID2 77000 ID1 77982 ID2 196570 ID1 197000 ID2 */
declare @T table (字段1 int,字段2 int) insert into @T select 63200,63320 union all select 63320,63422 union all select 63422,63500 union all select 63500,63650 union all select 63650,63740 union all select 63740,63820 union all select 63820,63900 union all select 63900,64000 union all select 77000,77055 union all select 77055,77078 union all select 77078,77128 union all select 77128,77163 union all select 77163,77213 union all select 77213,77299 union all select 77299,77370 union all select 77370,77469 union all select 77469,77551 union all select 77551,77660 union all select 77660,77680 union all select 77680,77780 union all select 77780,77880 union all select 77880,77982 union all select 196570,196700 union all select 196700,196820 union all select 196820,196900 union all select 196900,196990 union all select 196990,197000select min(字段1) as 字段1,max(字段2) as 字段2 from @T group by left(字段1,2) order by 1 /* 字段1 字段2 ----------- ----------- 63200 64000 77000 77982 196570 197000 */
create table huiyaxiong(字段1 int, 字段2 int)insert into huiyaxiong select 63200, 63320 union all select 63320, 63422 union all select 63422, 63500 union all select 63500, 63650 union all select 63650, 63740 union all select 63740, 63820 union all select 63820, 63900 union all select 63900, 64000 union all select 77000, 77055 union all select 77055, 77078 union all select 77078, 77128 union all select 77128, 77163 union all select 77163, 77213 union all select 77213, 77299 union all select 77299, 77370 union all select 77370, 77469 union all select 77469, 77551 union all select 77551, 77660 union all select 77660, 77680 union all select 77680, 77780 union all select 77780, 77880 union all select 77880, 77982 union all select 196570, 196700 union all select 196700, 196820 union all select 196820, 196900 union all select 196900, 196990 union all select 196990, 197000 select 字段1/10000 段, min(字段1) 字段1, max(字段2) 字段2 from huiyaxiong group by 字段1/10000段 字段1 字段2 ----------- ----------- ----------- 6 63200 64000 7 77000 77982 19 196570 197000(3 row(s) affected)
Select [ID1],Type='ID1' from TB AS a WHERE NOT EXISTS(SELECT 1 FROM TB WHERE [ID2]=a.[ID1]) OR NOT EXISTS(SELECT 1 FROM TB WHERE [ID1]=a.[ID2])
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID1] int,[ID2] int)
Insert #T
select 63200,63320 union all
select 63320,63422 union all
select 63422,63500 union all
select 63500,63650 union all
select 63650,63740 union all
select 63740,63820 union all
select 63820,63900 union all
select 63900,64000 union all
select 77000,77055 union all
select 77055,77078 union all
select 77078,77128 union all
select 77128,77163 union all
select 77163,77213 union all
select 77213,77299 union all
select 77299,77370 union all
select 77370,77469 union all
select 77469,77551 union all
select 77551,77660 union all
select 77660,77680 union all
select 77680,77780 union all
select 77780,77880 union all
select 77880,77982 union all
select 196570,196700 union all
select 196700,196820 union all
select 196820,196900 union all
select 196900,196990 union all
select 196990,197000
Go
Select [ID1],Type='ID1' from #T AS a WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [ID2]=a.[ID1])
UNION
Select [ID2],Type='ID2' from #T AS a WHERE NOT EXISTS(SELECT 1 FROM #T WHERE [ID1]=a.[ID2])
/*
ID1 Type
63200 ID1
64000 ID2
77000 ID1
77982 ID2
196570 ID1
197000 ID2
*/
declare @T table (字段1 int,字段2 int)
insert into @T
select 63200,63320 union all
select 63320,63422 union all
select 63422,63500 union all
select 63500,63650 union all
select 63650,63740 union all
select 63740,63820 union all
select 63820,63900 union all
select 63900,64000 union all
select 77000,77055 union all
select 77055,77078 union all
select 77078,77128 union all
select 77128,77163 union all
select 77163,77213 union all
select 77213,77299 union all
select 77299,77370 union all
select 77370,77469 union all
select 77469,77551 union all
select 77551,77660 union all
select 77660,77680 union all
select 77680,77780 union all
select 77780,77880 union all
select 77880,77982 union all
select 196570,196700 union all
select 196700,196820 union all
select 196820,196900 union all
select 196900,196990 union all
select 196990,197000select min(字段1) as 字段1,max(字段2) as 字段2 from @T
group by left(字段1,2) order by 1
/*
字段1 字段2
----------- -----------
63200 64000
77000 77982
196570 197000
*/
create table huiyaxiong(字段1 int, 字段2 int)insert into huiyaxiong
select 63200, 63320 union all
select 63320, 63422 union all
select 63422, 63500 union all
select 63500, 63650 union all
select 63650, 63740 union all
select 63740, 63820 union all
select 63820, 63900 union all
select 63900, 64000 union all
select 77000, 77055 union all
select 77055, 77078 union all
select 77078, 77128 union all
select 77128, 77163 union all
select 77163, 77213 union all
select 77213, 77299 union all
select 77299, 77370 union all
select 77370, 77469 union all
select 77469, 77551 union all
select 77551, 77660 union all
select 77660, 77680 union all
select 77680, 77780 union all
select 77780, 77880 union all
select 77880, 77982 union all
select 196570, 196700 union all
select 196700, 196820 union all
select 196820, 196900 union all
select 196900, 196990 union all
select 196990, 197000
select 字段1/10000 段,
min(字段1) 字段1,
max(字段2) 字段2
from huiyaxiong
group by 字段1/10000段 字段1 字段2
----------- ----------- -----------
6 63200 64000
7 77000 77982
19 196570 197000(3 row(s) affected)
[ID1],Type='ID1' from TB AS a
WHERE
NOT EXISTS(SELECT 1 FROM TB WHERE [ID2]=a.[ID1])
OR
NOT EXISTS(SELECT 1 FROM TB WHERE [ID1]=a.[ID2])