mysql 查询时 case,在MySQL的选择查询中使用CASE，WHEN，THEN，END

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:

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

The active field is a tinyint 0 or 1, representing the pitcher on the mound and the batter on the plate.

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 next batter in the battingOrder. (the one that that occurs after the active batter's 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).

I've never used a CASE query before, but I came up with the following:

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.

解决方案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