TABLE a: ID,姓名,手机号码
1 张三 13212314551
2 里斯 13572325458
3 王五 13134343452
4 徐之 15831055888
5 林小 13821223241我想输入的格式为:序号 类别 姓名 号码
1 移动 里斯 13572325458
2 移动 林小 13821223241
3 移动 徐之 158310558881 联通 王五 13134343452
2 联通 张三 13212314551
简单的意思就是 如何分类序号排列,按姓名
1 张三 13212314551
2 里斯 13572325458
3 王五 13134343452
4 徐之 15831055888
5 林小 13821223241我想输入的格式为:序号 类别 姓名 号码
1 移动 里斯 13572325458
2 移动 林小 13821223241
3 移动 徐之 158310558881 联通 王五 13134343452
2 联通 张三 13212314551
简单的意思就是 如何分类序号排列,按姓名
(select count(1)+1 from a where case when left(号码,3) in ('130','131','132','133','150','151','152','153') then '联通' else '移动' end=case when left(a1.号码,3) in ('130','131','132','133','150','151','152','153') then '联通' else '移动' end and 号码<=a1.号码) as 序号,
类别=case when left(号码,3) in ('130','131','132','133','150','151','152','153') then '联通' else '移动' end,
姓名,
号码
from a a1
order by case when left(号码,3) in ('130','131','132','133','150','151','152','153') then '联通' else '移动' end,号码
or SUBSTRING(手机号码 , 1 , 3) ='131'
or SUBSTRING(手机号码 , 1 , 3) ='132'
or SUBSTRING(手机号码 , 1 , 3) ='133' then '联通' else '移动' end) as 类别
from a order by 类别,ID
insert into a select 1,'张三','13212314551'
insert into a select 2,'里斯','13572325458'
insert into a select 3,'王五','13134343452'
insert into a select 4,'徐之','15831055888'
insert into a select 5,'林小','13821223241'
select * into # from (
select 姓名,
case when left(手机号码,3) in(135,136,137,138,139,158) then '移动' when left(手机号码,3) in(131,132) then '联通' end as '商家'
,手机号码
from a) tpselect id=(select count(1) from # where 商家=tp.商家 and 姓名<tp.姓名)+1,* from # tp
order by 商家 desc,idid 姓名 商家 手机号码
1 里斯 移动 13572325458
2 林小 移动 13821223241
3 徐之 移动 15831055888
1 王五 联通 13134343452
2 张三 联通 13212314551
create table t1(id int,name varchar(10),num varchar(15))
go
insert into t1
select 1,'张三','13212314551' union all
select 2,'里斯','13572325458' union all
select 3,'王五','13134343452' union all
select 4,'徐之','15831055888' union all
select 5,'林小','13821223241'select * from t1select id,
case when substring(num,1,3) in (135,138,158) then '移动'
when substring(num,1,3) in (131,132) then '联通'
end type,
name,num from t1 order by type
drop table tb
go
create table tb(ID int,姓名 varchar(10),手机号码 varchar(20))
insert tb
select 1, '张三', '13212314551' union all
select 2, '里斯', '13572325458' union all
select 3, '王五', '13134343452' union all
select 4, '徐之', '15831055888' union all
select 5, '林小', '13821223241'
select index_i=identity(int,1,1),类别='联通',姓名, 手机号码 as 号码 into #1 from tb where substring(手机号码,1,3)='131' or substring(手机号码,1,3)='132'
select index_i=identity(int,1,1),类别='移动',姓名, 手机号码 as 号码 into #2 from tb where substring(手机号码,1,3)<>'131' and substring(手机号码,1,3)!='132'
select * from #1 union all
select * from #2
order by 类别 desc
drop table #1
drop table #2接分了