比如一个表有两行数据,字段名称:
A B C D E F G H
1 1 2 3 1 1 1 2
2 1 2 1 1 2 2 1在这两行数据里,B,C,E,这三个字段的内容相同,所以查询出来COUNT是三,那么如何通过查询可以得出这两行数据有三个字段的内容是相同的呢?
A B C D E F G H
1 1 2 3 1 1 1 2
2 1 2 1 1 2 2 1在这两行数据里,B,C,E,这三个字段的内容相同,所以查询出来COUNT是三,那么如何通过查询可以得出这两行数据有三个字段的内容是相同的呢?
with tb(A,B,C,D,E,F,G,H)as(
select 1,1,2,3,1,1,1,2 union
select 2,1,2,1,1,2,2,1)
select 2*8
-(COUNT(distinct a)+COUNT(distinct b)+COUNT(distinct c)+COUNT(distinct d)+
COUNT(distinct e)+COUNT(distinct f)+COUNT(distinct g)+COUNT(distinct h))
from tb
create table xz
(A int,B int,C int,D int,E int,F int,G int,H int)insert into xz
select 1, 1, 2, 3, 1, 1, 1, 2 union all
select 2, 1, 2, 1, 1, 2, 2, 1
select case count(distinct(A)) when 1 then 1 else 0 end
+case count(distinct(B)) when 1 then 1 else 0 end
+case count(distinct(C)) when 1 then 1 else 0 end
+case count(distinct(D)) when 1 then 1 else 0 end
+case count(distinct(E)) when 1 then 1 else 0 end
+case count(distinct(F)) when 1 then 1 else 0 end
+case count(distinct(F)) when 1 then 1 else 0 end
+case count(distinct(H)) when 1 then 1 else 0 end 'COUNTS'
from xz
/*
COUNTS
-----------
3(1 row(s) affected)
*/
A B C D E F G H
1 1 2 3 1 1 1 2
2 1 2 1 1 2 2 1
3 3 3 3 1 2 2 1
。……。…。…。………
比如有另一行数据:
1 1 2 1 2 1 1 2那么我要查询表里有几个字段和它相同的,
然后把相同数超过3个的过滤出来。在我给的数据里应该查出来的是前两行数据。
如何实现呢?
with t1 as (
select 1 a,1 b,2 c,1 d,2 e,1 f,1 g,2 h
)
select t.* from table1 t
join t1 on
(
case when t.a=t1.a then 1 else 0 end+
case when t.b=t1.b then 1 else 0 end+
case when t.c=t1.c then 1 else 0 end+
case when t.d=t1.d then 1 else 0 end+
case when t.e=t1.e then 1 else 0 end+
case when t.f=t1.f then 1 else 0 end+
case when t.g=t1.g then 1 else 0 end+
case when t.h=t1.h then 1 else 0 end
)>=3
--11212112
with tb(A,B,C,D,E,F,G,H)as(
select 1,1,2,3,1,1,1,2 union
select 1,1,2,1,1,2,2,1 union
select 3,3,3,3,1,2,2,1 union
select 1,1,2,1,1,3,3,1)
select
case when (COUNT(case when a=1 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when b=1 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when c=2 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when d=1 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when e=2 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when f=1 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when g=1 then 1 else null end))>=3 then 1 else 0 end+
case when (COUNT(case when h=2 then 1 else null end))>=3 then 1 else 0 end
from tb
create table xz
(A int,B int,C int,D int,E int,F int,G int,H int)insert into xz
select 1, 1, 2, 3, 1, 1, 1, 2 union all
select 2, 1, 2, 1, 1, 2, 2, 1 union all
select 3, 3, 3, 3, 1, 2, 2, 1
create table oth
(A int,B int,C int,D int,E int,F int,G int,H int)insert into oth
select 1, 1, 2, 1, 2, 1, 1, 2
select x.A,x.B,x.C,x.D,x.E,x.F,x.G,x.H
from xz x
cross join oth y
where (case when x.A=y.A then 1 else 0 end
+case when x.B=y.B then 1 else 0 end
+case when x.C=y.C then 1 else 0 end
+case when x.D=y.D then 1 else 0 end
+case when x.E=y.E then 1 else 0 end
+case when x.F=y.F then 1 else 0 end
+case when x.G=y.G then 1 else 0 end
+case when x.H=y.H then 1 else 0 end)>=3/*
A B C D E F G H
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 1 1 1 2
2 1 2 1 1 2 2 1(2 row(s) affected)
*/