34,838
社区成员




declare @tb table (v varchar(80))
insert into @tb select '河北'
insert into @tb select '河南'
insert into @tb select '河西'
insert into @tb select '河东'
select Pid = (select count(1) + 1 from (select * from @tb) b where v <a.v),*
from (select * from @tb) a
order by pId
/*
Pid v
----------- --------------------------------------------------------------------------------
1 河北
2 河东
3 河南
4 河西
(所影响的行数为 4 行)
*/
con1 < n.con1 or (con1 = n.con1 and con2 < n.con2) or (con1 = n.con1 and con2 = n.con2 and con3 < n.con3)
SQL codeselect 序列 = (select count(1) from
(
SELECT t.RES_DESCR 设备名称,t.model as 设备型号,d.class_descr as 设备类型,t.vendor as 生产厂商, t.integrator as 集成商,t.supplier as 供应商,
t.appsystem as 业务系统, t.location 地理位置
FROM v_res_netdevice_desc t ,res_def_class d
) m where 地理位置 < n.地理位置) + 1,*
from
(
SELECT t.RES_DESCR 设备名称,t.model as 设备型号,d.class_descr as 设备类型,t.vendor as 生产厂商, t.integrator as 集成商,t.supplier as 供应商,
t.appsystem as 业务系统, t.location 地理位置
FROM v_res_netdevice_desc t ,res_def_class d
) n
--错了.加个条件:
where 省份名称 < n.省份名称 or (省份名称 = n.省份名称 and cnt < n.cnt)
--加个条件:
where 省份名称 < n.省份名称 or (省份名称 < n.省份名称 and cnt < n.cnt)
select 序列 = (select count(1) from
(
SELECT t.RES_DESCR 设备名称,t.model as 设备型号,d.class_descr as 设备类型,t.vendor as 生产厂商, t.integrator as 集成商,t.supplier as 供应商,
t.appsystem as 业务系统, t.location 地理位置
FROM v_res_netdevice_desc t ,res_def_class d
) m where 地理位置 < n.地理位置) + 1,*
from
(
SELECT t.RES_DESCR 设备名称,t.model as 设备型号,d.class_descr as 设备类型,t.vendor as 生产厂商, t.integrator as 集成商,t.supplier as 供应商,
t.appsystem as 业务系统, t.location 地理位置
FROM v_res_netdevice_desc t ,res_def_class d
) n
select *,
IDENTITY(int,1,1) as 序号
into #t
from tab
order by a,b --注意a,b可能不唯一
select * from #t
drop table #t
select *, --可以把*替换成你需要的字段列表
(select count(1) from tab
where a<x.a or a=x.a and b<x.b or a=x.a and b=x.b and c<x.c or a=x.a and b=x.b and c=x.c and d<=x.d
) as 序号
from tab x
order by a,b,c,d
declare @tb table (v varchar(80))
insert into @tb select '河北'
insert into @tb select '河南'
insert into @tb select '河西'
insert into @tb select '河东'
select Pid = (select count(1) + 1 from (select * from @tb) b where v <a.v),*
from (select * from @tb) a
order by pId
/*
Pid v
----------- --------------------------------------------------------------------------------
1 河北
2 河东
3 河南
4 河西
(所影响的行数为 4 行)
*/