Table :DbTable
====================
ID bigint
R1 int
R2 int
R3 int
R4 int
R5 int
R6 int
B7 int
eg:
R1 R2 R3 R4 R5 R6
1 2 3 21 27 33
2 3 4 21 27 33
......................
......................
Table : RedboxGrp3
ID bigint
R1 int
R2 int
R3 integ :
R1 R2 R3
1 2 3求一条SQL 统计 在第二张表中 每一行数据中三个数据 在Dbtable中出现的次数谢谢
Dbtable
R1 R2 R3 R4 R5 R6
1 2 3 21 27 33
2 3 4 21 27 33
......................
......................如果RedboxGrp3中有如下数据,上面的语句就统计不到
4 , 21 , 27
from RedboxGrp3 A join DbTable B
on A.R1 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6)
and A.R2 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6)
and A.R3 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6)这样应该可以,但效率不高..
select count(r2) as R2数量 from
(select top 1 R2 from redboxgrp3 where R2 in (select R1 from dbtable) union all
select top 1 R2 from redboxgrp3 where R2 in (select R2 from dbtable) union all
select top 1 R2 from redboxgrp3 where R2 in (select R3 from dbtable) union all
select top 1 R2 from redboxgrp3 where R2 in (select R4 from dbtable) union all
select top 1 R2 from redboxgrp3 where R2 in (select R5 from dbtable) union all
select top 1 R2 from redboxgrp3 where R2 in (select R6 from dbtable)
) as temp
瞎搞了,第二行就用分页里面的筛选
insert @T1
select 1,2,3,21,27,33 union all
select 2,3,4,21,27,33declare @T2 table(R1 int,R2 int,R3 int)
insert @T2
select 1,2,3select R1=count(B.R1),R2=count(C.R2),R3=count(D.R3)
from
(
select R1 from @T1 union all
select R2 from @T1 union all
select R3 from @T1 union all
select R4 from @T1 union all
select R5 from @T1 union all
select R6 from @T1
) A
left Join @T2 B on A.R1=B.R1
left join @T2 C on A.R1=C.R2
left join @T2 D on A.R1=D.R3
R1 R2 R3
----------- ----------- -----------
1 2 2(1 行受影响)
insert @T1
select 1,2,3,21,27,33 union all
select 2,3,4,21,27,33declare @T2 table(R1 int,R2 int,R3 int)
insert @T2
select 1,2,3;
with cte1 as
(
select * from @t1
unpivot
(
a1 for RN in(R1,R2,R3,R4,R5,R6)
)un
)
select R1 R,count(R1) num from cte1 c1 join @t2 t2 on c1.a1=t2.R1 group by R1
union all
select R2,count(R2) from cte1 c1 join @t2 t2 on c1.a1=t2.R2 group by R2
union all
select R3,count(R3) from cte1 c1 join @t2 t2 on c1.a1=t2.R3 group by R3
/* 统计 在第二张表中 每一行数据中三个数据 在@T1中出现的次数
R num
----------- -----------
1 1
2 2
3 2(3 行受影响)
*/