才两张表，随便写写就出来了 你是没见过该死的bom 那才伤脑筋
--先建立测试用例：
create table users(Id Int,Name varchar(20),regDate date);
alter table users add constraint users_pk primary key(Id);
create table UploadInfo(Id Int,UserId Int,FileName varchar(20),foreign key(UserId) references users(Id));
insert into users values(1,'A',sysdate-1);
insert into users values(2,'B',sysdate-2);
insert into users values(3,'C',sysdate-3);
insert into users values(4,'D',sysdate-4);
insert into users values(5,'E',sysdate-5);
select*from users;
--随机插入上传数据的存储过程
create or replace procedure InsertUploadInfoData is
i int:=0;
begin
for i in 1..100 loop
insert into UploadInfo values(i,trunc(DBMS_RANDOM.value(1,5)),'FileName '||trunc(DBMS_RANDOM.value(1,1000)));
end loop;
commit;
end ;
begin
InsertUploadInfoData;
end;
select*from UploadInfo;
--开始了
--第一题
select UserUploadRecord.Id,UserUploadRecord.UploadCount
from
(
select users.Id,count(UploadInfo.Id) as UploadCount from users
inner join UploadInfo on users.Id=UploadInfo.UserId --内联结
where users.regDate>=sysdate-3 and users.regDate<=sysdate --三天内
group by users.Id
order by UploadCount desc
) UserUploadRecord
where rownum=1 --取第一条记录
--创建用户名重复的测试用例
insert into users values(6,'A',sysdate-4);
--由于有外键约束，所以需先删除子表记录
delete from UploadInfo where UploadInfo.Userid
in
(
select users.id from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1 --取重复用户名的最大注册时间
)
)
--再删除父表，做法差不多
delete from users where (users.name,users.regdate) in
(
select users.name,max(users.regdate) from users group by users.name having count(users.id)>1
)
————————————————————
建表语法 测试用例都写给你了 还说运行不了？直接复制进去就行了
select 日期,isnull(count(case when 成绩='胜' then 1 end),0)胜
,isnull(sum(case when 成绩='负' then 1 end),0)负 from score group by 日期 order by 日期
