34,593
社区成员
发帖
与我相关
我的任务
分享
create table 表A
(序号 int, A1 int, B1 int, C1 int, D1 int)
insert into 表A
select 1, 1, 3, 5, 7 union all
select 2, 4, 7, 3, 2 union all
select 3, 0, 9, 2, 1 union all
select 4, 1, 2, 3, 4
create table 表B
(日期 varchar(4), A1 int, B1 int, C1 int, D1 int)
insert into 表B
select '06', 2, 3, 5, 7 union all
select '09', 7, 2, 8, 3 union all
select '11', 8, 2, 7, 5 union all
select '12', 3, 1, 6, 5
select distinct a.*
from 表A a
cross join 表B b
where
(case when a.A1 in (b.A1,b.B1,b.C1,b.D1) then 1 else 0 end
+case when a.B1 in (b.A1,b.B1,b.C1,b.D1) then 1 else 0 end
+case when a.C1 in (b.A1,b.B1,b.C1,b.D1) then 1 else 0 end
+case when a.D1 in (b.A1,b.B1,b.C1,b.D1) then 1 else 0 end)
>=3
序号 A1 B1 C1 D1
----------- ----------- ----------- ----------- -----------
1 1 3 5 7
2 4 7 3 2
(2 row(s) affected)