限制连接表 [英] 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屋！