MySQL IF 子句问题

Question

mysql> SELECT
   -> IF(status='EndSP',reportId,'') as 'reportId_EndSP'
   -> FROM T_Name LIMIT 10;
+--------------+
| reportId_EndSP |
+--------------+
|                |
|                |
| 270241         |
|                |
| 270242         |
|                |
|                |
| 270244         |
|                |
|                |
+--------------+
10 rows in set (0.00 sec)

But i need to return only the row where status = 'EndSP' 

我不需要执行 else 子句。

我可以简单地通过写作来实现这一点

SELECT reportId FROM T_Name where status='EndSP';

但我需要使用 if 或 case 来完成。

编辑/更新

我的实际查询看起来像

SELECT
   -> IF(status='EndSP',reportId,'') as 'reportId_EndSP',
   -> IF(status='EndSP',createdTS,'') as 'createdTS_EndSP',
   -> IF(status='BeginSP',reportId,'') as 'reportId_BeginSP',
   -> IF(status='BeginSP',createdTS,'') as 'createdTS_BeginSP'
   -> FROM T_Name HAVING reportId_EndSP!='' AND reportId_BeginSP!='' LIMIT  10;

Answer 1

怎么样:

SELECT * FROM (
   SELECT
      IF(status='EndSP',reportId,'') as reportId_EndSP
      FROM T_Name) a
WHERE reportId_EndSP != '' LIMIT 10;

演示 http://sqlfiddle.com/#!2/d1167/8

或单选版

SELECT
   IF(status='EndSP',reportId,'') as reportId_EndSP
   FROM T_Name
HAVING reportId_EndSP != '' LIMIT 10;

演示 http://sqlfiddle.com/#!2/d1167/9