关于一个oracle的题目:验证该FIXTURE名单验证该FIXTURE名单已妥善制定出来。也就是说,确保每个队一周只有一次比赛,主场或客场,总共打六场。
表FIXTURE包含去年锦标赛的相关的俱乐部。为验证这些Fixture妥善,找出潜在的问题。写完SQL脚本后,你可以把fixture故意改成错的,再运行,看是否能发现问题。该题的目的是,确保在有电脑的协助下,迅速核实该FIXTURE是否正确制定。
The league consists of four clubs:
CODE NAME LOCATION
---- -------------------- -----------------
TW Turf Worz Cross Blades
HR High Rollers Bias Sidings
WT Whytatts Parssel Shelf
DS Draw Shots Jacks Close The fixture list
for last season was:
WEEK FIXTURE
------- -------
1 TW v HR
1 WT v DS
2 HR v WT
2 DS v TW
3 HR v DS
3 WT v TW
4 HR v TW
4 DS v WT
5 WT v HR
5 TW v DS
6 TW v WT
6 DS v HR
表FIXTURE包含去年锦标赛的相关的俱乐部。为验证这些Fixture妥善,找出潜在的问题。写完SQL脚本后,你可以把fixture故意改成错的,再运行,看是否能发现问题。该题的目的是,确保在有电脑的协助下,迅速核实该FIXTURE是否正确制定。
The league consists of four clubs:
CODE NAME LOCATION
---- -------------------- -----------------
TW Turf Worz Cross Blades
HR High Rollers Bias Sidings
WT Whytatts Parssel Shelf
DS Draw Shots Jacks Close The fixture list
for last season was:
WEEK FIXTURE
------- -------
1 TW v HR
1 WT v DS
2 HR v WT
2 DS v TW
3 HR v DS
3 WT v TW
4 HR v TW
4 DS v WT
5 WT v HR
5 TW v DS
6 TW v WT
6 DS v HR
解决方案 »
- pl/sql 中怎么实现对一个参数的重复输入
- 求助:PLS-00201:UTL_FILE报错问题???
- Stored procedure 'p_mypro' may be run only in unchained transaction mode
- ora-04031 无法分配4096字节的共享内存
- 急求oracle client 9 dll文件!!!!
- 关于临时表权限的问题(global temporary table)
- 如何通过程序生成oracle数据库中已经存在的表的建表脚本?
- 请教各位高手,怎样把selserver中的函数转换成oracle的函数????急!!!!完成转换立即揭帖!
- 请问oracle8i 存放表和字段信息的系统表是哪个,注释信息又都在系统表的什么字段里
- 一个在2000下安装ORACLE8.0.5出现的问题?
- 一个关于oracle自由发挥的问题——最好多点做法
- powerdesigner生成sql脚本
select case when select count(*) from
(select m.week,count(distinct(m.club))
from
(select f.week as week,substr(f.fixture,1,2) as club,'home' as flag--主客场标志
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))
union all
(select f.week as week,substr(f.fixture,1,2) as club,'away' as flag
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))m,--将FIXTURE字段进行拆分,且拆分后的队名在CLUB表中存在,并将记录竖排。
group by m.week
having count(distinct(m.club)) = 4) --检验每周的比赛是否4个队都参与。
=6 --检查正确的记录数是否为6条(比赛周数)。
and select count(*) from
(select n.club,n.flag,count(*)
from
(select f.week as week,substr(f.fixture,1,2) as club,'home' as flag--主客场标志
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))
union all
(select f.week as week,substr(f.fixture,1,2) as club,'away' as flag
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))n,--将FIXTURE字段进行拆分,且拆分后的队名在CLUB表中存在,并将记录竖排。
group by n.club,n.week
having count(*) = 3) --检验每队主/客场比赛是否各3场。
=8 --检查正确的记录数是否为8条(队数*2)。
then 'TRUE' else 'FALSE' end from dual
--凭空写的,可能语法有误,大致思路可参考。
(select f.week as week,substr(f.fixture,1,2) as club,'home' as flag
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))
union all
(select f.week as week,substr(f.fixture,6,2) as club,'away' as flag
from fixture f
where exists (select 'x' from club c where c.code = substr(f.fixture,1,2)))m--命名为表m
这一段,相当于建立出这样一张表:
week club flag
1 TW home
1 WT home
2 HR home
2 DS home
3 HR home
3 WT home
4 HR home
4 DS home
5 WT home
5 TW home
6 TW home
6 DS home
1 HR away
1 DS away
2 WT away
2 TW away
3 DS away
3 TW away
4 TW away
4 WT away
5 HR away
5 DS away
6 WT away
6 HR away--条件1:
select m.week,count(distinct(m.club))
from m
group by week
having count(distinct(m.club)) = 4 --检验每周参赛队是否满4个(如果不满足肯定是错误的,如果满足了不一定是正确的,还要判断主客场是否有重复),如果都满足的话,该SELECT语句选出来的记录集应该有6条,因为有6个星期,如果存在错误数据,是会<6条的。
--条件2:
select count(*) from
from m,
group by m.club,m.week
having count(*) = 3 --检验每队主/客场比赛是否各3场。如果都满足的话,该SELECT语句选出来的记录集应该有8条,因为有队数*主客场标志=8,如果存在错误数据,是会<8条的。同时满足上述两个条件的FIXTURE名单是正确的。