在MySQL的选择查询中使用CASE，WHEN，THEN，END [英] Using CASE, WHEN, THEN, END in a select query with MySQL

问题描述

我正在与棒球相关的网站上工作.我有一张桌子，上面有两个棒球队的击球阵容:

I'm working on a baseball related website. I have a table with a batting lineup for two baseball teams:

+----+----------+--------------+--------+
| id | playerId | battingOrder | active |
+----+----------+--------------+--------+

击球顺序是1到20之间的整数.这对应于以下逻辑:

Batting order is an integer between 1 and 20. This corresponds to the following logic:

1. 击球顺序1-9—客队阵容
2. 击球顺序10—客队投手
3. 击球顺序11-19—主队阵容
4. 第20个击球顺序—主队投手

活动字段是tinyint 0或1，表示土堆上的投手和盘子上的击球手.

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.

我需要编写一个查询，该查询返回与battingOrder中的 next 击球手相对应的主队队员的行. (发生在主动击球手的击球顺序之后的那一个)

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)

示例:

1. 如果处于battingOrder 13中的玩家处于活动状态，则查询应以batting order 14返回玩家.
2. 如果处于击球顺序19中的玩家处于活动状态，则查询应以击球顺序11返回该玩家(阵容循环回到该团队的第一位玩家).

我以前从未使用过CASE查询，但是我想到了以下内容:

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

这篇关于在MySQL的选择查询中使用CASE，WHEN，THEN，END的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！