大家好,请问我想查出表A中,字段B 以10开头的数据,如果为0条的话就设值为10,如果count(*)为1的话,就设为102,为2则103,也就是说,如果有10开头数据的话,就取其最大值后面数+1,放到10后面,请问,这语句要怎样写?
如:
表A
B
10
结果:10
101
结果:102
101
102
105
结果:106
102
1099
结果:10100
如:
表A
B
10
结果:10
101
结果:102
101
102
105
结果:106
102
1099
结果:10100
-- Author :SQL77(只为思齐老)
-- Date :2010-01-19 09:05:37
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([B] int)
insert #TB
select 10 UNION ALL
select 102 UNION ALL
SELECT 103
--------------开始查询----------------------------INSERT #TB
SELECT DISTINCT
CASE WHEN (SELECT TOP 1 RIGHT(B,LEN(B)-2) FROM #TB ORDER BY B DESC) ='' THEN 101
ELSE (SELECT TOP 1 B+1 FROM #TB ORDER BY B DESC) END AS B
from #TB ----------------结果----------------------------
/* (所影响的行数为 3 行)B
-----------
104(所影响的行数为 1 行)
*/>>
select
max(col)
from
(
select
case when cast(left(col,2) as int)=10 then cast(right(col,len(col)-2) as int)+1
from
tb
)t
-- Author :SQL77(只为思齐老)
-- Date :2010-01-19 09:05:37
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([B] int)
insert #TB
select 10 UNION ALL
select 102 UNION ALL
SELECT 103
--------------开始查询--------------------------
select
max(b)
from
(
select
case when cast(left(b,2) as int)=10 then cast('10'+right(b,len(b)-2) as int)+1 end as b
from
#tb
)t
/*-----------
104(1 行受影响)*/
create table A
(
B varchar(100)
)insert into A values ('1099')
delete A
goAlter Proc P_GetNumber
as
begin
declare @StrValue varchar(100),@currentSubNum int,@currentValue varchar(100)
if not exists (select 1 from A)
begin
set @StrValue = '10'
end
else
begin
select @currentValue = B from A
if @currentValue = '10'
begin
set @StrValue = '101'
end
else
begin
if substring(@currentValue,1,2) = 10
begin
set @currentSubNum = Convert(int,SUBSTRING(@currentValue,2,LEN(@currentValue)))+1
set @StrValue = '10'+Convert(varchar,@currentSubNum)
end
end
select @StrValue as '编号'
end
end exec P_GetNumber