限制连接表 [英] Limiting joined table
问题描述
嘿伙计:-)
我想提高我的MySQL知识,并给了我一个奇怪的要求。
所以请不要笑;-)
目标:在几行中显示每个用户的最后三个点条目
有两个表如:
表用户:
user_id |姓名
1 | first_name
2 | second_name
3 |第三名
和
桌位:
user_id |积分
1 | 10
1 | 20
1 | 30
1 | 40
2 | 15
2 | 25
2 | 35
2 | 45
3 | 11
3 | 22
3 | 33
结果如下:
user_id |名字|积分
1 | first_name | 40
1 | first_name | 30
1 | first_name | 20
2 | second_name | 45
2 | second_name | 35
2 | second_name | 25
3 | third_name | 33
3 | third_name | 22
3 | third_name | 11
我尝试了基本的加入和子查询知识,但我遇到了几个MySql错误。
限制是不允许的IN等等。
你会做什么,解决这个问题?
什么你是在一个类别中排名,其中每个名称都是类别,每个名称的积分都会排名。
我发现了一个巧妙的伎俩发布 [ ^ ]
SET @ prev := null ;
SET @ cnt := 0 跨度>;
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS
(
SELECT U.user_id,user_name,points,
IF ( @ prev <> U.user_id, @ cnt := 1 , @ cnt := @ cnt + 1 ) AS 排名,
@ prev := U.user_id
FROM user U
JOIN 指向P ON U.user_id = P.user_id
ORDER BY U.user_id,points DESC ;
);
选择 user_id,user_name,指向 table2 其中排名< 4 ;
抱歉 - sqlfiddle在我身上坠毁,所以我无法完全测试。
注意事项 - 我已按user_id升序排序查询,但点数降低点数值似乎增加。没有点上的顺序我没有尝试过查询。如果有获得积分的日期/时间,那么你可以使用它获得最后3分,否则你可能想要玩模拟sql Row_number函数 [ ^
Hey guys :-)
I want to improve my MySQL knowledge and have given me an weird requirement.
So please don't laugh ;-)
Target : show the last three point-entries for each user in several rows
There are two tables like:
table user:
user_id | name
1 | first_name
2 | second_name
3 | third_name
and
table point:
user_id | points
1 | 10
1 | 20
1 | 30
1 | 40
2 | 15
2 | 25
2 | 35
2 | 45
3 | 11
3 | 22
3 | 33
The result should look like:
user_id | name | points
1 | first_name | 40
1 | first_name | 30
1 | first_name | 20
2 | second_name | 45
2 | second_name | 35
2 | second_name | 25
3 | third_name | 33
3 | third_name | 22
3 | third_name | 11
I tried it with my basic joining- and subquery knowledge, but I ran into several MySql errors.
Limiting is not allowed with "IN" and so on.
What would you do, to solve this?
What you are after is ranking within a category, where each name is the "category" and the points will be ranked for each name.
I found a neat trick on this post[^] which I used in this query
SET @prev := null; SET @cnt := 0; CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS ( SELECT U.user_id, user_name, points, IF(@prev <> U.user_id, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := U.user_id FROM user U JOIN point P ON U.user_id = P.user_id ORDER BY U.user_id, points DESC; ); select user_id, user_name, points from table2 where rank < 4;
Sorry - sqlfiddle crashed on me so I haven't been able to test it fully.
Points to note - I've ordered the query by user_id ascending but points Descending as the points values seemed to increase. I haven't tried the query without the order on points. If there was a date/time when the points were gained then you could use that to get "the last 3 points" otherwise you might want to have a play around with emulating the sql Row_number function[^]
这篇关于限制连接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!