Mysql --如何根据列中的值对结果进行排序？

Question

我有两个数据库表customers，其中包含有关客户的数据，该方案如下：

mysql> SELECT * FROM customers;

customer_id created_at              partner_id
1           "2019-08-20 09:17:58"   cats
2           "2019-09-12 11:46:37"   dogs

以及customers_facts，它以fact_name和相应的fact_value的形式保存了客户的事实。

mysql> SELECT * FROM customers_facts;

customer_id fact_name   fact_value
1           name        Milton
1           city        Milan
1           birthday    "2019-08-20 09:17:58"
1           company     Idaho
2           surname     Bloom
2           name        Orlando
3           name        Milton
3           city        Milan
3           birthday    "2011-10-20 11:17:58"
3           company     Chicago

我想要创建一个查询来获取所有的customer_id，其中name=Milton和city=Milan按birthday和company排序。所以在我的例子中，结果是：

mysql> SELECT customer_id FROM ....

customer_id
1             
3           

我有一个查询，它获取所有的customers_id，其中name=Milton和city=Milan

SELECT cf.* FROM customers_facts cf 
WHERE cf.customer_id IN (
SELECT cf.customer_id FROM customers_facts cf
WHERE (cf.fact_name,cf.fact_value) IN (('name','Milton'),('city','Milan'))
GROUP BY cf.customer_id 
HAVING COUNT(*) = 2
)

但是我不知道如何用fact_value对结果进行排序，怎么做呢？这样的计划是否可能呢？

Answer 1

这有点棘手。在聚合之前，您不能轻松地进行过滤。那么，在having子句中进行过滤：

SELECT customer_id
FROM customers_facts
GROUP BY customer_id 
HAVING SUM( fact_name = 'name' AND fact_value = 'Milton' ) > 0 AND
       SUM( fact_name = 'city' AND fact_value = 'Milan' ) > 0
ORDER BY MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END) DESC,
         MAX(CASE WHEN fact_name = 'company' THEN fact_value END)

Answer 2

使用旋转逻辑为每个匹配的客户组计算生日：

SELECT customer_id
FROM customers_facts
WHERE (fact_name, fact_value) IN (('name', 'Milton'), ('city', 'Milan'))
GROUP BY customer_id 
HAVING MIN(fact_name) <> MAX(fact_name)
ORDER BY MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END);

除了ORDER BY子句之外，我使用了一个HAVING子句，它确保匹配的名称和城市在每个匹配的用户组中都存在。

编辑：

下面是您想要的ORDER BY子句：

ORDER BY
    MAX(CASE WHEN fact_name = 'birthday' THEN fact_value END) DESC,
    MAX(CASE WHEN fact_name = 'company' THEN fact_value END);  -- ASC is default