select b.* from b where cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) not in ( select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from A )
CrazyFor(为谁倾狂) 不对!再看看
我觉得用一句SQL是写不出来这个效果的
不一定要一句SQL语句来实现这个功能 也可以用存贮过程等方法来实现
select b.* from b where cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) not in ( select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from A )这句有什么问题,贴出来看看。
分析: 如果有如下数据: id aa bb -------------------------------- 1 1500 2000 //============================== 表b id aa bb 1 1000 2050则应该有两条结果数据: 1000 1500 2000 2050所以查询比较复杂。 另外,表A和表B中是否确定BB大于AA?否则难度又要提高。呵呵
CrazyFor(为谁倾狂)你的查询结果不是一个中间差,而是B表的数据
我的想法是先把上面的这种数据变成两条数据 id aa bb -------------------------------- 1 1500 2000 //============================== 表b id aa bb 1 1000 2000 1 2000 2050这样可能就好分析一点。
比较麻烦,但有点意思。 declare @intida smallint,@intaaa int,@intbba int declare @intaab int,@intbbb int declare @intaa intif exists(select * from tempdb..sysobjects where name like '#tmptable%') drop table #tmptable create table #tmptable (id smallint,aa int,bb int)declare cursora cursor for select * from tablea order by id open cursora fetch next from cursora into @intida,@intaaa,@intbba set @intaa=@intaaa while @@fetch_status=0 begin declare cursorb cursor for select aa,bb from tableb where id=@intida order by aa open cursorb fetch next from cursorb into @intaab,@intbbb while @@fetch_status=0 begin if @intaa<@intaab insert #tmptable values (@intida,@intaa,@intaab-1) set @intaa=@intbbb+1 fetch next from cursorb into @intaab,@intbbb end insert #tmptable values (@intida,@intaa,@intbba) close cursorb fetch next from cursora into @intida,@intaaa,@intbba end close cursora deallocate cursora deallocate cursorb select * from #tmptable
1,建自定义函数: CREATE FUNCTION GetMinValue (@inputId int,@startV int,@endV int) RETURNS int AS BEGIN declare @rV int select @rV=isnull((select top 1 ccc from ( select AA as CCC from b where id=@inputId and aa>@startV union select BB As CCC from b where id=@inputId and BB>@startV ) CC order by cc.ccc),@EndV) return @rv END------------- declare @intida int,@intaaa int,@intbba int,@lastV int,@temV int,@I int,@J int drop table #C select identity(int,1,1) as iid,* into #C from B declare cursora cursor for select * from a order by id open cursora fetch next from cursora into @intida,@intaaa,@intbbawhile @@fetch_status=0 begin if @intaaa>@intbba begin set @temV=@intaaa set @intaaa=@intbba set @intbba=@temV end set @I=@intaaa while @I<@intbba begin select @J=dbo.GetMinValue(@intida,@I,@intbba) if (select count(*) from b where (case when aa<bb then aa else bb end)<=@I+1 and (case when BB<AA then AA else BB end)>=@J-1)=0 begin insert into #C(id,aa,bb)values(@intida,@I+1,@J-1) end select @I=@J end
fetch next from cursora into @intida,@intaaa,@intbba endclose cursora deallocate cursora delete from #C where cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) in ( select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from b)select * from #c order by iid------结果1 1002 1002 1 1051 1099 1 1901 1999
select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from A
)
也可以用存贮过程等方法来实现
select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from A
)这句有什么问题,贴出来看看。
如果有如下数据:
id aa bb
--------------------------------
1 1500 2000
//==============================
表b
id aa bb
1 1000 2050则应该有两条结果数据:
1000 1500
2000 2050所以查询比较复杂。
另外,表A和表B中是否确定BB大于AA?否则难度又要提高。呵呵
id aa bb
--------------------------------
1 1500 2000
//==============================
表b
id aa bb
1 1000 2000
1 2000 2050这样可能就好分析一点。
declare @intida smallint,@intaaa int,@intbba int
declare @intaab int,@intbbb int
declare @intaa intif exists(select * from tempdb..sysobjects where name like '#tmptable%') drop table #tmptable
create table #tmptable (id smallint,aa int,bb int)declare cursora cursor for select * from tablea order by id
open cursora
fetch next from cursora into @intida,@intaaa,@intbba
set @intaa=@intaaa
while @@fetch_status=0
begin
declare cursorb cursor for select aa,bb from tableb where id=@intida order by aa
open cursorb
fetch next from cursorb into @intaab,@intbbb
while @@fetch_status=0
begin
if @intaa<@intaab insert #tmptable values (@intida,@intaa,@intaab-1)
set @intaa=@intbbb+1
fetch next from cursorb into @intaab,@intbbb
end
insert #tmptable values (@intida,@intaa,@intbba)
close cursorb
fetch next from cursora into @intida,@intaaa,@intbba
end
close cursora
deallocate cursora
deallocate cursorb
select * from #tmptable
CREATE FUNCTION GetMinValue (@inputId int,@startV int,@endV int)
RETURNS int AS
BEGIN
declare @rV int
select @rV=isnull((select top 1 ccc from (
select AA as CCC from b where id=@inputId and aa>@startV
union
select BB As CCC from b where id=@inputId and BB>@startV ) CC
order by cc.ccc),@EndV)
return @rv
END-------------
declare @intida int,@intaaa int,@intbba int,@lastV int,@temV int,@I int,@J int
drop table #C
select identity(int,1,1) as iid,* into #C from B
declare cursora cursor for
select * from a order by id
open cursora
fetch next from cursora into @intida,@intaaa,@intbbawhile @@fetch_status=0
begin
if @intaaa>@intbba
begin
set @temV=@intaaa
set @intaaa=@intbba
set @intbba=@temV
end
set @I=@intaaa
while @I<@intbba
begin
select @J=dbo.GetMinValue(@intida,@I,@intbba)
if (select count(*) from b where (case when aa<bb then aa else bb end)<=@I+1 and (case when BB<AA then AA else BB end)>=@J-1)=0
begin
insert into #C(id,aa,bb)values(@intida,@I+1,@J-1)
end
select @I=@J
end
fetch next from cursora into @intida,@intaaa,@intbba
endclose cursora
deallocate cursora
delete from #C where cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) in (
select cast(id as varchar)+','+cast(aa as varchar)+','+cast(bb as varchar) from b)select * from #c order by iid------结果1 1002 1002
1 1051 1099
1 1901 1999