高手帮忙，查询写了一半，下面的不会了

[kevinnick]

有一表oitm,有一列的数据为：fcc090101a,fcc090102a,fcc090103a,fcc090106a,fcc090107a,fcc090110a.....
现在要把数据分离为 101，102，103，106，107，110.....
然后列出其中缺少的数字 104，105，108，109


请问这个查询要如何做呢？

我的想法是把 101，102，103，106，107，110等分离出来写到表 #tt里面，
然后生成另一个临时表#tst，数据为 101，102，103，104，105，106，107，108，109，110
然后比较，其中缺少的就是了，但是我写了一半，下面的不会写了，高手帮忙。。。。

if OBJECT_ID('tempdb..#tt') is not NULL
drop table #tt

select distinct convert(int,SUBSTRING(ItemCode,7,3)) as substr
into #tt
from OITM
where ItemCode between 'fcc090101a' and 'fcc091010a'

declare @start int
declare @end int
select @start = min(substr),@end = max(substr)
from #tt

if OBJECT_ID('tempdb..#tst') is not NULL
drop table #tst

[--小F--]

先截取数据出来
然后
select number
from tb p right join (select   number from master..spt_values where type='p' and number between 101 and 110) k
on p.COL1=k.number
where COL1 is null

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-29 14:54:34
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(10))
insert [tb]
select 'fcc090101a' union all
select 'fcc090102a' union all
select 'fcc090103a' union all
select 'fcc090106a' union all
select 'fcc090107a' union all
select 'fcc090110a'
--------------开始查询--------------------------
;with f as
(
select substring(col,7,3) as col  from [tb]
)
select 
  number
from 
  f p 
right join 
(select   number from master..spt_values where type='p' and number between 101 and 110) k
on 
  p.COL=k.number
where 
  COL is null
----------------结果----------------------------
/* number
-----------
104
105
108
109

(4 行受影响)

*/

[bancxc]

Select A.Number
from 
(Select number from master..spt_values  where type='P' and number between 101 and 110)
A left join
(select distinct convert(int,SUBSTRING(ItemCode,7,3)) as substr from OITM where ItemCode between 'fcc090101a' and 'fcc090110a' ) B on A.number = B.substr
where B.substr is null


/*
Number      
----------- 
104
105
108
109
*/

[华夏小卒]

if object_ID('ta') IS NOT NULL DROP TABLE ta
go
create table  ta(col varchar(20) )
go  
insert ta select
'fcc090101a' union all select 
'fcc090102a' union all select 
'fcc090103a' union all select 
'fcc090106a' union all select 
'fcc090107a' union all select 
'fcc090110a'


select cast(substring(col,7,3)as int) as num into #t from ta

select top (select max(num) from #t) id=identity(int,1,1) into #s from sysobjects a ,sysobjects b


select id from #s where id not in(select num from #t)
and id>(select min(num) from #t)

(110 行受影响)
id
-----------
104
105
108
109

(4 行受影响)

drop table #t
drop table #s

[soft_wsx]

/*
Micsosoft Windows 7.0 7600
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 
小弟愿和大家共同进步、共同学习！
    如有雷同、实属巧合
●●●●●2009-09-29 14:06:47.163●●●●●
★★★★★soft_wsx★★★★★
*/
if object_id('ta')is not null drop table ta
go
CREATE TABLE ta(id int identity(1,1) primary key,name nvarchar(20))
INSERT ta(name) 
select 'fcc090101a'
 union all select 'fcc090102a'
  union all select 'fcc090103a'
   union all select 'fcc090106a'
    union all select 'fcc090107a'
     union all select 'fcc090110a'
     
select SUBSTRING(name,7,3) as name into #a from ta


select a.name from #a a left join #a b on a.name=b.name

select a.name
from(select 101+number  as name
       from master..spt_values
         where type='P' and number<10
     )a where not exists(select 1 from #a where name=a.name)
/*
name
104
105
108
109
*/

[chuifengde]

CREATE TABLE t1(a VARCHAR(1000),fl VARCHAR(1000),qs VARCHAR(1000))
INSERT t1 SELECT 'fcc090101a,fcc090102a,fcc090103a,fcc090106a,fcc090107a,fcc090110a' ,NULL,null

UPDATE  t1 SET fl=REPLACE(REPLACE(a+',','fcc090',''),'a,',',')
DECLARE @sql VARCHAR(1000)
DECLARE @i INT ,@min VARCHAR(4),@max VARCHAR(4)
SELECT @min=LEFT(fl,3),@max=left(RIGHT(fl,4),3), @i=@max*1-@min*1+1 FROM t1
 
IF OBJECT_ID('t2') IS NOT NULL 
	DROP TABLE t2
EXEC('create table t2(id int identity('+@min+',1),b int)')

SET ROWCOUNT @i
INSERT t2 SELECT 0 FROM syscolumns s
SET ROWCOUNT 0

SELECT @sql=ISNULL(@sql+',','')+LTRIM(id) FROM t2 ,t1 WHERE CHARINDEX(LTRIM(ID),a)=0
UPDATE t1 SET  qs=@sql

SELECT * FROM t1

[--小F--]

学习鸟的这个做法 鸟的这个方法是针对
'fcc090101a,fcc090102a,fcc090103a,fcc090106a,fcc090107a,fcc090110a' 这样排列来说的

[yander]

[Quote=引用 3 楼 bancxc 的回复:]
SQL codeSelect A.Numberfrom
(Selectnumberfrom master..spt_valueswhere type='P'andnumberbetween101and110)
Aleftjoin
(selectdistinctconvert(int,SUBSTRING(ItemCode,7,3))as substrfrom OITMwhere ItemCodebetween'fcc090101a'and'fcc090110a' ) Bon A.number= B.substrwhere B.substrisnull/*
Number
-----------
104
105
108
109*/
[/Quote]

select top (select max(num) from #t) id=identity(int,1,1) into #s from sysobjects a ,sysobjects b
这条语句附件出错？？？？

[kevinnick]

我的2000的数据库，好像不支持WITH语法。。

[soft_wsx]

5楼！

[--小F--]

[Quote=引用 9 楼 kevinnick 的回复:]
我的2000的数据库，好像不支持WITH语法。。
[/Quote]

select 
  number
from 
  (select substring(col,7,3) as col  from [tb]) p 
right join 
(select   number from master..spt_values where type='p' and number between 101 and 110) k
on 
  p.COL=k.number
where 
  COL is null

[fire_19]

if object_id(temp..#tt) is not null
drop table #tt
Create table oitm
(volues varchar(20))
insert into oitm select 'fcc090101a' union select 'fcc090102a' union select 'fcc090103a' union select 'fcc090106a' union select 'fcc090107a' union select 'fcc090110a'
select substring(volues,7,3) as volues from oitm
select number from master..spt_values where type = 'P' and number between 101 and 110
select A.number from (select number from master..spt_values where type = 'P' and number between 101 and 110) A left join (select substring(volues,7,3) as volues from oitm) B ON A.Number = B.Volues where B.volues is null