mysql开关大小写

Question

我有一个如下所示的查询结构，我想知道是否有一种方法可以使用 CASE 语句或通过其他方式将选择查询编写为一个查询，以便根据值将值插入到适当的变量中。

DECLARE passes INT;
DECLARE fails INT;
..

SELECT count(score)
INTO passes
  FROM scores
 WHERE score >= 40;

SELECT count(score)
INTO fails
  FROM scores
 WHERE score < 40;

---

Murdoch 想出了一个解决这个问题的巧妙方法，我只需要对其进行一个更改，将每个值放入相应的变量

SELECT * 
INTO   passes, fails 
FROM  (SELECT SUM(CASE 
                    WHEN score >= 40 THEN 1 
                    ELSE 0 
                  END) AS _passes, 
              SUM(CASE 
                    WHEN score < 40 THEN 1 
                    ELSE 0 
                  END) AS _fails 
       FROM   scores) AS x; 

Answer 1

您可以通过为每个分数做一个案例并返回 1 或 0 来做到这一点。然后将整个事情包装在一个 SUM(而不是一个 COUNT)中，实际上为每个与案例匹配的实例添加一个。

SELECT 
SUM(CASE WHEN score >= 40 THEN 1 ELSE 0 END) AS passes, 
SUM(CASE WHEN score < 40 THEN 1 ELSE 0 END) AS fails 
FROM scores