如何按值对SQL行进行排序 - How to order SQL rows by value?

How to order SQL rows by value?

本文关键字：排序 SQL 何按值 | 更新日期: 2023-11-21

如何通过Bmeta_key选择user_ids，并根据评级meta_value排序？

user_id|meta_key|meta_value-----------------------------------------------------1 A 11 B 11 C 11评级98 A 18 C 18评级999997 A 17 B 17 C 17评级999

我需要获得具有B元密钥的用户的ID，然后按用户评级值排序ID。

试试这个：

SELECT T1.user_id
    FROM yourtable T1 
    JOIN yourtable T2
        ON T1.user_id = T2.user_id
        AND T2.meta_key = 'rating'
    WHERE T1.meta_key = 'B'
    ORDER BY CAST(T2.meta_value AS SIGNED)

要做到这一点，您需要一个自联接：

select t.*
from t left outer join
     (select t.user_id, max(meta_value) as rating
      from t
      where t.meta_key = 'rating'
      group by t.user_id
     ) r
     on t.user_id = r.user_id
where meta_key = 'B'
order by r.rating