我现在有一张表TableA字段如下
ID,R1,R2,R3,R4,R5,R6
我想实现如下的查询SQL语句该如何写找出这个表中 五个字段相同且ID不相同 的记录
Select * from (
SELECT
a.ID,a.R1,a.R2,a.R3,a.R4,a.r5,a.r6,
CASE WHEN a.R1 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6 ) THEN 1 ELSE 0 END
+CASE WHEN a.R2 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R3 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R4 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R5 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R6 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END AS XO
FROM odbtable a,dbtable as b where a.id<>b.id
) c where XO=5 上面的语句运行后的结果不正确
ID,R1,R2,R3,R4,R5,R6
我想实现如下的查询SQL语句该如何写找出这个表中 五个字段相同且ID不相同 的记录
Select * from (
SELECT
a.ID,a.R1,a.R2,a.R3,a.R4,a.r5,a.r6,
CASE WHEN a.R1 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6 ) THEN 1 ELSE 0 END
+CASE WHEN a.R2 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R3 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R4 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R5 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R6 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END AS XO
FROM odbtable a,dbtable as b where a.id<>b.id
) c where XO=5 上面的语句运行后的结果不正确
from a aa
where exists(select * from a where aa.r1=r1 and aa.r2=r2 and aa.r3=r3 and aa.r4=r4 and aa.r5=r5 and aa.r6=r6 and aa.id<>id)
1 1 1 1 2 3 4
2 1 1 3 4 5 6算多少字段相同,为什么?
WHERE EXISTS(select 1 from a where aa.r1=r1 and aa.r2=r2 and aa.r3=r3 and aa.r4=r4 and aa.r5=r5 and aa.r6=r6 and aa.id<>id)?
from a aa
where exists(select * from a where aa.r1=r1 and aa.r2=r2 and aa.r3=r3 and aa.r4=r4 and aa.r5=r5 and aa.r6=r6 and aa.id<>id)顶!*****
ID 不在比较的范围
1 1 1 1 2 3 4
2 1 1 3 4 5 6 算多少字段相同,为什么?-------------
同一条记录中R1,R2,R3,R4,R5,R6 不会出现相同的数字
ID 不在比较的范围
那
ID R1 R2 R3 R4 R5 R6
1 1 2 3 4 5 6
2 2 3 4 5 6 7 算多少字段相同,为什么?
declare @t table(id int,R1 int,R2 int,R3 int,R4 int,R5 int,R6 int)
insert @t
select 1,1,2,3,4,5,6 union all
select 2,10,11,3,4,6,5 union all
select 3,1,2,3,4,10,6 union all
select 4,1,2,3,4,5,6 select * from
(
select A.id Aid,B.id Bid,
case when A.R1 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R2 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R3 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R4 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R5 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R6 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end as total
from @t A
inner join @t B
on A.id<B.id
) C
where total=5Aid Bid total
----------- ----------- -----------
1 3 5
3 4 5
楼主还是没说清楚啊。
那
ID R1 R2 R3 R4 R5 R6
1 1 2 3 4 5 6
2 2 3 4 5 6 7 算多少字段相同,为什么?
---------------------
算5字段相同 , 因为 第一条记录中的 2,3,4,5,6 在第二条记录中出现了,换句话说就是一条记录中有五个
字段的值在第二条记录中出现,不考虑位置的变化 (当然ID这个字段是不在比较的范围的)希望我的解释能让你明白
SELECT
a.ID,a.QIHAO,a.R1,a.R2,a.R3,a.R4,a.r5,a.r6,
CASE WHEN a.R1 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6 ) THEN 1 ELSE 0 END
+CASE WHEN a.R2 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R3 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R4 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R5 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END
+CASE WHEN a.R6 IN (b.R1,b.R2,b.R3,b.R4,b.R5,b.R6) THEN 1 ELSE 0 END AS XO
FROM dbtable a,dbtable as b where a.id<>b.id
) c where XO=5
但没能显示出如下的结果求 ( 第一条记录 显示: 如ID为60这条记录有5个红球相同的记录的ID分别是
233 和 235
ID QIHAO R1 R2 R3 R4 R5 R6
60 4017 5 12 14 15 25 31 233 235
100 4104 7 11 17 18 24 29 354 537
235 6105 5 12 14 15 20 31 60 903我要的就是这个显示效果 (可能与之相同的记录不只两条 )兄弟,这要不清楚我就结帖子了
declare @t table(id int,R1 int,R2 int,R3 int,R4 int,R5 int,R6 int)
insert @t
select 1,1,2,3,4,5,6 union all
select 2,10,11,3,4,6,5 union all
select 3,1,2,3,4,10,6 union all
select 4,1,2,3,4,5,6
;
with temp as
(
select * from
(
select A.*,B.id Bid,
case when A.R1 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R2 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R3 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R4 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R5 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end +
case when A.R6 in (B.R1,B.R2,B.R3,B.R4,B.R5,B.R6) then 1 else 0 end as total
from @t A inner join @t B on A.id<>B.id
) C
where total=5
)select distinct id,R1,R2,R3,R4,R5,R6,
(select cast(Bid as char(3))+' ' from temp where D.id=id for xml path(''))
from temp D
id R1 R2 R3 R4 R5 R6
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 2 3 4 5 6 3
3 1 2 3 4 10 6 1 4
4 1 2 3 4 5 6 3