假设表1中有1列A,为字符型
A
A000001
A000002
A000005
A000006
A000009我希望得出没有使用的最小值,例如上述例子,结果为A000003,即1、2都用过了,3没有用过。
假设1到9都用过了,就得出A000010,这个最简单的方法是什么?
A
A000001
A000002
A000005
A000006
A000009我希望得出没有使用的最小值,例如上述例子,结果为A000003,即1、2都用过了,3没有用过。
假设1到9都用过了,就得出A000010,这个最简单的方法是什么?
然后和这张表left join
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([A] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A000001' UNION ALL
SELECT 'A000002' UNION ALL
SELECT 'A000005' UNION ALL
SELECT 'A000006' UNION ALL
SELECT 'A000009'--SELECT * FROM [tb]-->SQL查询如下:
SELECT 'A'+RIGHT(10000001+ISNULL(MIN(RIGHT(A.A,6)),0),6) AS MINID
FROM tb a
LEFT JOIN tb b
ON RIGHT(A.A,6)=RIGHT(b.A,6)-1
WHERE b.A IS NULL
/*
MINID
-------------
A000003(1 行受影响)
*/
from #tab a
where
not exists(select * from #tab b where substring(a.txt,1,6)=substring(b.txt ,1,6) and cast(right(a.txt,1) as int)+1=cast(right(b.txt,1) as int) )
order by txt
2楼,你的方法似乎有问题。因为还有以下情况:
A000003
A000004
A000006
这时我希望得到A000001
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([A] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A000002' UNION ALL
SELECT 'A000004' UNION ALL
SELECT 'A000005' UNION ALL
SELECT 'A000006' UNION ALL
SELECT 'A000009'--SELECT * FROM [tb]-->SQL查询如下:
SELECT CASE WHEN NOT EXISTS(SELECT 1 FROM tb WHERE A='A000001') THEN 'A000001'
ELSE 'A'+RIGHT(10000001+ISNULL(MIN(RIGHT(A.A,6)),0),6)
END AS MINID
FROM tb a
LEFT JOIN tb b
ON RIGHT(A.A,6)=RIGHT(b.A,6)-1
WHERE b.A IS NULL
/*
MINID
-------------
A000001(1 行受影响)
*/SORRY,没考虑到
INSERT #temp
select 'A000001' union all
select 'A000002' union all
select 'A000005' union all
select 'A000006' union all
select 'A000009'
GO
--SQL:
SELECT TOP(1) [no] = 'A' + RIGHT('000000'+RTRIM(rowno), 6)
FROM (SELECT rowno = ROW_NUMBER() OVER(ORDER BY col), * FROM #temp) T
WHERE rowno <> CAST(STUFF(col, 1,1,'') AS INT)
/*
no
A000003
*/
create table #tb(A nvarchar(10))
insert #tb
select 'A000001' union all
select 'A000002' union all
select 'A000003' union all
select 'A000004' union all
select 'A000005' union all
select 'A000006' union all
select 'A000007' union all
select 'A000008' union all
select 'A000009' union all
select 'A000010' union all
select 'A000012'declare @T table(num int)
insert @T select Row_Number() over(order by A) from #tb
select top 1 'A'+substring('00000',1,6-len(ltrim(t1.num)))+ltrim(t1.num) from @t as t1
left join #tb as t2 on cast(stuff(t2.A,1,1,'') as int)=t1.num
where t2.A is null order by t1.num
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([A] [nvarchar](10))
INSERT INTO [tb]
--SELECT 'A000001' UNION ALL
SELECT 'A000002' UNION ALL
SELECT 'A000005' UNION ALL
SELECT 'A000006' UNION ALL
SELECT 'A000009'--SELECT * FROM [tb]-->SQL查询如下:
SELECT case when exists(select 1 from tb where A = 'A000001' ) then 'A'+RIGHT(10000001+ISNULL(MIN(RIGHT(a.A,6)),0),6) else 'A000001' end AS MINID
FROM tb a
LEFT JOIN tb b
ON RIGHT(a.A,6)=RIGHT(b.A,6)-1
WHERE b.A IS NULL
/*
MINID
-------------
A000001(1 行受影响)
*/