teamscore.sql

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

/*
//小组赛成绩（排名, 组别，球队，国旗，场次，胜，平，负，进球，失球，净胜球，积分）
CREATE TABLE TeamScore
(   rank  int,
    group1  char(10),
    team1  nchar(20)  NOT NULL  UNIQUE,
    jack  image,
    completed  int  DEFAULT 0, 
    win  int  DEFAULT 0, 
    tie   int  DEFAULT 0, 
    loss  int  DEFAULT 0, 
    goalsfor  int  DEFAULT 0, 
    goalsagainst  int  DEFAULT 0,
    netvalue  int  DEFAULT 0,
    score  int  DEFAULT 0, 
  PRIMARY KEY (team1),
  FOREIGN KEY(team1) REFERENCES Team(team1))


  INSERT INTO TeamScore
  VALUES (1, 'A', '德国', null, 3, 3, 0, 0, 8, 2, 6, 9)

  INSERT INTO TeamScore
  VALUES (2, 'A', '厄瓜多尔', null, 3, 2, 0, 1, 5, 3, 2, 6)

  INSERT INTO TeamScore
  VALUES (3, 'A', '波兰', null, 3, 1, 0, 2, 2, 4, -2, 3)

  INSERT INTO TeamScore
  VALUES (4, 'A', '哥斯达黎加', null, 3, 0, 0, 3, 3, 9, -6, 0)


  INSERT INTO TeamScore
  VALUES (1, 'B', '英格兰', null, 3, 2, 1, 0, 5, 2, 3, 7)

  INSERT INTO TeamScore
  VALUES (2, 'B', '瑞典', null, 3, 1, 2, 0, 3, 2, 1, 5)

  INSERT INTO TeamScore
  VALUES (3, 'B', '巴拉圭', null, 3, 1, 0, 2, 2, 2, 0, 3)

  INSERT INTO TeamScore
  VALUES (4, 'B', '特立尼达和多巴哥', null, 3, 0, 1, 2, 0, 4, -4, 1)

  INSERT INTO TeamScore
  VALUES (1, 'C', '阿根廷', null, 3, 2, 1, 0, 8, 1, 7, 7)

  INSERT INTO TeamScore
  VALUES (2, 'C', '荷兰', null, 3, 2, 1, 0, 3, 1, 2, 7)

  INSERT INTO TeamScore
  VALUES (3, 'C', '科特迪瓦', null, 3, 1, 0, 2, 5, 6, -1, 3)

  INSERT INTO TeamScore
  VALUES (4, 'C', '塞黑', null, 3, 0, 0, 3, 2, 10, -8, 0)

  INSERT INTO TeamScore
  VALUES (1, 'D', '葡萄牙', null, 3, 3, 0, 0, 5, 1, 4, 9)

  INSERT INTO TeamScore
  VALUES (2, 'D', '墨西哥', null, 3, 1, 1, 1, 4, 3, 1, 4)

  INSERT INTO TeamScore
  VALUES (3, 'D', '安哥拉', null, 3, 0, 2, 1, 1, 2, -1, 2)

  INSERT INTO TeamScore
  VALUES (4, 'D', '伊朗', null, 3, 0, 1, 2, 2, 6, -4, 1)

  INSERT INTO TeamScore
  VALUES (1, 'E', '意大利', null, 3, 2, 1, 0, 5, 1, 4, 7)

  INSERT INTO TeamScore
  VALUES (2, 'E', '加纳', null, 3, 2, 0, 1, 4, 3, 1, 6)

  INSERT INTO TeamScore
  VALUES (3, 'E', '捷克', null, 3, 1, 0, 2, 3, 4, -1, 3)

  INSERT INTO TeamScore
  VALUES (4, 'E', '美国', null, 3, 0, 1, 2, 2, 6, -4, 1)

  INSERT INTO TeamScore
  VALUES (1, 'F', '巴西', null, 3, 3, 0, 0, 7, 1, 6, 9)

  INSERT INTO TeamScore
  VALUES (2, 'F', '澳大利亚', null, 3, 1, 1, 1, 5, 5, 0, 4)

  INSERT INTO TeamScore
  VALUES (3, 'F', '克罗地亚', null, 3, 0, 2, 1, 2, 3, -1, 2)

  INSERT INTO TeamScore
  VALUES (4, 'F', '日本', null, 3,  0, 1, 2, 2, 7, -5, 1)

  INSERT INTO TeamScore
  VALUES (1, 'G', '瑞士', null, 3, 2, 1, 0, 4, 0, 4, 7)

  INSERT INTO TeamScore
  VALUES (2, 'G', '法国', null, 3, 1, 2, 0, 3, 1, 2, 5)

  INSERT INTO TeamScore
  VALUES (3, 'G', '韩国', null, 3, 1, 1, 1, 3, 4, -1, 4)

  INSERT INTO TeamScore
  VALUES (4, 'G', '多哥', null, 3, 0, 0, 3, 1, 6, -5, 0)


  INSERT INTO TeamScore
  VALUES (1, 'H', '西班牙', null, 3, 3, 0, 0, 8, 1, 7, 9)

  INSERT INTO TeamScore
  VALUES (2, 'H', '乌克兰', null, 3, 2, 0, 1, 5, 4, 1, 6)

  INSERT INTO TeamScore
  VALUES (3, 'H', '突尼斯', null, 3, 0, 1, 2, 3, 6, -3, 1)

  INSERT INTO TeamScore
  VALUES (4, 'H', '沙特', null, 3, 0, 1, 2, 2, 7, -5, 1)


SELECT *
  FROM TeamScore
 WHERE group1='A'
 ORDER BY rank
 
SELECT *
  FROM TeamScore
 WHERE group1='A'
 ORDER BY '#1'
 
 //SQL Server不支持#1 ，不支持//注释
*/