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

[usernamegaorn]

现有两个表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语句该怎么写？

[Trance]

呵呵，这个语句不错。。学习

[usernamegaorn]

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语句并没有查找出这条记录来，仅仅查找了在列对应的情况下记录值相等时的记录，所以不够严密，没有查找完！

[昵称被占用了]

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)+'个整数相同'

[usernamegaorn]

怎么没人跟贴呢？请大家发言呀

[昵称被占用了]

方法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)+'个整数相同'

[昵称被占用了]

方法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

[binglengdexin2]

学习..悟识..

[xiaxianshuang]

头痛，看了半天才理解意思。关注

[usernamegaorn]

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

[ORARichard]

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,23

declare @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)+'个整数相同'

[昵称被占用了]

要求变了?
吃饭了,下午再看

[昵称被占用了]

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

[usernamegaorn]

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

[usernamegaorn]

谢

[usernamegaorn]

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

[昵称被占用了]

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