MySQL 查询中更复杂的 IF 语句

问题描述

一周前我问了一个相关的问题，但这是我面临的另一个问题.我觉得自己是个菜鸟，但我想问问和学习总比不学习好.

I asked a related question a week ago, but here's another problem I'm facing. I feel like such a noob, but I guess it's better to ask and learn than never learn.

这是我的三个表:

战士桌

fighter_id, name

事件表

event_id, event_name, event_date

战斗表

fight_id, fighter_a, fighter_b, winner, method, event

所以在 Fights 表中，fighter_a、fighter_b 和获胜者是与 Fighters 表中的 fighter_id 对应的整数.

So in the fights table, fighter_a, fighter_b, and winner are integers that correspond to fighter_id in the Fighters table.

我基本上是在基于 fighter_id 检索数据的页面上.

I'm basically on a page retrieving data based on fighter_id.

我正在尝试为该战士的每场战斗创建行，其中包括他的对手的姓名、结果(获胜、失败、平局或 nc)、方法、事件名称和事件日期.如果是平局或不竞赛，则在方法栏中会显示平局或不竞赛，而获胜者将为NULL.

I'm trying to create rows with each fight of that fighter that includes his opponent's name, result (win, loss, draw, or nc), method, event_name, and event_date. If it's a Draw or No Contest, it'll say Draw or No Contest in Method column, while winner will be NULL.

我正在查看这些 MySQL IF 语句，并创建了用于确定战斗机是赢、输、平还是不比赛的适当标准.这些语句似乎不能用在 PHP mysql_query 中，但至少它可以让您了解标准.我知道我必须将这 3 个表内部连接在一起，但我不确定如何确定赢家/输家/等...因为 fighter_a 或 fighter_b 可能是赢家，或者两者都不是.我不知道如何在 MySQL 查询中处理这些 IF 语句.

I was looking at these MySQL IF statements, and created what would be the appropriate criteria for determining whether a fighter won, lost, got a draw, or no contest. It doesn't seem like these statements can be used in a PHP mysql_query, but at least it gives you an idea of criteria. I know I have to inner join these 3 tables together, but I'm not sure how to determine winner/loser/etc... because fighter_a or fighter_b can be a winner, or neither could be. I don't know how to approach these IF statements in MySQL query.

IF winner IS NOT NULL AND winner=fighter_id THEN SET record='win';
ELSEIF winner IS NOT NULL AND winner<>fighter_id THEN SET record='loss';
ELSEIF winner IS NULL AND method='Draw' THEN SET record='draw';
ELSEIF winner IS NULL AND method='No Contest' THEN SET record='no contest';
ELSE SET record='';
ELSE IF;

我试图在一个看起来像这样的表格中得到一个结果:http://en.wikipedia.org/wiki/Fedor_Emelianenko#Mixed_martial_arts_record

I'm trying to get a result in a table that look something like this:http://en.wikipedia.org/wiki/Fedor_Emelianenko#Mixed_martial_arts_record

推荐答案

如果我理解正确，我相信您会想要使用 CASE 语句来确定结果.战士将需要加入，类似于:

If I understand you correctly, I believe you would want to use a CASE statement to determine the result. The fighters will require a join, similar to this:

select
    fight_id,
    CASE
      WHEN winner is not null and winner=fighter_id then 'win'
      WHEN winner is not null and winner<>fighter_id then 'loss'
      WHEN winner is null and method='Draw' then 'draw'
      WHEN winner is null and method = 'No Contest' then 'no contest'
      ELSE ''
    END as match_result,
    participant.name 'participant',
    opponent.name 'opponent'
FROM fights
    INNER JOIN fighters as participant on participant.fighter_id = fights.fighter_a
    INNER JOIN fighters as oppoent on opponent.fighter_id = fights.fighter_b

这篇关于MySQL 查询中更复杂的 IF 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！