MySQL: 다른 컬럼의 변경에 따라 컬럼에 순차적으로 지정

MySQL: 다른 컬럼의 변경에 따라 컬럼에 순차적으로 지정

다음 열과 값을 가진 테이블이 있는 경우, 순서:parent_id:

id    parent_id    line_no
--    ---------    -------
1     2            
2     2
3     2
4     3
5     4
6     4

그리고 나는 채우고 싶다.line_no값이 나올 때마다 1로 다시 시작하는 일련 번호를 사용하여parent_id변경:

id    parent_id    line_no
--    ---------    -------
1     2            1
2     2            2
3     2            3
4     3            1
5     4            1
6     4            2

쿼리 또는 sproc는 어떻게 생겼습니까?

메모: 이 작업은 한 번만 수행하면 됩니다.내 PHP 코드에 새로운 함수가 있습니다. 이 함수는 자동적으로line_no새로운 레코드가 추가될 때마다.이미 존재하는 기록만 업데이트하면 됩니다.

MySQL의 대부분의 버전은 지원하지 않습니다.row_number()변수를 사용하여 이 작업을 수행할 수 있습니다.하지만 당신은 매우 조심해야 해요.MySQL은 변수 평가 순서를 보장하지 않습니다.select변수에는 다른 식에서 참조되는 변수를 할당할 수 없습니다.

그래서:

select t.*,
       (@rn := if(@p = parent_id, @rn + 1,
                  if(@p := parent_id, 1, 1)
                 )
       ) as line_no
from (select t.* from t order by id) t cross join
     (select @p := 0, @rn := 0) params;

테이블을 정렬하기 위한 서브쿼리가 필요하지 않을 수 있습니다.버전 5.7 전후에서는 변수를 사용할 때 이것이 필요하게 되었습니다.

편집:

변수를 사용한 업데이트는 재미있습니다.이 경우 위의 서브쿼리를 사용합니다.

update t join
       (select t.*,
               (@rn := if(@p = parent_id, @rn + 1,
                          if(@p := parent_id, 1, 1)
                         )
               ) as new_line_no
        from (select t.* from t order by id) t cross join
             (select @p := 0, @rn := 0) params
       ) tt
       on t.id = tt.id
    set t.line_no = tt.new_line_no;

아니면 좀 더 오래된...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,parent_id INT NOT NULL
);

INSERT INTO my_table VALUES
(1,    2), 
(2 ,    2), 
(3 ,    2), 
(4 ,    3), 
(5 ,    4), 
(6 ,    4);

SELECT x.*
     , CASE WHEN @prev = parent_id THEN @i := @i+1 ELSE @i := 1 END i
     , @prev := parent_id prev 
  FROM my_table x
     , (SELECT @prev:=null,@i:=0) vars 
 ORDER 
    BY parent_id,id;
    +----+-----------+------+------+
    | id | parent_id | i    | prev |
    +----+-----------+------+------+
    |  1 |         2 |    1 |    2 |
    |  2 |         2 |    2 |    2 |
    |  3 |         2 |    3 |    2 |
    |  4 |         3 |    1 |    3 |
    |  5 |         4 |    1 |    4 |
    |  6 |         4 |    2 |    4 |
    +----+-----------+------+------+    

서브쿼리는 다음과 같은 경우에 사용할 수 있습니다.row_number()도움이 되지 않습니다.

select t.*, 
       (select count(*)
        from table t1
        where t1.parent_id = t.parent_id and t1.id <= t.id
       ) as line_no
from table t;

언급URL : https://stackoverflow.com/questions/52706441/mysql-sequentially-number-a-column-based-on-change-in-a-different-column