表1
A,B,C,D
aa,ac00001,ac00100,100
aa,ac00501,ac00800,300
aa,ac00401,ac00450,50
cc,af00011,af00500,490
dd,af13001,af14000,1000
dd,af14301,af14500,200
......
表2
A,B,C,D
aa,ac00001,ac01000,1000
cc,af00001,af06000,6000
aa,ac01001,ac02000,1000
cc,af06001,af10000,4000
dd,af12001,af15000,3000
.....
怎么样从表2中找出在表1中没有数据范围。
B:开始的的范围
C:结束的范围
D:是B和C字段后面的数字相减所得
我要得到的结果是:
A,B,C,D
aa,ac00101,ac00401,400
aa,ac00451,ac00500,50
aa,ac00801,ac01000,200
cc,af00001,af00010,10
cc,af00501,af06000,5500
aa,ac01001,ac02000,1000
cc,af06001,af10000,4000
dd,af12001,af13000,1000
dd,af14001,af14300,300
dd,af14501,af15000,500
怎么在SQL中实现啊!
求求各位大大了!!!
A,B,C,D
aa,ac00001,ac00100,100
aa,ac00501,ac00800,300
aa,ac00401,ac00450,50
cc,af00011,af00500,490
dd,af13001,af14000,1000
dd,af14301,af14500,200
......
表2
A,B,C,D
aa,ac00001,ac01000,1000
cc,af00001,af06000,6000
aa,ac01001,ac02000,1000
cc,af06001,af10000,4000
dd,af12001,af15000,3000
.....
怎么样从表2中找出在表1中没有数据范围。
B:开始的的范围
C:结束的范围
D:是B和C字段后面的数字相减所得
我要得到的结果是:
A,B,C,D
aa,ac00101,ac00401,400
aa,ac00451,ac00500,50
aa,ac00801,ac01000,200
cc,af00001,af00010,10
cc,af00501,af06000,5500
aa,ac01001,ac02000,1000
cc,af06001,af10000,4000
dd,af12001,af13000,1000
dd,af14001,af14300,300
dd,af14501,af15000,500
怎么在SQL中实现啊!
求求各位大大了!!!
解决方案 »
- SQLServer 2000 中 EXEC 如何执行某表中的字符串
- 请教ACCESS的时间函数转换为SQL SERVER时间函数。
- 请问这种情况会出现吗,如何避免,最好有代码,万分焦急
- 查看数据库中的相关信息
- SQL小问题,送大分!
- 在线等待。。。。!存储过程中的使用游标排序的问题!!!!!
- 高手请进!!!『怎样将两个库中的表和表结构进行差异比对,并输出结果』---在线等待!『online』
- 特殊问题,请教特殊的处理方法。请各位高手参加。
- 大虾们:我在创建数据库时的不解,希望得到大家的指点。
- 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中
- 根据时间段,查询时间段内每一天的人数
- sql 2000 第一次查询速度很慢,第二次正常
--建立如下函数
create function getnewstr
(@oldstr varchar(100))
returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goselect * from tb where col like('%[^0-9]%')
测试数据create table test (a nvarchar(10) ,b nvarchar(10),c nvarchar(10))
select *from testinsert into test select 'aa','ac00001','ac00100'
insert into test select 'aa','ac00501','ac00800'
insert into test select 'aa','ac00401','ac00450'
insert into test select 'cc','af00011','af00500'
insert into test select 'dd','af13001','af14000'
insert into test select 'dd','af14301','af14500'
create table test2 (a nvarchar(10) ,b nvarchar(10),c nvarchar(10))
insert into test2 select 'aa','ac00001','ac01000'
insert into test2 select 'cc','af00001','af06000'
insert into test2 select 'aa','ac01001','ac02000'
insert into test2 select 'cc','af06001','af10000'
insert into test2 select 'dd','af12001','af15000' insert into test2 select 'dd','af13001','af14000'
insert into test2 select 'dd','af14301','af14500' -----语句-----------
select test2.* from test2 where test2.id
not in (select id From test,test2 where test.a=test2.a and test.b=test2.b and test.c=test2.c)
结果aa ac00001 ac01000 1
cc af00001 af06000 2
aa ac01001 ac02000 3
cc af06001 af10000 4
dd af12001 af15000 5
B,C字段的數字部分,固定是后面5位?
否則需要寫function取出數字部分...
--將就看看吧,結果差不多,效率不是很好
--測試數據
create table t1(a varchar(10),b varchar(10),c varchar(10),d int)
insert into t1 select 'aa','ac00001','ac00100',100
insert into t1 select 'aa','ac00501','ac00800',300
insert into t1 select 'aa','ac00401','ac00450',50
insert into t1 select 'cc','af00011','af00500',490
insert into t1 select 'dd','af13001','af14000',1000
insert into t1 select 'dd','af14301','af14500',200create table t2(a varchar(10),b varchar(10),c varchar(10),d int)
insert into t2 select 'aa','ac00001','ac01000', 1000
insert into t2 select 'cc','af00001','af06000' ,6000
insert into t2 select 'aa','ac01001','ac02000' ,1000
insert into t2 select 'cc','af06001','af10000' ,4000
insert into t2 select 'dd','af12001','af15000' ,3000GOselect top 100000 id=identity(int,1,1) into #
from syscolumns a,syscolumns b,syscolumns cselect D.id,D.a,stuff(D.b,len(D.b)-4,5,right(100000+id,5)) as b,stuff(D.c,len(D.c)-4,5,right(100000+id,5)) as c,0 as d
into #t
from
(
select *
from #
left join t2
on #.id<=convert(int,right(t2.c,5)) and #.id>=convert(int,right(t2.b,5))
where a is not null
) D
left join t1
on D.id<=convert(int,right(t1.c,5)) and D.id>=convert(int,right(t1.b,5)) and D.a=t1.a
where t1.a is nullcreate index temp_ix on #t(a,id)select *, convert(int,right(c,5))-convert(int,right(b,5))+1 as d
from
(select a,min(b) as b,max(c) as c
from
(
select tmp=(select count(*) from #t a where a.a=#t.a and a.id<=#t.id),* from #t
) T
group by a,(id-tmp)) X
order by a
/*
a b c d
------------------------------
aa ac00101 ac00400 300
aa ac00451 ac00500 50
aa ac00801 ac02000 1200 --這條連起來了
cc af00001 af00010 10
cc af00501 af10000 9500 --這條也連了
dd af12001 af13000 1000
dd af14001 af14300 300
dd af14501 af15000 500*/drop table t1,t2
drop table #,#t
比如你的記錄是
aa 000001,000010,10
那就在臨時表里形成
aa,000001
aa,000002
aa,000003
aa,000004
.....
aa,0000010
然后再匹配t1,也全部show出。
比如t1中是
aa ,000003,000008,6
那么結果是
------------------
aa,000001,null
aa,000002,null
aa,000003,000003
aa,000004,000004
aa,000005,000005
aa,000006,000006
aa,000007,000007
aa,000008,000008
aa,000009,null
aa,0000010,null那么,是null的就是你需要的數據了,將連續的min看成b,max看成c,再算d。