各位朋友,小弟的表有如下记录,请看!表1:
s-11-0001
s-11-0002
s-11-0003
s-11-0005
小弟的表1如上所示,怎样做SELECT,才能找出右边四个数字最小记录,而该记录又没有使用过的?范围是从“0001”至“9999”为止,而前面的"s-11" 是固定的!
最终想要的SELECT结果是:“s-11-0004”(因为0001至9999这堆数字之中,1、2、3、5都用了,未用的数字之中,只有4是最少的,所以最后想要的结果是“s-11-0004”)
表2:
s-11-0029
s-11-0030最后想要的结果:s-11-0001
s-11-0001
s-11-0002
s-11-0003
s-11-0005
小弟的表1如上所示,怎样做SELECT,才能找出右边四个数字最小记录,而该记录又没有使用过的?范围是从“0001”至“9999”为止,而前面的"s-11" 是固定的!
最终想要的SELECT结果是:“s-11-0004”(因为0001至9999这堆数字之中,1、2、3、5都用了,未用的数字之中,只有4是最少的,所以最后想要的结果是“s-11-0004”)
表2:
s-11-0029
s-11-0030最后想要的结果:s-11-0001
GO
CREATE TABLE TB(
COL1 VARCHAR(20)
)
INSERT INTO TB
SELECT 's-11-0001' UNION ALL
SELECT 's-11-0002' UNION ALL
SELECT 's-11-0003' UNION ALL
SELECT 's-11-0005'SELECT TOP 1 's-11-'+RIGHT('0000'+LTRIM(T2.NUMBER),4)
FROM TB T1
RIGHT JOIN MASTER..SPT_VALUES T2
ON CONVERT(INT,STUFF(COL1,1,5,'') )=T2.NUMBER
WHERE T2.TYPE='P' AND T2.NUMBER BETWEEN 1 AND (
SELECT MAX(CONVERT(INT,STUFF(COL1,1,5,'') ))+1 FROM TB
)AND T1.COL1 IS NULL
ORDER BY T2.NUMBER
--s-11-0004
(
id varchar(10)
)
insert into tb
select 's-11-0001' union all
select 's-11-0002' union all
select 's-11-0003' union all
select 's-11-0005'
SELECT CASE WHEN NOT EXISTS(select 1 from tb where cast(parsename(REPLACE(id,'-','.'),1) as int)=1) THEN 's-11-0001'
ELSE (SELECT 's-11'+right('0000'+ltrim(MIN(cast(parsename(REPLACE(a.id,'-','.'),1) as int)+1)),4) FROM tb A WHERE NOT EXISTS(SELECT 1 FROM tb B WHERE cast(parsename(REPLACE(a.id,'-','.'),1) as int)+1=cast(parsename(REPLACE(b.id,'-','.'),1) as int)))
END
/*
---------------
(无列名)
s-110004
from sys.columns ,sys.columns select right('s-11-0000'+ltrim(min(id))),9) from #tb
where id not in(select cast(right(col,4) as int) n from tb)
ELSE (SELECT 's-11-'+right('0000'+ltrim(MIN(cast(parsename(REPLACE(a.id,'-','.'),1) as int)+1)),4) FROM tb A WHERE NOT EXISTS(SELECT 1 FROM tb B WHERE cast(parsename(REPLACE(a.id,'-','.'),1) as int)+1=cast(parsename(REPLACE(b.id,'-','.'),1) as int)))
END
--感觉一个语句是查不出来的,或者一个语句查的话很繁琐
--建表
create table 表1
(
code varchar(100)
)insert into 表1 select 's-11-0001'
insert into 表1 select 's-11-0002'
insert into 表1 select 's-11-0003'
insert into 表1 select 's-11-0005'--测试语句
--这里写了两句,分为有0001和没有0001的两种情况
--后面必须是4位的,如果's-11-9999'是最后一个,那下一次会算成's-11-0000'declare @c_code varchar(20)
select @c_code = ''if not exists(select 1 from 表1 where code = 's-11-0001')
select @c_code = 's-11-0001'
else
select @c_code = 's-11-' + right('0000' + cast(cast(stuff(min(code),1,5,'') as int)+1 as varchar(10)),4) from 表1 a
where not exists(select 1 from 表1 where cast(stuff(code,1,5,'') as int) = cast(stuff(a.code,1,5,'') as int) +1 )select @c_code--结果
/*
s-11-0004
*/
where 临时表列名 not in (select cast(right(tb.col,4) as int) n from tb )
select 's-11-'+min(col) from 临时表 where col
not in (select replace(表1.col,'s-11-','') from 表1)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 VARCHAR(20)
)
INSERT INTO TB
SELECT 's-11-0001' UNION ALL
SELECT 's-11-0002' UNION ALL
SELECT 's-11-0003' UNION ALL
SELECT 's-11-0005'
;WITH MU AS (
SELECT 1 AS NUM
UNION ALL SELECT NUM+1 FROM MU WHERE NUM<9999
)
SELECT TOP 1 's-11-'+RIGHT('0000'+LTRIM(T2.NUM),4)
FROM TB T1
RIGHT JOIN MU T2
ON CONVERT(INT,STUFF(COL1,1,5,'') )=T2.NUM
WHERE T1.COL1 IS NULL
ORDER BY T2.NUM
OPTION (MAXRECURSION 0)
--s-11-0004