ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 2318|回复: 9

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

[复制链接]
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 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 );
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-4-28 04:38 | 显示全部楼层
人都到哪去了?我查了下,今年五一假期也还没开始呢?

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
发表于 2017-4-28 11:27 | 显示全部楼层
我选D
AB不能同时约束主客队
CE不能约束时间
D正确

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
发表于 2017-4-28 11:29 | 显示全部楼层
这段时间被技术炒股大牛吸引过去了

使用道具 举报

回复
论坛徽章:
234
白羊座
日期:2016-05-23 11:49:19秀才
日期:2016-03-01 09:57:08处女座
日期:2016-10-27 22:16:58天枰座
日期:2015-12-28 11:03:38巨蟹座
日期:2015-12-20 15:00:56巨蟹座
日期:2015-12-14 21:46:03天枰座
日期:2015-11-30 15:57:24天蝎座
日期:2016-08-16 09:49:11秀才
日期:2015-11-23 10:00:44白羊座
日期:2015-11-29 10:44:09
发表于 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>

使用道具 举报

回复
论坛徽章:
393
雪佛兰
日期:2013-12-04 20:30:02马上有钱
日期:2014-03-11 11:59:122014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13
发表于 2017-4-28 14:12 | 显示全部楼层
感觉都很麻烦,有更好的设计吗

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
发表于 2017-4-28 14:33 | 显示全部楼层
solomon_007 发表于 2017-4-28 12:46
这一期没有一个正确答案

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

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

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
发表于 2017-4-28 14:34 | 显示全部楼层
期待优雅的答案!

使用道具 举报

回复
论坛徽章:
234
白羊座
日期:2016-05-23 11:49:19秀才
日期:2016-03-01 09:57:08处女座
日期:2016-10-27 22:16:58天枰座
日期:2015-12-28 11:03:38巨蟹座
日期:2015-12-20 15:00:56巨蟹座
日期:2015-12-14 21:46:03天枰座
日期:2015-11-30 15:57:24天蝎座
日期:2016-08-16 09:49:11秀才
日期:2015-11-23 10:00:44白羊座
日期:2015-11-29 10:44:09
发表于 2017-4-28 16:08 | 显示全部楼层
〇〇 发表于 2017-4-28 14:12
感觉都很麻烦,有更好的设计吗

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

使用道具 举报

回复
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 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子句中的队,那么这个方法就能满足需求。

使用道具 举报

回复

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

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表