计算一行中 NULL 的数量

Question

有没有办法获取指示行中 NULL 字段数量的列？这将在 SELECT 语句中。

例如：

Field1  Field2  Num_Null
-----------------------
NULL     "A"      1

更新：我想要这个查询，以便我可以根据给定书籍的附属销售数量进行排序。因此，无论是哪一个，拥有 3 个附属机构的排序都会高于拥有 2 个附属机构的排序。我的数据库中有大约七个附属机构，而且这个数字还会增长。因此，任何要求指定每个附属字段的查询都可能太长

表：

Affiliates_Cache - 主键是 Affiliate_ISBN，包含各个附属机构的图书价格（如果不可用则为 NULL）。 Affiliates_Cache 是我想要计算 NULL 数量的缓存

Answer 1

我不确定是否有更简洁的方法，但这应该有效：

SELECT Field1, Field2, ISNULL(Field1) + ISNULL(Field2) Num_Null
FROM   YourTable;

测试用例：

CREATE TABLE YourTable (Field1 varchar(10), Field2 varchar(10));

INSERT INTO YourTable VALUES (NULL, 'A');
INSERT INTO YourTable VALUES ('B', 'C');
INSERT INTO YourTable VALUES ('B', NULL);
INSERT INTO YourTable VALUES (NULL, NULL);

结果：

+--------+--------+----------+
| Field1 | Field2 | Num_Null |
+--------+--------+----------+
| NULL   | A      |        1 |
| B      | C      |        0 |
| B      | NULL   |        1 |
| NULL   | NULL   |        2 |
+--------+--------+----------+
4 rows in set (0.00 sec)

---

更新：进一步更新的问题：

如果表中的列看起来像 affiliate_1、affiliate_2 等，这很少是一个好主意，因为您会将数据与元数据混合。一般来说，建议的修复方法是使用另一个依赖表来表示用户与附属机构的关系，如下例所示：

CREATE TABLE users (
   user_id int, 
   user_name varchar(100),
   PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE users_affiliates (
   user_id int, 
   affiliate_name varchar(100),
   PRIMARY KEY (user_id, affiliate_name),
   FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=INNODB;

然后按附属机构数量对 users 表进行排序，如下所示：

SELECT    u.*, d_tb.num_aff
FROM      users
JOIN      (
             SELECT   user_id, COUNT(*) num_aff
             FROM     users_affiliates
             GROUP BY user_id
          ) d_tb ON (d_tb.user_id = u.user_id)
ORDER BY  d_tb.num_aff DESC;

优点很多，但最重要的是，它使上述查询易于编写，并且足够灵活，可以与任意数量的附属机构一起使用（不受您分配的列数的限制）。

Answer 2

这个查询怎么样？ （参考大牛给出的测试用例。）

SELECT Field1, Field2, (2 - (COUNT(ALL Field1)+COUNT(ALL Field2)))  Num_Null
FROM   @YourTable
GROUP BY Field1, Field2

Answer 3

保持简单和标准：

SELECT Field1, Field2, 
       CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END
       AS Num__Null
  FROM YourTable;

完整的测试用例：

WITH YourTable (Field1, Field2)
     AS 
     (
      SELECT CAST(Field1 AS VARCHAR(10)), 
             CAST(Field2 AS VARCHAR(10))
        FROM (
              VALUES (NULL, 'A'),
                     ('B', 'C'),
                     ('B', NULL),
                     (NULL, NULL)
             ) AS YourTable (Field1, Field2)
     )
SELECT Field1, Field2, 
       CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END
       AS Num__Null
  FROM YourTable;