table3A B C D E F 1 3 2 5 4 4 1 1 2 4 3 6 3 6 6 3 3 0 1 0 3 5 7 9 4 3 4 0 3 7 0 7 7 6 3 6 比如执行:select top 3 * from table3
结果:132544 112436 366330结果就相当是有3个六位数字,现在要筛选出 这些六位数中 有相同数字的数,而且相同数字的出现次数在2次或两次以上;比如上面出现的这3个六位数:132544,112436,366330,
其中相同数字的出现次数在2次或两个以上的就只有366330,因为它重复的数有3和6--->相同数字的出现次数在2次或两次以上
而其132544中重复的只有4,112436中重复的只有1,他们都只出现了一次重复,如: 366330 含有3个3 2个6 434037 含有2个4 2个3 077636 含有2个7 2个6
结果:132544 112436 366330结果就相当是有3个六位数字,现在要筛选出 这些六位数中 有相同数字的数,而且相同数字的出现次数在2次或两次以上;比如上面出现的这3个六位数:132544,112436,366330,
其中相同数字的出现次数在2次或两个以上的就只有366330,因为它重复的数有3和6--->相同数字的出现次数在2次或两次以上
而其132544中重复的只有4,112436中重复的只有1,他们都只出现了一次重复,如: 366330 含有3个3 2个6 434037 含有2个4 2个3 077636 含有2个7 2个6
table3 A B C D E F 1 3 2 5 4 4 1 1 2 4 3 6 3 6 6 3 3 0 1 0 3 5 7 9 4 3 4 0 3 7 0 7 7 6 3 6 比如执行:select top 3 * from table3
结果:132544 112436 366330 你select top 3 * from table3 会查出132544 112436 366330 ???????????
你这问题好有挑战性,关注
select top 3 * from table3 是错误的,想要的查询结果是132544,(举例说明)。
*
from
(select 1 A, 3 B, 2 C, 5 D , 4 E, 4 F
union all
select 1 A, 1 B, 2 C, 4 D , 3 E, 6 F
union all
select 3 A, 6 B, 6 C, 3 D , 3 E, 0 F union all
select 1 A, 0 B, 3 C, 5 D , 7 E, 9 F union all
select 4 A, 3 B, 4 C, 0 D , 3 E, 7 F union all
select 0 A, 7 B, 7 C, 6 D , 3 E, 6 F)tab
where((A=B) and (B=C) and (C=D) and (D=E) and(E=F)) or
((B=C) and (C=D) and (D=E) and(E=F))
or
((B=C) and (D=E) and(E=F))
or
((B=C) and (D=E) )
....
继续添加条件,
(ID int,A int,B int,C int,D int,E int,F int)
Insert Into #Temp Values
(1,1, 3, 2, 5, 4, 4)
Insert Into #Temp Values
(2,1, 1, 2, 4, 3, 6)
Insert Into #Temp Values
(3,3, 6, 6, 3, 3, 0)
Insert Into #Temp Values
(4,1, 0, 3, 5, 7, 9)
Insert Into #Temp Values
(5,4, 3, 4, 0, 3, 7)
Insert Into #Temp Values
(6,0, 7, 7, 6, 3, 6)
Select A,B,C,D,E From #Temp Where ID in (
Select ID From (Select ID, 6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(A as nvarchar(2)),'')) as LenA,
6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(B as nvarchar(2)),'')) as LenB,
6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(C as nvarchar(2)),'')) as LenC,
6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(D as nvarchar(2)),'')) as LenD,
6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(E as nvarchar(2)),'')) as LenE,
6-Len(Replace(Cast(A as nvarchar(2)) + Cast(B as nvarchar(2)) + Cast(C as nvarchar(2)) + Cast(D as nvarchar(2)) + Cast(E as nvarchar(2)) + Cast(F as nvarchar(2)),Cast(F as nvarchar(2)),'')) as LenF
From #Temp ) As A
Where LenA+LenB+LenC+LenD+LenE+LenF>=6)Drop table #Temp