按字段排序，仅当它存在时 [英] Ordering by a field, only if it exists

问题描述

我正在尝试获取所有用户，并按另一个表上的字段对他们进行排序，但是该字段并不总是存在?

I'm trying to get all users, and order them by a field on another table, however this field doesn't always exist?

用户 - 持有用户用户元数据 - 保存元数据，特别是权重"，这是我想要订购的.

Users - Holds Users User Meta - Holds metadata, specifically "weight" which is what I'm trying to order by.

更具体的解决方案是自动为它们定义默认权重，但是无论如何我可以让它工作吗?当前工作查询:

A more concrete solution would be to automatically define them a default weight, however is there anyway I can make it work without? Current working query:

SELECT * FROM users u, usermeta um 
WHERE u.ID = um.ID 
  AND u.name LIKE '%search_terms%';

按部分顺序丢失，任何/所有帮助将不胜感激！

Lost on the order by part, any/all help would be appreciated!

推荐答案

如果您有表之间的键关系 (u.ID = um.ID) 并且您想列出所有用户，你可以使用这样的东西在 usermeta 上按 weight 排序.

If you have a key relationship between the tables (u.ID = um.ID) and you want to list all users, you could use something like this to order by weight on usermeta.

LEFT JOIN 允许您保留所有用户，无论他们是否存在于 usermeta 中:

The LEFT JOIN lets you keep ALL users, regardless of their existence in usermeta:

SELECT *, IFNULL(um.weight,9999) as newweight
FROM users u
LEFT JOIN usermeta um on u.ID = um.ID
WHERE u.name LIKE '%search_terms%'
ORDER BY IFNULL(um.weight,9999);

这篇关于按字段排序，仅当它存在时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！