更难的SQL题目!!!

最新推荐文章于 2023-03-03 19:28:58 发布

cuishuobi4947

最新推荐文章于 2023-03-03 19:28:58 发布

阅读量264

点赞数

文章标签：面试

更难的SQL题目!!!
Q：再考一道SQL面试难题,因为是英文翻译过来的,不知道大家能不能理解我的意思
再问大家一道SQL面试题

表a

字段
a1,a2,a3,a4,a5,a6,a7,a8

请统计出每两列重复，三列重复，四列重复,五列重复,6列重复,7列重复记录的值

统计结果如下

重复2列的 XXX
重复3列的 XXX
重复4列的 XXX
重复5列的 XXX
重复6列的 XXX
重复7列的 XXX

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

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

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

比如
一行数据为：1, 1, 2, 2, 3, 3, 4, 4
该如何统计
虽然重复多个，但是算一行,就是两个重复的

1, 1, 2, 2, 3, 3, 4, 4 算两个重复的一列
1, 1, 2, 5, 6, 3, 4, 9算两个重复的一列
1, 1, 1, 3, 5, 7 ,2 ,6 算三个重复的一列
1, 1, 1 , 3, 3 ,3 8 ,8 算三个重复的一列

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

就算重复2列的 XXX

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

就算重复3列的 XXX

A：
I〉针对一般情况的情况的处理
方法一：with tt as (
select 1 no1, 1 no2, 2 no3, 2 no4, 3 no5, 3 no6, 4 no7 , 4 no8 from dual
)
select max(count(0)) from(
select decode(rn,1,no1,2,no2,3,no3,4,no4,5,no5,6,no6,7,no7,8,no8)col from(
select tt.*,a.rn from tt,(select rownum rn from dual connect by rownum<9) a
)) group by col
方法二：
create table test(a number,b number,c number,d number,e number,f number,g number,h number);
insert into test values(1, 1, 2, 2, 3, 3, 4, 4 );
insert into test values(1, 1, 2, 5, 6, 3, 4, 9 );
insert into test values(1, 1, 1, 3, 5, 7, 2, 6 );
insert into test values(1, 1, 1, 3, 3, 3, 8, 8 );

create or replace type t_object as object(
id varchar2(60),
sub_id varchar2(60));
/

create type t_ret_table is table of t_object;
/

create or replace function f_test(var_str in varchar2) return t_ret_table PIPELINED
as
var_tmp varchar2(60);
var_element varchar2(60);
begin
for i in (select a||'@@'||b||'@@'||c||'@@'||d||'@@'||e||'@@'||f||'@@'||g||'@@'||h id from test) loop
var_tmp := i.id;
while instr(var_tmp,'@@')>0 loop
var_element := substr(var_tmp,1,instr(i.id,'@@')-1);
var_tmp := substr(var_tmp,instr(i.id,'@@')+2,length(var_tmp));
pipe row(t_object(i.id,var_element));
end loop;
pipe row(t_object(i.id,var_tmp));
end loop;
return;
end f_test;
/

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)
------------------------------------------------------------ ----------
11133388 3
11135726 3
11223344 2
11256349 2

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

分两种情况
1.每一列的长度为1，也就是单字符char(1)的
2.每一列得长度不定，也就是varchar2

测试用表；
create table uss
(
a1 varchar2(10),
a2 varchar2(10),
a3 varchar2(10),
a4 varchar2(10),
a5 varchar2(10),
a6 varchar2(10),
a7 varchar2(10),
a8 varchar2(10)
)
nologging;
一：对于1
A：插入测试数据：
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','3','3','2','2','3','4');
insert into uss values('1','1','3','3','2','2','3','4');
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','1','1','2','2','3','4');
insert into uss values('1','1','6','1','2','1','3','4');
insert into uss values('1','1','1','6','2','1','3','4');
commit;
B：建立函数
create or replace function foundoutchar1(str in varchar2) return varchar2 as
instr number := 0;
instr1 number := 0;
begin
for i in 2 .. length(str)
loop
if (substr(str,i,1) = substr(str, i - 1, 1)) then
instr := instr + 1;
if (i = length(str) and instr > instr1) then
instr1 := instr;
end if;
else
if (instr > instr1) then
instr1 := instr;
end if;
instr := 0;
end if;
end loop;
return '重复' || (instr1+1) || '列的';
end;
C：运行的sql:
select count(foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)) aa,
foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8) aa1
from uss t
group by foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8);
D:执行结果
SQL> set serveroutput on
SQL>
SQL> select count(foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)) aa,
2 foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8) aa1
3 from uss t
4 group by foundoutchar1(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8)
5 ;

AA AA1
---------- --------------------------------------------------------------------------------
1 重复3列的
3 重复4列的
3 重复2列的
二：对于2
A：插入测试数据：
truncate table uss;

insert into uss values('11','11','11','11','22','22','33','44');
insert into uss values('11','11','33','33','22','22','33','44');
insert into uss values('11','11','33','33','22','22','33','44');
insert into uss values('11','11','11','11','22','22','33','44');
insert into uss values('11','11','11','11','22','22','33','44');
insert into uss values('11','11','66','11','22','11','33','44');
insert into uss values('11','11','11','66','22','11','33','44');
commit;
B：建立函数
create or replace function foundoutchar(str in varchar2,chrlength in varchar2) return varchar2 as
instr number := 0;
instr1 number := 0;
forinc number :=1;
forinc1 number :=1;
begin
for i in 2 .. length(chrlength)
loop
forinc:=forinc+to_number(substr(chrlength,i-1,1));
if (substr(str,forinc,to_number(substr(chrlength,i,1))) = substr(str, forinc1, to_number(substr(chrlength,i-1,1)))) then
instr := instr + 1;
if (i = length(chrlength) and instr > instr1) then
instr1 := instr;
end if;
else
if (instr > instr1) then
instr1 := instr;
end if;
instr := 0;
end if;
forinc1:=forinc1+to_number(substr(chrlength,i-1,1));
end loop;
return '重复' || (instr1+1) || '列的';
end;
C：运行的sql:
select count(foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
length(t.a1) || length(t.a2) || length(t.a3) ||
length(t.a4) || length(t.a5) || length(t.a6) ||
length(t.a7) || length(t.a8))) aa,
foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
length(t.a1) || length(t.a2) || length(t.a3) ||
length(t.a4) || length(t.a5) || length(t.a6) ||
length(t.a7) || length(t.a8)) aa1
from uss t
group by foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
length(t.a1) || length(t.a2) || length(t.a3) ||
length(t.a4) || length(t.a5) || length(t.a6) ||
length(t.a7) || length(t.a8));
D：执行结果
SQL> select count(foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
2 length(t.a1) || length(t.a2) || length(t.a3) ||
3 length(t.a4) || length(t.a5) || length(t.a6) ||
4 length(t.a7) || length(t.a8))) aa,
5 foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
6 length(t.a1) || length(t.a2) || length(t.a3) ||
7 length(t.a4) || length(t.a5) || length(t.a6) ||
8 length(t.a7) || length(t.a8)) aa1
9 from uss t
10 group by foundoutchar(t.a1 || t.a2 || t.a3 || t.a4 || t.a5 || t.a6 || t.a7 || t.a8,
11 length(t.a1) || length(t.a2) || length(t.a3) ||
12 length(t.a4) || length(t.a5) || length(t.a6) ||
13 length(t.a7) || length(t.a8));