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

2009-09-29 02:44:33 257 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

CTFshow-pwn入门-前置基础pwn32-pwn34 CTFshow-pwn入门-前置基础pwn32-pwn34 阅读详情

有一表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

...全文

知识图谱实战：从零搭建一个企业级金融风控图谱（附完整代码）本文提供了一份从零构建企业级金融风控知识图谱的实战指南。通过使用Neo4j图数据库和Python，手把手教你整合多源数据、设计数据模型，并利用Cypher查询与图算法挖掘隐藏的关联风险与欺诈网络，附有完整代码，助力金融科技从业者实现从数据孤岛到全局风险洞察的升级。阅读详情

kevinnick

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

fire_19 2009-09-29

打赏
举报

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

--小F-- 2009-09-29

打赏
举报

[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

soft_wsx 2009-09-29

打赏
举报

5楼！

kevinnick 2009-09-29

打赏
举报

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

yander 2009-09-29

打赏
举报

[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
这条语句附件出错？？？？

--小F-- 2009-09-29

打赏
举报

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

chuifengde 2009-09-29

打赏
举报

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

soft_wsx 2009-09-29

打赏
举报

/*

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

*/

华夏小卒 2009-09-29

打赏
举报



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

bancxc 2009-09-29

打赏
举报



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

*/

--小F-- 2009-09-29

打赏
举报

----------------------------------------------------------------

-- 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 行受影响)



*/

--小F-- 2009-09-29

打赏
举报

先截取数据出来

然后

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

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

相关推荐