select * from tb k where not exists(select * from tb where left(k.col)=col and cast(stuff(k.col,1,1,'') as int)< cast(stuff(col,1,1,'') as int))
select * from ta a where not exists(select 1 from ta where cast(replace(bh,'a','') as int = cast(replace(a.bh,'a','') as int) + 1)
if not object_id('tb') is null drop table tb Go Create table tb([编号] nvarchar(5)) Insert tb select N'a100' union all select N'a102' union all select N'a103' union all select N'a105' union all select N'a107' union all select N'a207' union all select N'a1022' Go Select [max]=min(cast(substring([编号],patindex('%[0-9]%',[编号]),len([编号])-patindex('%[0-9]%',[编号])+1)as int))from tb
create table [tb]([bh] varchar(5)) insert [tb] select 'a102' union all select 'a103' union all select 'a105' union all select 'a907' union all select 'a1001' union all select 'b203' union all select 'b204' union all select 'b206' select * from tb a where exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)-1)/** bh ----- a103 b204 **/
改一下13楼的 ---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([bh] varchar(5)) insert [tb] select 'a102' union all select 'a103' union all select 'a105' union all select 'a907' union all select 'a1001' union all select 'b203' union all select 'b204' union all select 'b206'
---查询--- select * from tb a where exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)-1) and not exists (select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)+1) ---结果--- bh ----- a103 b204(所影响的行数为 2 行)
在order by里replace啊,不是说了吗
--> 测试数据:@tb declare @tb table([id] varchar(5)) insert @tb select 'a102' union all select 'a103' union all select 'a105' union all select 'a907' union all select 'a1001' union all select 'b203' union all select 'b204' union all select 'b206'select a.id from @tb a where exists( select 1 from @tb b where left(a.id,1)=left(a.id,1) and cast(stuff(a.id,1,1,'') as int)= cast(stuff(b.id,1,1,'') as int)-1) /* id ----- a102 b203 (2 行受影响) */
select *,cast(STUFF(col,1,1,'') as int) AS st into # from tb a where not exists(select 1 from tb where left(col,1)=left(a.col,1) and cast(stuff(col,1,1,'') as int) = cast(stuff(a.col,1,1,'') as int)+1) select COL from # p where not exists(select 1 from # where left(col,1)=left(p.col,1) and ST<p.st)
select bh+ltrim(min(t)) from( select left(bh,1) as bh, cast(stuff(bh,1,1,'') as int) t from tb a where not exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int) + 1) ) a group by bh
-------------- a103 b204(所影响的行数为 2 行)
虽然结贴啦还试贴下create table #UU1 ( Num varchar(20) ) insert into #UU1 select 'a102' insert into #UU1 select 'a103' insert into #UU1 select 'a105' insert into #UU1 select 'a907' insert into #UU1 select 'a1001' insert into #UU1 select 'b203' insert into #UU1 select 'b204' insert into #UU1 select 'b206';with hgo as ( select substring(Num,1,1) num,substring(Num,2,len(num)-1) cnt, row_number() over(partition by substring(Num,1,1) order by Num asc) as rank from #UU1 ), h as ( select * from hgo h where not exists(select * from hgo where num=h.num and rank=h.rank-1 and cnt<>h.cnt-1) ), g as ( select num,max(cnt) cnt from h group by num ) select ltrim(num+ltrim(cnt)) from g---------------------- a103 b204(2 行受影响)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([bh] varchar(5)) insert [tb] select 'a102' union all select 'a103' union all select 'a105' union all select 'a907' union all select 'a1001' union all select 'b103' union all select 'b104' union all select 'b106'按照大家都方法来 出来的结果都不对了
group by bhselect bh+ltrim(min(t)) from( select left(bh,1) as bh, cast(stuff(bh,1,1,'') as int) t from tb a where not exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int) + 1 and left(bh,1)=left(a.bh,1)) ) a group by bh --and left(bh,1)=left(a.bh,1) 这个条件加上就好了
from tb k
where not exists(select * from tb where left(k.col)=col and cast(stuff(k.col,1,1,'') as int)< cast(stuff(col,1,1,'') as int))
from ta a
where not exists(select 1 from ta where cast(replace(bh,'a','') as int = cast(replace(a.bh,'a','') as int) + 1)
drop table tb
Go
Create table tb([编号] nvarchar(5))
Insert tb
select N'a100' union all
select N'a102' union all
select N'a103' union all
select N'a105' union all
select N'a107' union all
select N'a207' union all
select N'a1022'
Go
Select
[max]=min(cast(substring([编号],patindex('%[0-9]%',[编号]),len([编号])-patindex('%[0-9]%',[编号])+1)as int))from tb
insert [tb]
select 'a102' union all
select 'a103' union all
select 'a105' union all
select 'a907' union all
select 'a1001' union all
select 'b203' union all
select 'b204' union all
select 'b206'
select *
from tb a
where exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)-1)/**
bh
-----
a103
b204
**/
a100
a102
a103
a105
a107
a207
a1022 字段是nvarchar(50),怎么求出最大的!!
(求最好解决方案)!!
=================================================
9楼.这个问题你的方案最好.但怎么求出来整个字符串呢?你求的只是整数部分.
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([bh] varchar(5))
insert [tb]
select 'a102' union all
select 'a103' union all
select 'a105' union all
select 'a907' union all
select 'a1001' union all
select 'b203' union all
select 'b204' union all
select 'b206'
---查询---
select *
from tb a
where exists(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)-1)
and not exists
(select 1 from tb where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int)+1)
---结果---
bh
-----
a103
b204(所影响的行数为 2 行)
在order by里replace啊,不是说了吗
declare @tb table([id] varchar(5))
insert @tb
select 'a102' union all
select 'a103' union all
select 'a105' union all
select 'a907' union all
select 'a1001' union all
select 'b203' union all
select 'b204' union all
select 'b206'select a.id from @tb a where
exists(
select 1 from @tb b where
left(a.id,1)=left(a.id,1)
and cast(stuff(a.id,1,1,'') as int)= cast(stuff(b.id,1,1,'') as int)-1)
/*
id
-----
a102
b203 (2 行受影响)
*/
into #
from tb a
where not exists(select 1 from tb where left(col,1)=left(a.col,1) and cast(stuff(col,1,1,'') as int) = cast(stuff(a.col,1,1,'') as int)+1)
select COL
from # p
where not exists(select 1 from # where left(col,1)=left(p.col,1) and ST<p.st)
from(
select left(bh,1) as bh, cast(stuff(bh,1,1,'') as int) t
from tb a
where not exists(select 1 from tb
where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int) + 1)
) a
group by bh
--------------
a103
b204(所影响的行数为 2 行)
(
Num varchar(20)
)
insert into #UU1 select 'a102'
insert into #UU1 select 'a103'
insert into #UU1 select 'a105'
insert into #UU1 select 'a907'
insert into #UU1 select 'a1001'
insert into #UU1 select 'b203'
insert into #UU1 select 'b204'
insert into #UU1 select 'b206';with hgo as
(
select substring(Num,1,1) num,substring(Num,2,len(num)-1) cnt,
row_number() over(partition by substring(Num,1,1) order by Num asc) as rank
from #UU1
),
h as
(
select * from hgo h where not exists(select * from hgo where num=h.num and rank=h.rank-1 and cnt<>h.cnt-1)
),
g as
(
select num,max(cnt) cnt from h group by num
)
select ltrim(num+ltrim(cnt)) from g----------------------
a103
b204(2 行受影响)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([bh] varchar(5))
insert [tb]
select 'a102' union all
select 'a103' union all
select 'a105' union all
select 'a907' union all
select 'a1001' union all
select 'b103' union all
select 'b104' union all
select 'b106'按照大家都方法来 出来的结果都不对了
group by bhselect bh+ltrim(min(t))
from(
select left(bh,1) as bh, cast(stuff(bh,1,1,'') as int) t
from tb a
where not exists(select 1 from tb
where cast(stuff(bh,1,1,'') as int) = cast(stuff(a.bh,1,1,'') as int) + 1 and left(bh,1)=left(a.bh,1))
) a
group by bh
--and left(bh,1)=left(a.bh,1) 这个条件加上就好了