当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL: breaking a tie

问题描述:

I have an SQL database structured like Major League Baseball. It has an American League and National League, which contain divisions, which contain teams, which have Win/Loss records and contain players.

My League table has: |league_name|league_id(primary key)|wild card|

Where wild card is a foreign key teamID that is the best team in a division who doesn't lead that division.

Here is my trigger to update wild card when I update my Z_TEAMS table (where the win/loss records are kept):

UPDATE Z_LEAGUES

SET `Z_LEAGUES`.`Wild Card` = (SELECT teamID

FROM Z_TEAMS

WHERE (teamWins/teamLoss) =

(SELECT MAX(teamWins/teamLoss) FROM Z_TEAMS,

Z_DIVISIONS WHERE Z_TEAMS.divID =

Z_DIVISIONS.divID AND Z_DIVISIONS.leagueID =

Z_LEAGUES.leagueID AND Z_TEAMS.teamID != Z_DIVISIONS.divLeader))

It usually works well and picks the correct team every time Z_TEAMS is updated. However, if I try to update Z_TEAMS so that 2 teams have the same record and both are candidates for wild card, the trigger returns more than one row and restricts the update.

My question is: How can I break this tie? I am open to displaying both teams in the wild card spot, or arbitrarily picking one, or picking by say, alphabetical order. I'm fairly new to SQL and can't figure this out.

网友答案:
UPDATE Z_LEAGUES
SET `Z_LEAGUES`.`Wild Card` = 
(SELECT teamID
 FROM Z_TEAMS
 WHERE (teamWins/teamLoss) = 
    (SELECT MAX(teamWins/teamLoss) 
     FROM Z_TEAMS, Z_DIVISIONS 
     WHERE Z_TEAMS.divID = Z_DIVISIONS.divID 
       AND Z_DIVISIONS.leagueID = Z_LEAGUES.leagueID 
       AND Z_TEAMS.teamID != Z_DIVISIONS.divLeader)
 ORDER BY teamID
 Limit 1)

Added the last 2 lines to your base query.

Limit will limit the output rows, and order by will order them for precitability of which row will be selected.

You may use order by teamID desc to reverse sort.

分享给朋友:
您可能感兴趣的文章:
随机阅读: