MySQL mysql切换案例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5282637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql switch case
提问by volting
I have a query structure like below, Im wondering if there is a way to the write the select queries as one using CASE statements or by some other means so that the values get inserted into the appropriate variables based on their values.
我有一个像下面这样的查询结构，我想知道是否有一种方法可以使用 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 came up with a neat solution to this problem, I just had to make one change to it to put each of values in to there respective variables
默多克为这个问题提出了一个巧妙的解决方案，我只需要对其进行一次更改，将每个值放入相应的变量中
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;
回答by 3urdoch
You can do this by doing a case for each score and returning 1 or 0. Then wrapping the whole thing in a SUM (not a COUNT) in effect adding one for each instance that matches the case.
您可以通过为每个分数做一个案例并返回 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
回答by Scott
DECLARE tpasses INT;
DECLARE tfails INT;
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
INTO tpasses, tfails
FROM scores
