mysql get count(column)if first select query return no result

hm2xizp9 于 2023-10-15 发布在 Mysql

关注(0)|答案(1)|浏览(123)

如果第一个查询没有返回结果，我想执行第二个查询。我想得到那些ID年龄> 20。如果没有结果，则希望获取年龄< 20的id的数量。
表中：

id   name   gender   age
    1   'Ryan'  'M'     30
    2  'Joanna' 'F'     10

查询

(SELECT id FROM students WHERE age > 20
ORDER BY id desc limit 1)

UNION ALL

SELECT count(id) FROM students WHERE age < 20 AND 
NOT EXISTS (SELECT * FROM students WHERE age > 20  ORDER BY id desc limit 1)

结果：

id 
    1 
    0

它应该只返回1，但返回1和0。
如果将ryan age改为10，则返回2，这是正确的。
友情链接：query

mysql

来源：https://stackoverflow.com/questions/77219462/mysql-get-countcolumn-if-first-select-query-return-no-result

1条答案

按热度按时间

amrnrhlw1#

给你正如您在案例1中所要求的：ID年龄> 20限制1和情况2：年龄< 20的计数仅当没有来自案例1的结果集时。
这应该可以！！！

-- fetch some values
With CTE_above20 AS
(   SELECT 
        'Descending Top 1 id above 20' Description, id 
    FROM students WHERE age > 20 ORDER BY id desc limit 1 
), CTE_under20 AS
(   SELECT
        'Count of ids age below 20' Description
        , CASE WHEN  (SELECT count(1) FROM CTE_above20 LIMIT 1) > 0 THEN NULL ELSE count(id) END as id 
    FROM students WHERE age < 20 )
SELECT * FROM CTE_above20
UNION ALL
SELECT * FROM CTE_under20 
WHERE id is NOT  NULL

结果1：插入年龄> 20的语句和年龄< 20的语句

-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',30);
INSERT INTO students VALUES (2, 'Joanna', 'F',40);
INSERT INTO students VALUES (3, 'Ryan', 'M',1);

 /*  Output 1
        Description                        Id
        --------------------------------------
        Descending Top 1 id above 20        2
        
*/

结果2所有年龄< 20岁的插入物

-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',10);
INSERT INTO students VALUES (2, 'Joanna', 'F',10);
INSERT INTO students VALUES (3, 'Ryan', 'M',1);

/*

Description                 Id
------------------------------
Count of ids age below 20   3
*/

赞(0）回复(0）举报 2023-10-15

我来回答

mysql get count(column)if first select query return no result

1条答案

相关问题

热门标签

最新问答