How to select user_ids by B meta_key and order by ratings meta_value?
如何通过B meta_key选择user_ids并按评级meta_value排序?
user_id | meta_key | meta_value
-----------------------------------------------------
1 A 1
1 B 1
1 C 1
1 rating 9
8 A 1
8 C 1
8 rating 99999
7 A 1
7 B 1
7 C 1
7 rating 999
I need to get an IDs of users who have B meta_key and, then, to order IDs by user rating values.
我需要获取具有B meta_key的用户的ID,然后按用户评级值订购ID。
2 个解决方案
#1
1
Try this:
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)
#2
1
To do this, you need a self join:
为此,您需要自我加入:
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
#1
1
Try this:
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)
#2
1
To do this, you need a self join:
为此,您需要自我加入:
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