目录
如果空表如何输出null
应用背景:(力扣题:力扣(LeetCode)官网 - 全球极客挚爱的技术成长平台)
在题目中会出现这样的问题,当我们查询的结果为空时,我们需要输出null,
下面讲解输出null值得两种方法
首先总结表格
ps:空表示没有没有任何输出,null表示输出为null
例子
CREATE TABLE IF NOT EXISTS my_numbers (num INT);
TRUNCATE TABLE my_numbers
INSERT INTO my_numbers (num) VALUES ('8');
INSERT INTO my_numbers (num) VALUES ('8');
INSERT INTO my_numbers (num) VALUES ('3');
INSERT INTO my_numbers (num) VALUES ('3');
INSERT INTO my_numbers (num) VALUES ('4');
INSERT INTO my_numbers (num) VALUES ('4');
INSERT INTO my_numbers (num) VALUES ('5');
INSERT INTO my_numbers (num) VALUES ('5');
然后正常查询
select num
FROM (
SELECT num
FROM my_numbers
GROUP BY num
HAVING COUNT(num)=1
ORDER BY num DESC
LIMIT 0,1
) t1
结果为空
用聚合函数优化
SELECT SUM(num) sum_num, AVG(num) avg_num, MAX(num) max_num,MIN(num) min_num
FROM (
SELECT num
FROM my_numbers
GROUP BY num
HAVING COUNT(num)=1
ORDER BY num DESC
LIMIT 0,1
) t1
用select查询 不加from
select num
(
SELECT num
FROM my_numbers
GROUP BY num
HAVING COUNT(num)=1
ORDER BY num DESC
LIMIT 0,1
) result
总结
可以使用聚合函数进行空值null值的转换,具体的聚合函数包括SUM/AVG/MAX/MIN
可以使用select语句进行转换
但空值应直接写在select中而非from中 limit语句无法出现新的null值
where和having同样无法出现新的null值