MySQL을 사용하여 테이블에 레코드 인덱스를 포함하는 열을 어떻게 생성 할 수 있습니까?

MySQL을 사용하여 테이블에 레코드 인덱스를 포함하는 열을 어떻게 생성 할 수 있습니까?

---

쿼리에서 실제 행 번호를 얻을 수있는 방법이 있습니까?

score라는 필드로 league_girl이라는 테이블을 주문할 수 있기를 원합니다. 사용자 이름과 해당 사용자 이름의 실제 행 위치를 반환합니다.

특정 사용자가 어디에 있는지 알 수 있도록 사용자 순위를 지정하고 싶습니다. Joe는 200 점 중 100 위입니다.

User Score Row
Joe  100    1
Bob  50     2
Bill 10     3

여기에서 몇 가지 해결책을 보았지만 대부분을 시도했지만 실제로 행 번호를 반환하는 것은 없습니다.

나는 이것을 시도했다 :

SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score 
       FROM league_girl GROUP BY username ORDER BY score DESC) 

파생 된대로

...하지만 행 위치를 반환하지 않는 것 같습니다.

어떤 아이디어?

---

다음을 시도해 볼 수 있습니다.

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r;

이 JOIN (SELECT @curRow := 0)부분은 별도의 SET명령 없이 변수 초기화를 허용합니다 .

테스트 케이스 :

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);

테스트 쿼리 :

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r
WHERE   l.score > 50;

결과:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)

---

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo

---

내가 사용한 템플릿의 구조는 다음과 같습니다.

  select
          /*this is a row number counter*/
          ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
          as rownumber,
          d3.*
  from 
  ( select d1.* from table_name d1 ) d3

And here is my working code:

select     
           ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
           as rownumber,
           d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
    from       maindatabase d1
    where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
    group by   YEAR( d1.date ), MONTH( d1.date ) ) d3

---

You can also use

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

to initialise the counter variable.

---

Assuming MySQL supports it, you can easily do this with a standard SQL subquery:

select 
    (count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
    username,
    score
from league_girl l2
order by score;

For large amounts of displayed results, this will be a bit slow and you will want to switch to a self join instead.

---

If you just want to know position of one specific user after order by field score, you can simply select all row from your table where field score is higher than current user score. And use row number returned + 1 to know which position of this current user.

Assuming that your table is "league_girl" and your primary field is "id", you can use this:

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>

---

I know the OP is asking for a mysql answer but since I found the other answers not working for me,

• Most of them fail with order by
• Or they are simply very ineffecient and make your query very slow for a fat table

So to save time for others like me, just index the row after retrieving them from databse

example in PHP:

        $users = UserRepository::loadAllUsersAndSortByScore();

        foreach($users as $index=>&$user){
            $user['rank'] = $index+1;
        }

example in PHP using offset and limit for paging:

        $limit = 20; //page size
        $offset = 3; //page number

        $users = UserRepository::loadAllUsersAndSortByScore();

        foreach($users as $index=>&$user){
            $user['rank'] = $index+1+($limit*($offset-1));
        }

---

I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting. As such, I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting.

SELECT * FROM 
( 
    SELECT *, @curRow := @curRow + 1 AS "row_number"
    FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;

Having a subquery in a subquery is not very efficient, so it would be worth testing whether you get a better result by having your SQL server handle this query, or fetching the entire table and having the application/web server manipulate the rows after the fact.

Personally my SQL server isn't overly busy, so having it handle the nested subqueries was preferable.

참고URL : https://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table