在 MySQL 的选择查询中使用 CASE、WHEN、THEN、END
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5656440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using CASE, WHEN, THEN, END in a select query with MySQL
提问by Stephen
I'm working on a baseball related website. I have a table with a batting lineup for two baseball teams:
我在一个棒球相关的网站上工作。我有一张桌子，上面有两个棒球队的击球阵容：
+----+----------+--------------+--------+
| id | playerId | battingOrder | active |
+----+----------+--------------+--------+
Batting order is an integer between 1 and 20. This corresponds to the following logic:
击球顺序是 1 到 20 之间的整数。 这对应于以下逻辑：
- Batting Order 1-9 — Away Team Lineup
- Batting Order 10 — Away Team Pitcher
- Batting Order 11-19 — Home Team Lineup
- Batting Order 20 — Home Team Pitcher
- 击球顺序 1-9 — 客队阵容
- 击球令 10 — 客队投手
- 击球顺序 11-19 — 主队阵容
- 击球令 20 — 主队投手
The active field is a tinyint 0 or 1, representing the pitcher on the mound and the batter on the plate.
active 字段是一个 tinyint 0 或 1，代表土墩上的投手和盘子上的击球手。
Known Fact: There will always be one active pitcher from one team and one active batter from the opposite team.
已知事实：总会有来自一支球队的一名现役投手和来自另一支球队的一名现役击球手。
I need to write a query that returns a row for a home team player that corresponds to the nextbatter in the battingOrder. (the one that that occurs afterthe active batter's battingOrder)
我需要编写一个查询，为与 battingOrder 中的下一个击球手相对应的主队球员返回一行。（在活跃击球手的 battingOrder之后发生的那个）
Example:
例子：
- If the player in battingOrder 13 is active, the query should return the player in batting order 14.
- If the player in battingOrder 19 is active, the query should return the player in batting order 11 (the lineup loops back to the first player for the team).
- 如果 battingOrder 13 中的球员处于活动状态，则查询应返回 battingOrder 14 中的球员。
- 如果 battingOrder 19 中的球员处于活动状态，则查询应返回 battingOrder 11 中的球员（阵容循环回到球队的第一个球员）。
I've never used a CASE query before, but I came up with the following:
我以前从未使用过 CASE 查询，但我想出了以下内容：
SELECT *
FROM lineups
WHERE battingOrder =
CASE (
SELECT battingOrder
FROM lineups
WHERE battingOrder > 10 AND active = 1
LIMIT 1
)
WHEN 11 THEN 12
WHEN 12 THEN 13
WHEN 13 THEN 14
WHEN 14 THEN 15
WHEN 15 THEN 16
WHEN 16 THEN 17
WHEN 17 THEN 18
WHEN 18 THEN 19
WHEN 19 THEN 11
END
LIMIT 1;
It seems to work, but what edge cases and/or pitfalls have I walked into? Is this efficient? I'm particulary interested in a solution to my problem that does not use a nested query.
它似乎有效，但是我遇到了哪些边缘情况和/或陷阱？这有效率吗？我对不使用嵌套查询的问题的解决方案特别感兴趣。
回答by Thomas
Select LNext.player As NextPlayer
From lineups As L
Left Join lineups As LNext
On LNext.BattingOrder Between 11 And 20
And LNext.BattingOrder = Case
When L.BattingOrder = 19 Then 11
Else L.BattingOrder + 1
End
Where L.battingOrder Between 11 And 20
And L.active = 1
In fact, you could make it handle both home and away like so:
事实上，你可以像这样让它在主场和客场都可以处理：
Select LNext.player As NextPlayer
From lineups As L
Left Join lineups As LNext
On LNext.BattingOrder = Case
When L.BattingOrder = 19 Then 11
When L.BattingOrder = 9 Then 1
Else L.BattingOrder + 1
End
Where L.active = 1
