mysql 切换大小写

发布于 2024-10-21 21:25:02 字数 699 浏览 2 评论 0原文

我有如下所示的查询结构，我想知道是否有一种方法可以使用 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;

默多克想出了一个巧妙的解决方案来解决这个问题，我只需对其进行一项更改，将每个值放入相应的变量中

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;

原文

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.

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪我终i 2024-10-28 21:25:02

您可以通过为每个分数执行一个 case 并返回 1 或 0 来完成此操作。然后将整个结果包装在 SUM（而不是 COUNT）中，实际上为与该 case 匹配的每个实例添加一个。

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

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.

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

回复收藏 0 原文

妄司 2024-10-28 21:25:02

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

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

回复收藏 0 原文

~没有更多了~