不太明白,大概写一下: select n.e,n.f,n.g from X,Y m,Y n where X.e=m.e and X.f=m.f and X.g=m.g and (case when m.a=n.a then 1 else 0 end)+ (case when m.b=n.b then 1 else 0 end)+ (case when m.c=n.c then 1 else 0 end)+ (case when m.d=n.d then 1 else 0 end)<3
declare @X table(e int,f int,g int) insert into @X select 4,5,6 --X表中的记录declare @Y table(e int,f int,g int,a int,b int,c int,d int) insert into @Y select 4,5,6,0,1,2,3 --Y表的唯一记录 insert into @Y select 4,5,8,0,0,2,4 --该记录中有2个字段与本表唯一记录的a,b,c,d字段相同,符合条件 insert into @Y select 4,5,9,0,2,2,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件 insert into @Y select 4,6,6,0,1,3,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件 insert into @Y select 4,7,6,0,0,0,0 --该记录中有1个字段与本表唯一记录的a,b,c,d字段相同,符合条件 insert into @Y select 4,8,6,4,3,2,1 --该记录中有0个字段与本表唯一记录的a,b,c,d字段相同,符合条件select n.e,n.f,n.g from @X t,@Y m,@Y n where t.e=m.e and t.f=m.f and t.g=m.g and (case when m.a=n.a then 1 else 0 end)+ (case when m.b=n.b then 1 else 0 end)+ (case when m.c=n.c then 1 else 0 end)+ (case when m.d=n.d then 1 else 0 end)<3/* e f g ----------- ----------- ----------- 4 5 8 4 7 6 4 8 6 */
你这个能等于吗? e=(select e from Z) and f=(select f from Z) and g=(select g from Z) 找出来的是集合的话肯定会出错啊
感谢大虾libin_ftsafe,pl_mm ,我想要的就是二楼的那种结果。我还想知道一楼说的select top 1是加在哪个地方,大虾们能再指点一下吗?明天结贴。
declare @a int ,@b int,@c int,@d intselect @a = a,@b = b,@c= c ,@d = d from y a join x b on a.e = b.e and a.f = b.f and a.g = b.gselect t.e,t.f,t.g from ( select * ,con = case when a = @a then 1 else 0 end + case when b = @b then 1 else 0 end + case when c = @c then 1 else 0 end + case when d = @d then 1 else 0 end from y ) t where con < 3
create table #1 (e int,f int,g int) insert into #1 select 4,5,6 --X表中的记录create table #2 (e int,f int,g int,a int,b int,c int,d int) insert into #2 select 4,5,6,0,1,2,3 --Y表的唯一记录 insert into #2 select 4,5,8,0,0,2,4 --该记录中有2个字段与本表唯一记录的a,b,c,d字段相同,符合条件 insert into #2 select 4,5,9,0,2,2,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件 insert into #2 select 4,6,6,0,1,3,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件 insert into #2 select 4,7,6,0,0,0,0 --该记录中有1个字段与本表唯一记录的a,b,c,d字段相同,符合条件 insert into #2 select 4,8,6,4,3,2,1 --该记录中有0个字段与本表唯一记录的a,b,c,d字段相同,符合条件select e,f,g from ( select n.e,n.f,n.g ,rn = case when b.a = n.a then 1 else 0 end + case when b.b = n.b then 1 else 0 end + case when b.c = n.c then 1 else 0 end + case when b.d = n.d then 1 else 0 end from #1 a join #2 b on a.e = b.e and a.f = b.f and a.g = b.g ,#2 n ) t where rn < 3
e f g ----------- ----------- ----------- 4 5 8 4 7 6 4 8 6(3 行受影响)不过还是2楼的比较精炼!学习!
select
n.e,n.f,n.g
from
X,Y m,Y n
where
X.e=m.e and X.f=m.f and X.g=m.g
and
(case when m.a=n.a then 1 else 0 end)+
(case when m.b=n.b then 1 else 0 end)+
(case when m.c=n.c then 1 else 0 end)+
(case when m.d=n.d then 1 else 0 end)<3
declare @X table(e int,f int,g int)
insert into @X select 4,5,6 --X表中的记录declare @Y table(e int,f int,g int,a int,b int,c int,d int)
insert into @Y select 4,5,6,0,1,2,3 --Y表的唯一记录
insert into @Y select 4,5,8,0,0,2,4 --该记录中有2个字段与本表唯一记录的a,b,c,d字段相同,符合条件
insert into @Y select 4,5,9,0,2,2,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件
insert into @Y select 4,6,6,0,1,3,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件
insert into @Y select 4,7,6,0,0,0,0 --该记录中有1个字段与本表唯一记录的a,b,c,d字段相同,符合条件
insert into @Y select 4,8,6,4,3,2,1 --该记录中有0个字段与本表唯一记录的a,b,c,d字段相同,符合条件select
n.e,n.f,n.g
from
@X t,@Y m,@Y n
where
t.e=m.e and t.f=m.f and t.g=m.g
and
(case when m.a=n.a then 1 else 0 end)+
(case when m.b=n.b then 1 else 0 end)+
(case when m.c=n.c then 1 else 0 end)+
(case when m.d=n.d then 1 else 0 end)<3/*
e f g
----------- ----------- -----------
4 5 8
4 7 6
4 8 6
*/
e=(select e from Z) and f=(select f from Z) and g=(select g from Z)
找出来的是集合的话肯定会出错啊
declare @a int ,@b int,@c int,@d intselect @a = a,@b = b,@c= c ,@d = d
from y a
join x b on a.e = b.e and a.f = b.f and a.g = b.gselect t.e,t.f,t.g from (
select *
,con = case when a = @a then 1 else 0 end
+ case when b = @b then 1 else 0 end
+ case when c = @c then 1 else 0 end
+ case when d = @d then 1 else 0 end
from y ) t
where con < 3
create table #1 (e int,f int,g int)
insert into #1 select 4,5,6 --X表中的记录create table #2 (e int,f int,g int,a int,b int,c int,d int)
insert into #2 select 4,5,6,0,1,2,3 --Y表的唯一记录
insert into #2 select 4,5,8,0,0,2,4 --该记录中有2个字段与本表唯一记录的a,b,c,d字段相同,符合条件
insert into #2 select 4,5,9,0,2,2,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件
insert into #2 select 4,6,6,0,1,3,3 --该记录中有3个字段与本表唯一记录的a,b,c,d字段相同,不符合条件
insert into #2 select 4,7,6,0,0,0,0 --该记录中有1个字段与本表唯一记录的a,b,c,d字段相同,符合条件
insert into #2 select 4,8,6,4,3,2,1 --该记录中有0个字段与本表唯一记录的a,b,c,d字段相同,符合条件select e,f,g from
(
select n.e,n.f,n.g
,rn = case when b.a = n.a then 1 else 0 end
+ case when b.b = n.b then 1 else 0 end
+ case when b.c = n.c then 1 else 0 end
+ case when b.d = n.d then 1 else 0 end
from
#1 a join #2 b on a.e = b.e and a.f = b.f and a.g = b.g
,#2 n ) t
where rn < 3
e f g
----------- ----------- -----------
4 5 8
4 7 6
4 8 6(3 行受影响)不过还是2楼的比较精炼!学习!