MySQL：选择一个额外的列，根据 IF 语句对另一列进行分类

Question

假设这个命令：SELECT sessionID, SessionLength FROM mytable;

生成这个表：

+-----------+---------------+
| sessionID | SessionLength |
+-----------+---------------+
|         1 | 00:20:31      | 
|         2 | 00:19:54      | 
|         3 | 00:04:01      | 

  ...
|      7979 | 00:00:15      | 
|      7980 | 00:00:00      | 
|      7981 | 00:00:00      | 
+-----------+---------------+
7981 rows in set (0.92 sec)

但我想生成一个像这样的表：

+-----------+---------------+--------+
| sessionID | SessionLength | Size   |
+-----------+---------------+--------+
|         1 | 00:20:31      | BIG    |
|         2 | 00:19:54      | BIG    |
|         3 | 00:04:01      | MEDIUM |

  ...
|      7979 | 00:00:15      | SMALL  |
|      7980 | 00:00:00      | SMALL  |
|      7981 | 00:00:00      | SMALL  |
+-----------+---------------+--------+
7981 rows in set (0.92 sec)

• 当SessionLength > 时，某物是大。 10
• 当 SessionLength <= 10 AND SessionLength >=1 时，某物为中；
• 当其 时，某物为小会话长度> 1

从概念上讲，我想做的是：

SELECT 
   sessionID, 
   SessionLength,
   (SessionLength > 10 ? "BIG" : (SessionLength < 1 : "SMALL" : "MEDIUM"))
FROM mytable;

有没有一种简单的方法可以做到这一点？

Answer 1

是的，

SELECT
   sessionID, SessionLength,
   CASE WHEN SessionLength > 10 THEN 'BIG'
        WHEN SessionLength < 1 THEN 'SMALL'
        ELSE 'MEDIUM'
   END 
FROM mytable;

Answer 2

SELECT
    sessionID,
    SessionLength,
    IF( SessionLength > 10, "BIG",
        IF( SessionLength < 1, "SMALL", "MEDIUM")) AS Size
FROM mytable;

华泰