matchrecord.sql

Jbuilder 边写的世界杯足球赛成绩统计 ~~新手上路 多多关照!!

/*

//小组赛记录表（组别，球队1，球队2，场次，比赛时间，队1进球数，队2进球数）
//创建赛程安排及战况记录表

CREATE TABLE MatchRecord
(
    group1     nchar(10),
    team1      nchar(20) NOT NULL,
    team2      nchar(20) NOT NULL,
    number     int,
    fixture    datetime,
    goalsfor1  int  default 0,
    goalsfor2  int  default 0,
  PRIMARY KEY (team1,team2),
  FOREIGN KEY(team1) REFERENCES Team(team1),
  FOREIGN KEY(team2) REFERENCES Team(team1))

//小组赛第1轮
  INSERT INTO MatchRecord
  VALUES ('A', '德国','哥斯达黎加',1, '2006-6-10 0:0', 4, 2)

  INSERT INTO MatchRecord
  VALUES ('A', '波兰', '厄瓜多尔', 2, '2006-6-10 3:00', 0, 2)

  INSERT INTO MatchRecord
  VALUES ('B', '英格兰','巴拉圭', 3, '2006-6-10 21:00', 1, 0)

  INSERT INTO MatchRecord
  VALUES ('B', '特立尼达和多巴哥','瑞典', 4, '2006-6-11 0:00', 0, 0)

  INSERT INTO MatchRecord
  VALUES ('C', '阿根廷','科特迪瓦', 5, '2006-6-11 3:00', 2, 1)

  INSERT INTO MatchRecord
  VALUES ('C', '塞黑','荷兰', 6, '2006-6-11 21:00', 0, 1)

  INSERT INTO MatchRecord
  VALUES ('D', '墨西哥','伊朗', 7, '2006-6-12 0:00', 3, 1)

  INSERT INTO MatchRecord
  VALUES ('D', '安哥拉','葡萄牙', 8, '2006-6-12 3:00', 0, 1)

  INSERT INTO MatchRecord
  VALUES ('E', '意大利','加纳', 9, '2006-6-13 3:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('E', '美国','捷克', 10, '2006-6-13 0:00', 0, 3)

  INSERT INTO MatchRecord
  VALUES ('F', '巴西','克罗地亚', 11, '2006-6-14 3:00', 1, 0)

  INSERT INTO MatchRecord
  VALUES ('F', '澳大利亚','日本', 12, '2006-6-12 21:00', 3, 1)

  INSERT INTO MatchRecord
  VALUES ('G', '法国','瑞士', 13, '2006-6-14 0:00', 0, 0)

  INSERT INTO MatchRecord
  VALUES ('G', '韩国','多哥', 14, '2006-6-13 21:00', 2, 1)

  INSERT INTO MatchRecord
  VALUES ('H', '西班牙','乌克兰', 15, '2006-6-14 21:00', 4, 0)

  INSERT INTO MatchRecord
  VALUES ('H', '突尼斯','沙特', 16, '2006-6-15 0:00', 2, 2)

//小组赛第2轮
  INSERT INTO MatchRecord
  VALUES ('A', '德国','波兰',17, '2006-6-15 3:0', 1, 0)

  INSERT INTO MatchRecord
  VALUES ('A', '厄瓜多尔', '哥斯达黎加', 18, '2006-6-15 21:00', 3, 0)

  INSERT INTO MatchRecord
  VALUES ('B', '英格兰','特立尼达和多巴哥', 19, '2006-6-16 0:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('B', '瑞典','巴拉圭', 20, '2006-6-16 3:00', 1, 0)

  INSERT INTO MatchRecord
  VALUES ('C', '阿根廷','塞黑', 21, '2006-6-16 21:00', 6, 0)

  INSERT INTO MatchRecord
  VALUES ('C', '荷兰','科特迪瓦', 22, '2006-6-17 0:00', 2, 1)

  INSERT INTO MatchRecord
  VALUES ('D', '墨西哥','安哥拉', 23, '2006-6-17 3:00', 0, 0)

  INSERT INTO MatchRecord
  VALUES ('D', '葡萄牙','伊朗', 24, '2006-6-17 21:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('E', '意大利','美国', 25, '2006-6-18 3:00', 1, 1)

  INSERT INTO MatchRecord
  VALUES ('E', '捷克','加纳', 26, '2006-6-18 0:00', 0, 2)

  INSERT INTO MatchRecord
  VALUES ('F', '巴西','澳大利亚', 27, '2006-6-19 0:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('F', '日本','克罗地亚', 28, '2006-6-18 21:00', 0, 0)

  INSERT INTO MatchRecord
  VALUES ('G', '法国','韩国', 29, '2006-6-19 3:00', 1, 1)

  INSERT INTO MatchRecord
  VALUES ('G', '多哥','瑞士', 30, '2006-6-19 21:00', 0, 2)

  INSERT INTO MatchRecord
  VALUES ('H', '西班牙','突尼斯', 31, '2006-6-20 3:00', 3, 1)

  INSERT INTO MatchRecord
  VALUES ('H', '沙特','乌克兰', 32, '2006-6-20 0:00', 0, 4)

//小组赛第3轮
  INSERT INTO MatchRecord
  VALUES ('A', '厄瓜多尔','德国',33, '2006-6-20 22:0', 0, 3)

  INSERT INTO MatchRecord
  VALUES ('A', '哥斯达黎加', '波兰', 34, '2006-6-20 22:00', 1, 2)

  INSERT INTO MatchRecord
  VALUES ('B', '瑞典','英格兰', 35, '2006-6-21 3:00', 2, 2)

  INSERT INTO MatchRecord
  VALUES ('B', '巴拉圭','特立尼达和多巴哥', 36, '2006-6-21 3:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('C', '荷兰','阿根廷', 37, '2006-6-22 3:00', 0, 0)

  INSERT INTO MatchRecord
  VALUES ('C', '科特迪瓦','塞黑', 38, '2006-6-22 3:00', 3, 2)

  INSERT INTO MatchRecord
  VALUES ('D', '葡萄牙','墨西哥', 39, '2006-6-21 22:00', 2, 1)

  INSERT INTO MatchRecord
  VALUES ('D', '伊朗','安哥拉', 40, '2006-6-21 22:00', 1, 1)

  INSERT INTO MatchRecord
  VALUES ('E', '捷克','意大利', 41, '2006-6-22 22:00', 0, 2)

  INSERT INTO MatchRecord
  VALUES ('E', '加纳','美国', 42, '2006-6-22 22:00', 2, 1)

  INSERT INTO MatchRecord
  VALUES ('F', '日本','巴西', 43, '2006-6-23 3:00', 1, 4)

  INSERT INTO MatchRecord
  VALUES ('F', '克罗地亚','澳大利亚', 44, '2006-6-23 3:00', 2, 2)

  INSERT INTO MatchRecord
  VALUES ('G', '多哥','法国', 45, '2006-6-24 3:00', 0, 2)

  INSERT INTO MatchRecord
  VALUES ('G', '瑞士','韩国', 46, '2006-6-24 3:00', 2, 0)

  INSERT INTO MatchRecord
  VALUES ('H', '沙特','西班牙', 47, '2006-6-23 22:00', 0, 1)

  INSERT INTO MatchRecord
  VALUES ('H', '乌克兰','突尼斯', 48, '2006-6-23 22:00', 1, 0)


SELECT *
  FROM MatchRecord
 WHERE group1='A'
 ORDER BY number

DELETE FROM MatchRecord

*/