mysql - 带 WHERE 子句的 SQL 查询 ORDER BY

Question

我有 2 张 table 。第一个包含订阅者信息:

--------------------------------------------------------
| id |    first_name    |   last_name  |     mail      |
--------------------------------------------------------
| 1  |       Jean       |    Bono      | jean@bono.com |
--------------------------------------------------------
| 2  |       Paul       |     Dodu     | paule@dodu.com|
--------------------------------------------------------

第二个带有自定义字段:

------------------------------------------------------
| id | subscriber_id | custom_field_id |   value     |
------------------------------------------------------
| 1  |       1       |        1        | Photographer  |
------------------------------------------------------
| 2  |       1       |        2        | 00000000    |
------------------------------------------------------
| 3  |       2       |        1        | Journalism|
------------------------------------------------------
| 4  |       2       |        2        | 00000000    |
------------------------------------------------------

我想按 id = 1 的值或字符串值(不是 int)对我的订阅者进行排序。

例如，首先是“新闻”，其次是“摄影师”

这是我的 SQL 查询(测试):

SELECT sub.*, cf.* 
FROM subscribers AS sub 
JOIN subscriber_custom_field AS cf 
ON cf.subscriber_id = sub.id 
WHERE sub.status = 'subscribed' 
ORDER BY cf.value

但是这个 SQL 查询错误导调用话号码和字符串混合......

有什么想法吗？

谢谢!

Answer 1

如果您想按 custom_field_id = 1 值进行排序，则必须在该字段上添加 WHERE 条件，但您只会链接自定义字段 = 1 的行:

SELECT sub.*, cf.value as type
FROM subscribers AS sub 
JOIN subscriber_custom_field AS cf 
ON cf.subscriber_id = sub.id 
WHERE sub.status = 'subscribed' 
AND cf.custom_field_id = 1
ORDER BY cf.value

如果您还需要选择其他自定义字段，我认为您必须完全更改 SQL，使用子查询转换列中的行，否则您将为每个自定义字段获取一行:

SELECT sub.*, 
       (SELECT cf.value FROM subscriber_custom_field WHERE cf.subscriber_id = subscriber_id AND custom_field_id = 1) AS type,
       (SELECT cf.value FROM subscriber_custom_field WHERE cf.subscriber_id = subscriber_id AND custom_field_id = 2 LIMIT 1) AS phone
FROM subscribers AS sub 
JOIN subscriber_custom_field AS cf 
ON cf.subscriber_id = sub.id 
WHERE sub.status = 'subscribed' 
ORDER BY (SELECT cf.value FROM subscriber_custom_field WHERE cf.subscriber_id = subscriber_id AND custom_field_id = 1)