连续计算NULL数 [英] Count number of NULLs in a row

问题描述

有没有一种方法可以获取表示行中NULL字段数的列?这将在SELECT语句中.

Is there a way to get a column indicating the number of NULL fields in a row? This would be within a SELECT statement.

例如:

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

更新:我想要这个查询，以便我可以根据给定书的有多少会员销售进行排序.因此，无论哪个会员，拥有3个会员的排名都会比拥有2个会员的排名高.我的数据库中大约有七个会员，并且会不断增长.因此，任何要求指定每个会员字段的查询都可能太长了

UPDATE: I want this query so I can sort based on how many Affiliates sales there are of a given Book. So having 3 affiliates would be sorted higher than having 2, regardless of which ones. There are about seven affiliates in my database, and that's subject to grow. So any query requiring that each Affiliate field be specified would probably be too long

表格:

Affiliates_Cache-主键是Affiliate_ISBN，具有该书在各个分支机构上的价格(如果不存在，则为NULL). Affiliates_Cache是​​我要计算NULL数量的那个

Affiliates_Cache - Primary key is Affiliate_ISBN, has the prices of the book on various affiliates (NULL if its not available). Affiliates_Cache is the one where i want to count the number of NULLs

推荐答案

我不确定是否有更整洁的方法，但这应该可行:

I'm not sure if there are neater methods, but this should work:

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)

---

更新:在更新的问题之外:

---

UPDATE: Further to the updated question:

如果表中的列看起来像affiliate_1，affiliate_2等，这不是一个好主意，因为您会将数据与元数据混合.通常，建议的解决方法是对用户与关联关系使用另一个依赖表，如以下示例所示:

If you have columns in your table that look like affiliate_1, affiliate_2, etc, this is rarely a good idea as you would be mixing data with the metadata. In general, a recommended fix is to use another dependent table for the users-to-affiliates relationships, as in the following example:

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表进行排序，如下所示:

Then sorting the users table by the number of affiliates will look something like this:

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;

优点很多，但最重要的是，它使上述查询更易于编写，并且足够灵活，可以与任意数量的关联公司(不受分配的列数限制)一起使用.

The advantages are plenty, but most importantly it makes queries such as the above easy to write, and flexible enough to work with any number of affiliates (an not limited by the number of columns you allocated).

这篇关于连续计算NULL数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！