超难题：两表对比后，检索出符合条件的记录数量

Question

现有两个表Table_A和Table_B，它们的结构完全相同，但记录值不同,记录总数各有上千条且两者数目不等。Table_A的结构如下：
┌─────┬───-┬───-┬───-┬───-┬───-┬-───┐
│  序  号  │Field_1│Field_2│Field_3│Field_4│Field_5│Field_6│ 
│varchar(4)│ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N003   │  31   │  45   │  12   │  89   │  50   │  66   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N004   │  24   │  35   │  10   │  72   │  12   │  23   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N005   │  24   │  89   │  23   │  11   │  35   │  31   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N006   │  24   │  89   │  23   │  11   │  35   │  90   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
.................Table_B的结构如下：
┌─────┬───-┬───-┬───-┬───-┬───-┬-───┐
│  序  号  │Field_1│Field_2│Field_3│Field_4│Field_5│Field_6│ 
│varchar(4)│ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N003   │  31   │  46   │  14   │  79   │  55   │  16   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N004   │  22   │  15   │  40   │  72   │  12   │  53   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N006   │  24   │  22   │  23   │  55   │  15   │  90   │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
.................现已知：Table_B的序号取自Table_A的一部分而非全部，且两者的序号都是主键。
要求：以Table_A作为参照表，在Table_B中检索满足以下条件的记录个数：
  ①.当两表的序号相同时，Table_B该行中Field_1到Field_6中六个字段值（整形数），有且仅有1个整数与参照表（Table_A）的六个值之一相同；
  ②.当两表的序号相同时，Table_B该行中Field_1到Field_6中六个字段值，有且仅有2个整数与参照表（Table_A）的六个值之二相同；
  ③.当两表的序号相同时，Table_B该行中Field_1到Field_6中六个字段值，有且仅有3个整数与参照表（Table_A）的六个值之三相同；
  ④.当两表的序号相同时，Table_B该行中Field_1到Field_6中六个字段值，有且仅有4个整数与参照表（Table_A）的六个值之四相同；
  ⑤.当两表的序号相同时，Table_B该行中Field_1到Field_6中六个字段值，有且仅有5个整数与参照表（Table_A）的六个值之五相同；
  ⑥.当两表的序号相同时，两表的Field字段六个值全相同（列顺序可以相同也可以不同）
  ⑦.检索结果形式如下：
┌──────────┬───────┐
│      类    别      │   记录个数   │
├──────────┼───────│
│有且仅有1个整数相同 │    378       │
├──────────┼───────│
│有且仅有2个整数相同 │     80       │
├──────────┼───────│
│有且仅有3个整数相同 │    780       │
├──────────┼───────│
│有且仅有4个整数相同 │     37       │
├──────────┼───────│
│有且仅有5个整数相同 │     78       │
├──────────┼───────│
│   6个整数全相同    │     70       │
├──────────┼───────│大家快帮帮忙吧！这样的SQL语句该怎么写？

Answer 1

select
'有且仅有'+cast(
case when a.Field_1=b.Field_1 then 1 else 0 end+
case when a.Field_2=b.Field_2 then 1 else 0 end+
case when a.Field_3=b.Field_3 then 1 else 0 end+
case when a.Field_4=b.Field_4 then 1 else 0 end+
case when a.Field_5=b.Field_5 then 1 else 0 end+
case when a.Field_6=b.Field_6 then 1 else 0 end
as varchar)+'个整数相同' as [类    别],
count(*) as 记录个数
from table_a a inner join table_b b
on a.序号=b.序号
group by '有且仅有'+cast(
case when a.Field_1=b.Field_1 then 1 else 0 end+
case when a.Field_2=b.Field_2 then 1 else 0 end+
case when a.Field_3=b.Field_3 then 1 else 0 end+
case when a.Field_4=b.Field_4 then 1 else 0 end+
case when a.Field_5=b.Field_5 then 1 else 0 end+
case when a.Field_6=b.Field_6 then 1 else 0 end
as varchar)+'个整数相同'

Answer 2

Haiwer(海阔天空) ，你好：
    你的sql语句并不严密。请看题中条件①的后半句：有且仅有1个整数与参照表（Table_A）的六个值之一相同，这里并没有要求列必须对应起来，只要有一个整数相同就满足条件，如下：Table_A：
┌─────┬───-┬───-┬───-┬───-┬───-┬-───┐
│  序  号  │Field_1│Field_2│Field_3│Field_4│Field_5│Field_6│ 
│varchar(4)│ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N010   │  17   │ ××  │ ××  │ ××  │ ××  │××  │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│Table_B：
┌─────┬───-┬───-┬───-┬───-┬───-┬-───┐
│  序  号  │Field_1│Field_2│Field_3│Field_4│Field_5│Field_6│ 
│varchar(4)│ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │ (Int) │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│
│   N010   │ ××  │  17   │ ××  │ ××  │ ××  │ ××  │
├─────┼───-┼───-┼───-┼───-┼───-┼───-│Table_B中序号为N010这条记录中有一列值为17，Table_A中序号为N010这条记录中也有一列值为17，虽然它们不在同一个字段（即不要求列必须对应），但这也满足条件①。你的sql语句并没有查找出这条记录来，仅仅查找了在列对应的情况下记录值相等时的记录，所以不够严密，没有查找完！

Answer 3

Haiwer(海阔天空)
Where are you now?
Help,help!

Answer 4

declare @t1 table(id varchar(4),f1 int,f2 int,f3 int)
insert into @t1
select 'N003',31,45,12 union
select 'N004',24,35,10 union
select 'N005',24,89,23 union
select 'N006',24,89,23declare @t2 table(id varchar(4),f1 int,f2 int,f3 int)
insert into @t2
select 'N003',31,46,14 union
select 'N004',22,15,40 union
select 'N006',24,22,23
select '有且仅有'+str(n)+'个整数相同' [类    别],count(id) from
(
select id, sum(f) n from
(
select b.id,case when b.f1=a.f1 or b.f1=a.f2 or b.f1=a.f3 then 1 else 0 end f from @t1 a right join @t2 b on a.id=b.id union all
select b.id,case when b.f2=a.f1 or b.f2=a.f2 or b.f2=a.f3 then 1 else 0 end f from @t1 a right join @t2 b on a.id=b.id union all
select b.id,case when b.f3=a.f1 or b.f3=a.f2 or b.f3=a.f3 then 1 else 0 end f from @t1 a right join @t2 b on a.id=b.id
) t
group by id 
) tb
group by '有且仅有'+str(n)+'个整数相同'

Answer 5

那应该要求两表的
Field_1│Field_2│Field_3│Field_4│Field_5│Field_6│
同一行数据里没有相同的吧？

Answer 6

方法1（因为效率最高，虽然写法比较难看，还是最早推荐）：
select
'有且仅有'+cast(
case when a.Field_1=b.Field_1 or a.Field_1=b.Field_2 or a.Field_1=b.Field_3 or a.Field_1=b.Field_4 or a.Field_1=b.Field_5 or a.Field_1=b.Field_6 then 1 else 0 end+
case when a.Field_2=b.Field_1 or a.Field_2=b.Field_2 or a.Field_2=b.Field_3 or a.Field_2=b.Field_4 or a.Field_2=b.Field_5 or a.Field_2=b.Field_6 then 1 else 0 end+
case when a.Field_3=b.Field_1 or a.Field_3=b.Field_2 or a.Field_3=b.Field_3 or a.Field_3=b.Field_4 or a.Field_3=b.Field_5 or a.Field_3=b.Field_6 then 1 else 0 end+
case when a.Field_4=b.Field_1 or a.Field_4=b.Field_2 or a.Field_4=b.Field_3 or a.Field_4=b.Field_4 or a.Field_4=b.Field_5 or a.Field_4=b.Field_6 then 1 else 0 end+
case when a.Field_5=b.Field_1 or a.Field_5=b.Field_2 or a.Field_5=b.Field_3 or a.Field_5=b.Field_4 or a.Field_5=b.Field_5 or a.Field_5=b.Field_6 then 1 else 0 end+
case when a.Field_6=b.Field_1 or a.Field_6=b.Field_2 or a.Field_6=b.Field_3 or a.Field_6=b.Field_4 or a.Field_6=b.Field_5 or a.Field_6=b.Field_6 then 1 else 0 end
as varchar)+'个整数相同' as [类    别],
count(*) as 记录个数
from table_a a inner join table_b b
on a.序号=b.序号
group by '有且仅有'+cast(
case when a.Field_1=b.Field_1 or a.Field_1=b.Field_2 or a.Field_1=b.Field_3 or a.Field_1=b.Field_4 or a.Field_1=b.Field_5 or a.Field_1=b.Field_6 then 1 else 0 end+
case when a.Field_2=b.Field_1 or a.Field_2=b.Field_2 or a.Field_2=b.Field_3 or a.Field_2=b.Field_4 or a.Field_2=b.Field_5 or a.Field_2=b.Field_6 then 1 else 0 end+
case when a.Field_3=b.Field_1 or a.Field_3=b.Field_2 or a.Field_3=b.Field_3 or a.Field_3=b.Field_4 or a.Field_3=b.Field_5 or a.Field_3=b.Field_6 then 1 else 0 end+
case when a.Field_4=b.Field_1 or a.Field_4=b.Field_2 or a.Field_4=b.Field_3 or a.Field_4=b.Field_4 or a.Field_4=b.Field_5 or a.Field_4=b.Field_6 then 1 else 0 end+
case when a.Field_5=b.Field_1 or a.Field_5=b.Field_2 or a.Field_5=b.Field_3 or a.Field_5=b.Field_4 or a.Field_5=b.Field_5 or a.Field_5=b.Field_6 then 1 else 0 end+
case when a.Field_6=b.Field_1 or a.Field_6=b.Field_2 or a.Field_6=b.Field_3 or a.Field_6=b.Field_4 or a.Field_6=b.Field_5 or a.Field_6=b.Field_6 then 1 else 0 end
as varchar)+'个整数相同'

Answer 7

方法2:
select 
'有且仅有'+cast(cnt as varchar)+'个整数相同' as [类    别],
count(*) as 记录个数
from (
select a.序号,count(*) as cnt
from (
select 序号,Field_1 as 数据
from table_a
union all
select 序号,Field_2 as 数据
from table_a
union all
select 序号,Field_3 as 数据
from table_a
union all
select 序号,Field_4 as 数据
from table_a
union all
select 序号,Field_5 as 数据
from table_a
union all
select 序号,Field_6 as 数据
from table_a
) as a,(
select 序号,Field_1 as 数据
from table_b
union all
select 序号,Field_2 as 数据
from table_b
union all
select 序号,Field_3 as 数据
from table_b
union all
select 序号,Field_4 as 数据
from table_b
union all
select 序号,Field_5 as 数据
from table_b
union all
select 序号,Field_6 as 数据
from table_b
) as b
where a.序号=b.序号 and a.数据=b.数据
group by a.序号
) as t
group by cnt

Answer 8

Haiwer(海阔天空) :
正如你所问的，同一行记录里6个字段值皆不相同！

Answer 9

我是楼主，我还想继续问：Table_A和Table_B如题，同一行记录里6个字段值皆不相同。现在我想在Table_B中检索出满足如下条件的记录行来：当两表的序号相同时，Table_B该行中有且仅有2个字段值与Table_A的任意2个字段值相同，同样不要求字段必须对应。
    原题是分类统计个数，现在我是要检索出如上条件的记录！看清了呀！
    还请各位继续关注呀，尤其是Haiwer(海阔天空) ！

Answer 10

select
*                       --*显示出table_a，table_b所有字段，你可以选择一些字段
from table_a a inner join table_b b
on a.序号=b.序号
where case when a.Field_1=b.Field_1 or a.Field_1=b.Field_2 or a.Field_1=b.Field_3 or a.Field_1=b.Field_4 or a.Field_1=b.Field_5 or a.Field_1=b.Field_6 then 1 else 0 end+
case when a.Field_2=b.Field_1 or a.Field_2=b.Field_2 or a.Field_2=b.Field_3 or a.Field_2=b.Field_4 or a.Field_2=b.Field_5 or a.Field_2=b.Field_6 then 1 else 0 end+
case when a.Field_3=b.Field_1 or a.Field_3=b.Field_2 or a.Field_3=b.Field_3 or a.Field_3=b.Field_4 or a.Field_3=b.Field_5 or a.Field_3=b.Field_6 then 1 else 0 end+
case when a.Field_4=b.Field_1 or a.Field_4=b.Field_2 or a.Field_4=b.Field_3 or a.Field_4=b.Field_4 or a.Field_4=b.Field_5 or a.Field_4=b.Field_6 then 1 else 0 end+
case when a.Field_5=b.Field_1 or a.Field_5=b.Field_2 or a.Field_5=b.Field_3 or a.Field_5=b.Field_4 or a.Field_5=b.Field_5 or a.Field_5=b.Field_6 then 1 else 0 end+
case when a.Field_6=b.Field_1 or a.Field_6=b.Field_2 or a.Field_6=b.Field_3 or a.Field_6=b.Field_4 or a.Field_6=b.Field_5 or a.Field_6=b.Field_6 then 1 else 0 end
=2