更难的SQL题目!!!

更难的SQL题目!!! ITPUB个人空间/L(Z#ac[Je1k
Q：再考一道SQL面试难题,因为是英文翻译过来的,不知道大家能不能理解我的意思
7u/kkz"I*CDX0再问大家一道SQL面试题

表a

字段ITPUB个人空间E]q#P"|kT%b/f/p
a1,a2,a3,a4,a5,a6,a7,a8

ITPUB个人空间RWkZ%xuY2r
请统计出每两列重复，三列重复，四列重复,五列重复,6列重复,7列重复记录的值

统计结果如下

重复2列的 XXXITPUB个人空间ZLu:PQ T?
重复3列的 XXXITPUB个人空间N/xI c't
重复4列的 XXXITPUB个人空间G AJVo#W
重复5列的 XXX
1imDS-k%QXv0重复6列的 XXXITPUB个人空间.`?ng Jv
重复7列的 XXX

比如记录中如果出现 a1=a2 有两行那就表示两列重复的值为2

但是如果是三列之间相等那要算3列的,和两列之间无关了，前面说的两列相等要仅仅两列相等
&b9@uF"M/ZB:aS7p0后面的不管说多少列都要说仅仅.就是仅仅两列相等的，仅仅三列相等的。如果四列相等就不能算到三列相等中去

#A
KO/FE3PFN0请做出这个统计,不知道大意大家明白没，我当时看题目是英文的,大意是这样的，请大家看看，是否有点难解

比如
!M&JKmPV
O)A0一行数据为：1, 1, 2, 2, 3, 3, 4, 4ITPUB个人空间 KoH2S7i#J BrU3K
该如何统计ITPUB个人空间5\;ylgtE
虽然重复多个，但是算一行,就是两个重复的

ITPUB个人空间8J RVK9@Q/z
1, 1, 2, 2, 3, 3, 4, 4 算两个重复的一列ITPUB个人空间`Ji:lg
Ise
1, 1, 2, 5, 6, 3, 4, 9算两个重复的一列
'RC nG,~c01, 1, 1, 3, 5, 7 ,2 ,6 算三个重复的一列ITPUB个人空间w W s$p#Z9G9E
1, 1, 1 , 3, 3 ,3 8 ,8 算三个重复的一列

就是说任何字段间记录出现两个相同的就算去统计(3列同就跑到三列的那个范围去了,就不在这里了。两个只相同出现多次不要紧)

就算重复2列的 XXX

就是说任何字段间记录出现三个相同的就算去统计(4列同就跑到4列的那个范围去了,就不在这里了。4个只相同出现多次不要紧,呵呵，不过如果是5个出现相同就不可能出现两对了，因为就8个字段)

就算重复3列的 XXX

["Y M}\9K5rv5c0A：ITPUB个人空间lv\X&C
I〉针对一般情况的情况的处理
8b|Mk4DB!@0方法一：with tt as (ITPUB个人空间Vm8uI&^p
select 1 no1, 1 no2, 2 no3, 2 no4, 3 no5, 3 no6, 4 no7 , 4 no8 from dualITPUB个人空间'C-}xid9y
)ITPUB个人空间$m U(W5Pz7q0rK
select max(count(0)) from(ITPUB个人空间Ez5^KI%xWI
I
select decode(rn,1,no1,2,no2,3,no3,4,no4,5,no5,6,no6,7,no7,8,no8)col from(
/C IIwQIL4D+?.E0select tt.*,a.rn from tt,(select rownum rn from dual connect by rownum<9) a ITPUB个人空间([!^S
)) group by colITPUB个人空间0~z byP
方法二：ITPUB个人空间{ u8w?8]
create table test(a number,b number,c number,d number,e number,f number,g number,h number);ITPUB个人空间d0ruig
insert into test values(1, 1, 2, 2, 3, 3, 4, 4 );
RKA#JD0insert into test values(1, 1, 2, 5, 6, 3, 4, 9 );
}C}(hZ#Y3]`D&_0insert into test values(1, 1, 1, 3, 5, 7, 2, 6 );ITPUB个人空间%Ooo
N:Uh4C)M
insert into test values(1, 1, 1, 3, 3, 3, 8, 8 );

ITPUB个人空间^jz)A'tu9N
create or replace type t_object as object(
%MK| l)f8F0I1v,P0id varchar2(60),ITPUB个人空间|?-Y6\ yN
sub_id varchar2(60));
H1u@u$gc3w8C0/

create type t_ret_table is table of t_object;
3n#Sf5vT o9Zf:Y4_"Z0/

create or replace function f_test(var_str in varchar2) return t_ret_table PIPELINEDITPUB个人空间Y(\2~~#XL7p'h3}:Y)F
as
xwJ#Q'q3P[0var_tmp varchar2(60);
N3K*b ?rw0|0var_element varchar2(60);ITPUB个人空间{&JnDs`
begin
vLm$r8s.H%Y0for i in (select a||'@@'||b||'@@'||c||'@@'||d||'@@'||e||'@@'||f||'@@'||g||'@@'||h id from test) loopITPUB个人空间/L0@,W y9~/C
G
var_tmp := i.id;ITPUB个人空间;F6lh/s+A
while instr(var_tmp,'@@')>0 loop
%iF~V8t
jr)b1r0var_element := substr(var_tmp,1,instr(i.id,'@@')-1);ITPUB个人空间w0lzdO2DBS`/m4v
var_tmp := substr(var_tmp,instr(i.id,'@@')+2,length(var_tmp));
,iz;ZF6W3|\9p0pipe row(t_object(i.id,var_element));
k&Z9vNX&|M:c0end loop;
Y;Cm)|
gZ0pipe row(t_object(i.id,var_tmp));
1_j"Y2v^U{0end loop;ITPUB个人空间1is;tW@/]
return;
?8K(]#wmW)[!FT0end f_test;ITPUB个人空间 Q8jUL)I]$x
/

SQL> select replace(id,'@@') id,max(sl) from (select id,sub_id,count(*) sl from (select * from table(f_test('a'))) group by id,sub_id) group by id;

ID MAX(SL)ITPUB个人空间|,ULA4v+T9MZ+U:B
------------------------------------------------------------ ----------
E _]1R4b"v5n M4AY011133388 3
ZZ aM2H8PE011135726 3
fgWG DOH{ t011223344 2
fU8]wEdgr/TY011256349 2

II〉针对连续列相同才计算的情况的处理

分两种情况
1A1G Q"\R)y01.每一列的长度为1，也就是单字符char(1)的ITPUB个人空间1A Vm
K!B\)i6S
?
2.每一列得长度不定，也就是varchar2

测试用表；ITPUB个人空间"l)ba;dq},xn
create table uss ITPUB个人空间S,bJa-P
(
7cqX;nc~ r0a1 varchar2(10),ITPUB个人空间_lD tx"{+cBdR
a2 varchar2(10),

TrAve%]2cw0a3 varchar2(10),
5L4R7oE$UeAd0a4 varchar2(10),ITPUB个人空间!]"U {a&S
a5 varchar2(10),
7qB^:zil(_'l0a6 varchar2(10),ITPUB个人空间&qX,u[\u E}
a7 varchar2(10),ITPUB个人空间0Y~c.r5LsK'Y
a8 varchar2(10)
!aB?&K n!d\0)
:`-H ibpC ?0nologging;ITPUB个人空间'M.l/c |oY
一：对于1ITPUB个人空间;yErP5w?`
A：插入测试数据：ITPUB个人空间 {`7`c]u
cf*E-{
insert into uss values('1','1','1','1','2','2','3','4');
i']/\tFE M5J'Co0insert into uss values('1','1','3','3','2','2','3','4');ITPUB个人空间Zn#|r|o2bV-m
insert into uss values('1','1','3','3','2','2','3','4');ITPUB个人空间"Q
[s9ITE
insert into uss values('1','1','1','1','2','2','3','4');ITPUB个人空间c"GZ$@$k
insert into uss values('1','1','1','1','2','2','3','4');
g8X Uz'yF0insert into uss values('1','1','6','1','2','1','3','4');ITPUB个人空间p'r4ICv%\d/M1S i
insert into uss values('1','1','1','6','2','1','3','4');ITPUB个人空间9r$["]ucoF:e
commit;ITPUB个人空间8U["^{X
vQ4q
B：建立函数
?e ^8{A? j9K0create or replace function foundoutchar1(str in varchar2) return varchar2 asITPUB个人空间x1j2M2P,h2e
instr number := 0;
VP/f%h}`4}.i:U0 instr1 number := 0;
A'Ks _5D-h[S7zp0beginITPUB个人空间/t&~/ZAn
for i in 2 .. length(str)
G
\bl;z,\8b}0 loopITPUB个人空间1L)Lc[W{d{%J+W
if (substr(str,i,1) = substr(str, i - 1, 1)) then
G4_O:E,tM0 instr := instr + 1;ITPUB个人空间c.fP6|&r9{"Q
if (i = length(str) and instr > instr1) then
,\:X/qE$T4}}t l&E0 instr1 := instr;
3{Ig0p#L6g9i0 end if;
9Tk1Am5__Y'zu0 elseITPUB个人空间eg![7z u3p
if (instr > instr1) thenITPUB个人空间3~5H1l9L9j2G,y
instr1 := instr;
Bsji_:z0 end if;
nt.J+f+Uw"zE0 instr := 0;ITPUB个人空间0B/r6]+l
U'B)l-I\T(t
end if;ITPUB个人空间
@'@m ZTs l
end loop;ITPUB个人空间O5N:Y7C rOm m
return '重复' || (instr1+1) || '列的';ITPUB个人空间0b/hdA"_ {*Im
end;ITPUB个人空间1pZ,X(H
s+F9]
C：运行的sql:ITPUB个人空间b+}x*YWv"C(Di
select count(foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)) aa,ITPUB个人空间4hS/s Wx)wJ9}
foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8) aa1
A~[Mb_/v0from uss t
1Yz+u-\;`tD7N0group by foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8);
X8j8CO"P~T ]Snwt0D:执行结果
M)E
mf$bl0SQL> set serveroutput on
K"QM-Uc;fe%[*H0SQL>
x:G2c([+J%_0SQL> select count(foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)) aa,ITPUB个人空间"Rd'e7c!Sy IbT P
2 foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8) aa1
2u9Okg8ge0 3 from uss tITPUB个人空间'E2`u9@K
4 group by foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)ITPUB个人空间b ?_j7ao1x*H8S
5 ;

AA AA1ITPUB个人空间Y)k8U\
y.K7UR?*L)v:H
---------- --------------------------------------------------------------------------------ITPUB个人空间%y0o}P?IbI4d p\
1 重复3列的
k$cDDn F W5b0 3 重复4列的
7E\Dw@{x;z0 3 重复2列的
evH O"k ld.h!V0二：对于2
$Rp6a%\qr8l0A：插入测试数据：ITPUB个人空间4W0l1j{Op E6wR$Zo
truncate table uss;

insert into uss values('11','11','11','11','22','22','33','44');ITPUB个人空间:T,NO7VFF`
insert into uss values('11','11','33','33','22','22','33','44');ITPUB个人空间9e k"b:p;IQ6`I
insert into uss values('11','11','33','33','22','22','33','44');
;U8Xqv L AYdD0insert into uss values('11','11','11','11','22','22','33','44');
'}4pX4v](K dh0insert into uss values('11','11','11','11','22','22','33','44');ITPUB个人空间B2tp zz`o(r-~b
insert into uss values('11','11','66','11','22','11','33','44');
]Gr4_W{Q|B0insert into uss values('11','11','11','66','22','11','33','44');ITPUB个人空间*{9|'m"NPE-h
commit;
Uu6CGbl8B0B：建立函数ITPUB个人空间$I cLY2JZWFW!m*N
create or replace function foundoutchar(str in varchar2,chrlength in varchar2) return varchar2 asITPUB个人空间.x;SXi BC_8hi
instr number := 0;ITPUB个人空间#r7LX hK.JLG
g:l
instr1 number := 0;ITPUB个人空间zq3i9g6c l6e
forinc number :=1;ITPUB个人空间l9y(O/I-Q!@ E'Oc0i#s M
forinc1 number :=1;
9`4}1gvs0begin
"uw#W$p0X0 for i in 2 .. length(chrlength)
`C)S Fs c0 loop ITPUB个人空间6t.v.BO!W
forinc:=forinc+to_number(substr(chrlength,i-1,1));
!c2fOqu8{0 if (substr(str,forinc,to_number(substr(chrlength,i,1))) = substr(str, forinc1, to_number(substr(chrlength,i-1,1)))) thenITPUB个人空间YU4TnU+rA
instr := instr + 1;ITPUB个人空间m @r*Mj&K pVKg
if (i = length(chrlength) and instr > instr1) then
1KXPa"I0 instr1 := instr;ITPUB个人空间vu0gND+Py G9q ?
end if;

~H4E$E0UE0 elseITPUB个人空间x$BIM-Cj:h
if (instr > instr1) then
W]H7@
]-\EOj0 instr1 := instr;ITPUB个人空间R%H;AK6v\(X2g/X3v
end if;
|sonj#Qh0 instr := 0;
reiY}9AJ0 end if;ITPUB个人空间 x)r"K*it
forinc1:=forinc1+to_number(substr(chrlength,i-1,1));
$?o4gf5H0 end loop;
'AN!D/t.})A3g-D0 return '重复' || (instr1+1) || '列的';
@Abx9Q[0end;ITPUB个人空间GH@4Fzlr1qPw
C：运行的sql:ITPUB个人空间&A|H@m;b/m
select count(foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,ITPUB个人空间 d,`w`VPj
length(t.a1) || length(t.a2) || length(t.a3) ||
Qf p-d)zc5t I0 length(t.a4) || length(t.a5) || length(t.a6) ||
^I
nT+p)_5M;t"L.Q0 length(t.a7) || length(t.a8))) aa,ITPUB个人空间u0~)g)f)U9E3{'X
foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,ITPUB个人空间+cAR)igY\r'?
length(t.a1) || length(t.a2) || length(t.a3) ||
g@L@`1c4MV~0 length(t.a4) || length(t.a5) || length(t.a6) ||ITPUB个人空间?/U a)]i
length(t.a7) || length(t.a8)) aa1ITPUB个人空间+N~!rG[ p3Si0q1Hx^
from uss t
5T
?+F~+w0group by foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,ITPUB个人空间g5?Vz*z\r
length(t.a1) || length(t.a2) || length(t.a3) ||
u]`s#gAK.M0 length(t.a4) || length(t.a5) || length(t.a6) ||ITPUB个人空间/hI#P?Mp
length(t.a7) || length(t.a8));ITPUB个人空间Bb3D$U;~RF1m
D：执行结果ITPUB个人空间3Z7O3Be3?E
SQL> select count(foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
L,R&bT&T0 2 length(t.a1) || length(t.a2) || length(t.a3) ||ITPUB个人空间&sw8DA}2[2JMefG
3 length(t.a4) || length(t.a5) || length(t.a6) ||ITPUB个人空间jG5~W8gx
4 length(t.a7) || length(t.a8))) aa,
3r6n0CN6H |0 5 foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
B&H6pD|+v9D0 6 length(t.a1) || length(t.a2) || length(t.a3) ||ITPUB个人空间mbNqh'GT!JT
7 length(t.a4) || length(t.a5) || length(t.a6) ||
U{E.yJHQ0 8 length(t.a7) || length(t.a8)) aa1ITPUB个人空间 s0iM#a&tl
9 from uss t
7u y6fXA0p5`0 10 group by foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,ITPUB个人空间NaU,a]NF4P
11 length(t.a1) || length(t.a2) || length(t.a3) ||ITPUB个人空间+W(V,H @~-RO(R2n
12 length(t.a4) || length(t.a5) || length(t.a6) ||ITPUB个人空间7ay{;|%xW
gS
13 length(t.a7) || length(t.a8));

AA AA1ITPUB个人空间%J^3h4{%}i(Q
---------- --------------------------------------------------------------------------------
#y/FK1xT B?0 1 重复3列的
fsd8wx
M0c"]D0 3 重复4列的
1n_ OpH0 3 重复2列的