php - MySQL查询顺序按“最完整的字段”

Question

我有一个表巫婆有45列，但其中只有几个尚未完成。此表是不断更新和添加等在我的自动完成功能，我想选择这些记录排序最完整的字段（我希望你理解）？
其中一个解决方案是创建另一个字段（“rank”字段）并创建一个php函数来选择*记录并为每个记录给出一个排名。
... 但我想知道是否有一种更简单的方法，仅凭一个订单就可以做到这一点？

Answer 1

据我所知，MySQL没有计算行上非空字段数的函数。
所以我唯一能想到的方法就是使用一个明确的条件：

SELECT * FROM mytable
    ORDER BY (IF( column1 IS NULL, 0, 1)
             +IF( column2 IS NULL, 0, 1)
             ...
             +IF( column45 IS NULL, 0, 1)) DESC;

……这是罪恶的丑恶，但应该做的把戏。
您还可以设计一个触发器来增加一个额外的列“fields\u filled”。触发器使您在 UPDATE上付出代价，45个IFs使您在 SELECT上受到伤害；您必须模拟更方便的方法。
注意，在更新时索引所有字段以加速 SELECT将花费您（并且45个不同的索引可能与选择时的表扫描花费一样多，而不是说索引字段是 VARCHAR）。运行一些测试，但我相信45-如果解决方案可能是最好的整体。
更新：
如果您可以重新设计表结构以使其正常化，那么您可以将字段放在 my_values表中。然后你会有一个“头表”（可能只有一个唯一的ID）和一个“数据表”。空字段根本不存在，然后您可以通过使用 RIGHT JOIN对填充字段进行排序，用“cc>”计算填充字段。这也将大大加快 COUNT()操作，并允许您高效地使用索引。
示例（从表设置到两个标准化表设置）：
假设我们有一组 UPDATE记录。我们将拥有一小部分“强制性”数据，如ID、用户名、密码、电子邮件等；然后我们将拥有一大部分“可选”数据，如昵称、头像、出生日期等。作为第一步，让我们假设所有这些数据都是 Customer（乍一看，与每个列都有自己的数据类型的单表解决方案相比，这似乎是一个限制）。
所以我们有一张桌子，

ID   username    ....
1    jdoe        etc.
2    jqaverage   etc.
3    jkilroy     etc.

然后我们得到了可选的数据表在这里，约翰多伊已经填补了所有领域，乔Q.平均只有两个，基尔罗伊没有（即使他在这里）。

userid  var   val
1       name  John
1       born  Stratford-upon-Avon
1       when  11-07-1974
2       name  Joe Quentin
2       when  09-04-1962

为了在MySQL中重现“单表”输出，我们必须创建一个非常复杂的 varchar和许多 VIEWs。但是，如果我们有一个基于 LEFT JOIN的索引（如果我们使用一个数字常量或一个集合而不是一个varchar来表示 (userid, var)的数据类型，则此视图将非常快速：

CREATE OR REPLACE VIEW usertable AS SELECT users.*,
    names.val AS name // (1)
FROM users
    LEFT JOIN userdata AS names ON ( users.id = names.id AND names.var = 'name') // (2)
;

逻辑模型中的每个字段，例如“name”，将包含在可选数据表中的元组（id，'name'，value）中。
它将在上述查询的第（1）节中生成一行 var格式的行，引用第（2）节中的 <FIELDNAME>s.val AS <FIELDNAME>格式的行。因此，我们可以通过将上述查询的第一个文本行与动态部分1、文本“FROM users”和动态构建的部分2连接起来，动态地构造查询。
完成此操作后，视图上的select与之前完全相同——但现在它们通过连接从两个规范化表中获取数据。

EXPLAIN SELECT * FROM usertable;

将告诉我们，向该设置添加列不会显著降低操作速度，也就是说，该解决方案的伸缩性相当好。
必须修改和更新插入（我们只插入必需的数据，并且只在第一个表中插入）和更新：我们要么更新必需的数据表，要么更新可选数据表的一行。但如果目标行不存在，则必须插入它。
所以我们必须更换

UPDATE usertable SET name = 'John Doe', born = 'New York' WHERE id = 1;

在这种情况下，用一个“upsert”

INSERT INTO userdata VALUES
        ( 1, 'name', 'John Doe' ),
        ( 1, 'born', 'New York' )
    ON DUPLICATE KEY UPDATE val = VALUES(val);

（我们需要一个 LEFT JOIN userdata AS <FIELDNAME>s ON ( users.id = <FIELDNAME>s.id AND <FIELDNAME>s.var = '<FIELDNAME>')才能工作）。
根据行大小和磁盘问题，此更改可能会产生可观的性能提高。
请注意，如果不执行此修改，现有查询不会产生错误-它们将悄然失败。
例如，我们修改了两个用户的名称；一个记录了一个名称，另一个记录了空名称。第一个是修改的，第二个不是。

mysql> SELECT * FROM usertable;
+------+-----------+-------------+------+------+
| id   | username  | name        | born | age  |
+------+-----------+-------------+------+------+
|    1 | jdoe      | John Doe    | NULL | NULL |
|    2 | jqaverage | NULL        | NULL | NULL |
|    3 | jtkilroy  | NULL        | NULL | NULL |
+------+-----------+-------------+------+------+
3 rows in set (0.00 sec)
mysql> UPDATE usertable SET name = 'John Doe II' WHERE username = 'jdoe';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE usertable SET name = 'James T. Kilroy' WHERE username = 'jtkilroy';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> select * from usertable;
+------+-----------+-------------+------+------+
| id   | username  | name        | born | age  |
+------+-----------+-------------+------+------+
|    1 | jdoe      | John Doe II | NULL | NULL |
|    2 | jqaverage | NULL        | NULL | NULL |
|    3 | jtkilroy  | NULL        | NULL | NULL |
+------+-----------+-------------+------+------+
3 rows in set (0.00 sec)

要知道每一行的排名，对于那些有排名的用户，我们只需检索每个id的userdata行数：

SELECT id, COUNT(*) AS rank FROM userdata GROUP BY id

现在要按“填充状态”顺序提取行，我们需要：

SELECT usertable.* FROM usertable
    LEFT JOIN ( SELECT id, COUNT(*) AS rank FROM userdata GROUP BY id ) AS ranking
ON (usertable.id = ranking.id)
ORDER BY rank DESC, id;

UNIQUE INDEX on userdata(id, var)确保了无生气的人也能被检索到，而 ON DUPLICATE KEY的额外排序则确保了具有相同级别的人总是以相同的顺序出现。