查看: 6588|回复: 9

[每日一题] PL/SQL Challenge 每日一题:2017-4-12 唯一约束

[复制链接]
论坛徽章:
528
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2017-4-26 23:32 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:ChrisSaxon

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

你正在创建一个应用来管理比赛结果。它会在下列的表保存比赛结果:

create table plch_game_results (
  home_team_id     int not null,
  away_team_id     int not null,
  game_datetime    date not null,
  home_team_points int not null,
  away_team_points int not null
);


现在里面有一个比赛的结果:

insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  1, 3, date'2017-01-01', 1, 1
);

commit;

一个队在任一时间只能打一场比赛,不管是主场还是客场。但不能同时是主客队!你老板让你创建一个约束来进行制约。

哪些选项创建了这表上的约束,使得所有下列的插入都会报异常?

insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  1, 1, date'2017-01-01', 1, 1
);
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  2, 1, date'2017-01-01', 1, 1
);
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  3, 1, date'2017-01-01', 1, 1
);
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  2, 3, date'2017-01-01', 1, 1
);

注意:一个队不能在比赛结束之前开始另一比赛,但是这不在本题讨论范围。这仅仅是测试一个对不能在恰好同一时间打两场比赛。

(A)
alter table plch_game_results add constraint plch_gare_one_game_u1
  unique ( home_team_id, game_datetime );
alter table plch_game_results add constraint plch_gare_one_game_u2
  unique ( away_team_id, game_datetime );

(B)
alter table plch_game_results add constraint plch_gare_one_game_u
  unique ( home_team_id, away_team_id, game_datetime );

(C)
create unique index plch_gare_one_game_ui on plch_game_results (
  game_datetime,
  least(home_team_id, away_team_id),
  greatest(home_team_id, away_team_id)
);

(D)
create unique index plch_gare_one_game_ui1 on plch_game_results (
  game_datetime,
  decode( mod(home_team_id, 2), 0, home_team_id, away_team_id )
);
create unique index plch_gare_one_game_ui2 on plch_game_results (
  game_datetime,
  decode( mod(home_team_id, 2), 0, away_team_id, home_team_id )
);
create unique index plch_gare_one_game_ui3 on plch_game_results (
  game_datetime,
  decode( mod(away_team_id, 2), 1, away_team_id, home_team_id )
);
create unique index plch_gare_one_game_ui4 on plch_game_results (
  game_datetime,
  decode( mod(away_team_id, 2), 1, home_team_id, away_team_id )
);
alter table plch_game_results add constraint plch_gare_diff_teams_c
  check ( home_team_id <> away_team_id );

(E)
create unique index plch_gare_one_game_ui1 on plch_game_results (
  game_datetime,
  case when home_team_id < away_team_id then home_team_id end
);
create unique index plch_gare_one_game_ui2 on plch_game_results (
  game_datetime,
  case when home_team_id < away_team_id then away_team_id end
);
create unique index plch_gare_one_game_ui3 on plch_game_results (
  game_datetime,
  case when home_team_id > away_team_id then home_team_id end
);
create unique index plch_gare_one_game_ui4 on plch_game_results (
  game_datetime,
  case when home_team_id > away_team_id then away_team_id end
);
alter table plch_game_results add constraint plch_gare_diff_teams_c
  check ( home_team_id <> away_team_id );
论坛徽章:
528
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2017-4-28 04:38 | 显示全部楼层
人都到哪去了?我查了下,今年五一假期也还没开始呢?

使用道具 举报

回复
论坛徽章:
44
秀才
日期:2016-04-29 15:03:39秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:35:05秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50状元
日期:2018-01-03 13:36:41榜眼
日期:2018-01-03 13:36:41
发表于 2017-4-28 11:27 | 显示全部楼层
我选D
AB不能同时约束主客队
CE不能约束时间
D正确

使用道具 举报

回复
论坛徽章:
44
秀才
日期:2016-04-29 15:03:39秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:35:05秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50状元
日期:2018-01-03 13:36:41榜眼
日期:2018-01-03 13:36:41
发表于 2017-4-28 11:29 | 显示全部楼层
这段时间被技术炒股大牛吸引过去了

使用道具 举报

回复
论坛徽章:
323
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2017-4-28 12:46 | 显示全部楼层
这一期没有一个正确答案

D和E是一样的,但都不能阻止所有的插入,以D为例:

SQL> drop table plch_game_results purge;
Table dropped
SQL> create table plch_game_results (
  2    home_team_id     int not null,
  3    away_team_id     int not null,
  4    game_datetime    date not null,
  5    home_team_points int not null,
  6    away_team_points int not null
  7  );
Table created
SQL> --现在里面有一个比赛的结果:
SQL> insert into plch_game_results (
  2    home_team_id, away_team_id, game_datetime,
  3    home_team_points , away_team_points
  4  ) values (
  5    1, 3, date'2017-01-01', 1, 1
  6  );
1 row inserted
SQL> commit;
Commit complete

SQL>
SQL> create unique index plch_gare_one_game_ui1 on plch_game_results (
  2    game_datetime,
  3    decode( mod(home_team_id, 2), 0, home_team_id, away_team_id )
  4  );
Index created
SQL> create unique index plch_gare_one_game_ui2 on plch_game_results (
  2    game_datetime,
  3    decode( mod(home_team_id, 2), 0, away_team_id, home_team_id )
  4  );
Index created
SQL> create unique index plch_gare_one_game_ui3 on plch_game_results (
  2    game_datetime,
  3    decode( mod(away_team_id, 2), 1, away_team_id, home_team_id )
  4  );
Index created
SQL> create unique index plch_gare_one_game_ui4 on plch_game_results (
  2    game_datetime,
  3    decode( mod(away_team_id, 2), 1, home_team_id, away_team_id )
  4  );
Index created
SQL> alter table plch_game_results add constraint plch_gare_diff_teams_c
  2    check ( home_team_id <> away_team_id );
Table altered

SQL>
SQL>
SQL> set serveroutput on;
SQL>
SQL>
SQL> insert into plch_game_results (
  2    home_team_id, away_team_id, game_datetime,
  3    home_team_points , away_team_points
  4  ) values (
  5    1, 1, date'2017-01-01', 1, 1
  6  );
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  1, 1, date'2017-01-01', 1, 1
)
ORA-02290: 违反检查约束条件 (PLSQL.PLCH_GARE_DIFF_TEAMS_C)
SQL> insert into plch_game_results (
  2    home_team_id, away_team_id, game_datetime,
  3    home_team_points , away_team_points
  4  ) values (
  5    2, 1, date'2017-01-01', 1, 1
  6  );
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  2, 1, date'2017-01-01', 1, 1
)
ORA-00001: 违反唯一约束条件 (PLSQL.PLCH_GARE_ONE_GAME_UI2)
SQL> insert into plch_game_results (
  2    home_team_id, away_team_id, game_datetime,
  3    home_team_points , away_team_points
  4  ) values (
  5    3, 1, date'2017-01-01', 1, 1
  6  );
1 row inserted
SQL> insert into plch_game_results (
  2    home_team_id, away_team_id, game_datetime,
  3    home_team_points , away_team_points
  4  ) values (
  5    2, 3, date'2017-01-01', 1, 1
  6  );
insert into plch_game_results (
  home_team_id, away_team_id, game_datetime,
  home_team_points , away_team_points
) values (
  2, 3, date'2017-01-01', 1, 1
)
ORA-00001: 违反唯一约束条件 (PLSQL.PLCH_GARE_ONE_GAME_UI2)

SQL> select * from plch_game_results;
                           HOME_TEAM_ID                            AWAY_TEAM_ID GAME_DATETIME                        HOME_TEAM_POINTS                        AWAY_TEAM_POINTS
--------------------------------------- --------------------------------------- ------------- --------------------------------------- ---------------------------------------
                                      1                                       3 2017/1/1                                            1                                       1
                                      3                                       1 2017/1/1                                            1                                       1

SQL>

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2017-4-28 14:12 | 显示全部楼层
感觉都很麻烦,有更好的设计吗

使用道具 举报

回复
论坛徽章:
44
秀才
日期:2016-04-29 15:03:39秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:35:05秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50状元
日期:2018-01-03 13:36:41榜眼
日期:2018-01-03 13:36:41
发表于 2017-4-28 14:33 | 显示全部楼层
solomon_007 发表于 2017-4-28 12:46
这一期没有一个正确答案

D和E是一样的,但都不能阻止所有的插入,以D为例:

d确实也是错的,二个都是奇数就有问题.

使用道具 举报

回复
论坛徽章:
44
秀才
日期:2016-04-29 15:03:39秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:29:54秀才
日期:2018-01-02 15:35:05秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50秀才
日期:2018-01-02 15:35:50状元
日期:2018-01-03 13:36:41榜眼
日期:2018-01-03 13:36:41
发表于 2017-4-28 14:34 | 显示全部楼层
期待优雅的答案!

使用道具 举报

回复
论坛徽章:
323
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2017-4-28 16:08 | 显示全部楼层
〇〇 发表于 2017-4-28 14:12
感觉都很麻烦,有更好的设计吗

我昨天想了一天也没想到好的办法

使用道具 举报

回复
论坛徽章:
528
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2017-4-28 21:56 | 显示全部楼层
答案:全错, 5楼得奖。

A: 这只是检验一个队一次只能打一个主场比赛或客场比赛。但是它仍然允许在同一时间安排一个主场和客场比赛!
B: 这确保一个主队不能同时作为客队。但是它仍然允许一个队在同一时间打多个主场或客场比赛。
C: 这会放置两个对在同一时间互相作为主客队。但是它仍然允许一个对在同一时间打其它比赛(不管主场客场)
D: 这已经很接近了,防止了许多重复。但是你仍然能够把主客队反过来然后插入!
结果表中的数据像这样:

HOME_TEAM_ID  AWAY_TEAM_ID  GAME_DATETIME         HOME_TEAM_POINTS  AWAY_TEAM_POINTS  
1             3             01-JAN-2017 00:00:00  1                 1                 
3             1             01-JAN-2017 00:00:00  1                 1     
E: 这已经很接近了。但是在CASE表达式中没有ELSE子句。所以当条件为假,它返回NULL。如果你全部加上  "else xxxx_team_id", 此处xxxx是不在WHEN子句中的队,那么这个方法就能满足需求。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

【有奖讨论】解决存储挑战了解一下
奖品:米家车载空气净化器 、米家声波电动牙刷 、小米运动蓝牙耳机

在数字经济时代,井喷式增长的数据,在释放大量商业价值的同时,也随之对企业的IT基础设施带来了不容忽视的挑战!如何存储、管理、使用这些数据呢?这是一条比以往更艰难的路~

活动时间:9月20日-10月11日

参与讨论>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表