在选择过程中如何赋予空值首选项 [英] How to to give preference to null value during select

问题描述

我有一张桌子

Id      value 
1000    null
1000    En
1000    Fr
1000    Es
1001    En
1001    Fr
1001    Es

select查询的输出应如下. (由于1000仅具有空值，因此请选择具有空值的行)

Output of the select query should be as follows. (Since 1000 has a null value only, select the row with null value)

Id      value
1000    null
1001    En
1001    Fr
1001    Es

推荐答案

您可以使用NOT EXISTS和相关子查询来检查ID是否存在NULL.包括这些行以及value为NULL的行.

You can use NOT EXISTS and a correlated subquery to check for the non-existence of a NULL for an ID. Include these rows and also rows where value is NULL.

SELECT t1.id,
       t1.value
       FROM elbat t1
       WHERE NOT EXISTS (SELECT *
                                FROM elbat t2
                                WHERE t2.id = t1.id
                                      AND t2.value IS NULL)
              OR t1.value IS NULL;

这篇关于在选择过程中如何赋予空值首选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！