Tengo una tabla con el siguiente esquema +-------------------------+------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+------------------+------+-----+-------------------+-----------------------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | parent_id | int(10) unsigned | NO | | NULL | | | name | varchar(255) | NO | UNI | NULL | | | disp1 | varchar(255) | NO | | NULL | | | disp2 | varchar(255) | NO | | NULL | | | created | timestamp | NO | | CURRENT_TIMESTAMP | | | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | node_e | tinyint(1) | YES | | 1 | | | node_priority | int(5) | NO | | NULL | | +-------------------------+------------------+------+-----+-------------------+-----------------------------+ que ejecuto debajo de la consulta para obtener los datos deseados:
select id,node_priority,parent_id from node where parent_id in (Select distinct parent_id from node as n) order by parent_id asc;
que da a continuación la salida:
+-----+---------------+-----------+
| id | node_priority | parent_id |
+-----+---------------+-----------+
| 17 | 0 | 2 |
| 18 | 0 | 2 |
| 19 | 0 | 2 |
| 20 | 0 | 2 |
| 21 | 0 | 2 |
| 216 | 0 | 3 |
| 23 | 0 | 22 |
| 24 | 0 | 22 |
| 25 | 0 | 22 |
| 26 | 0 | 22 |
| 27 | 0 | 22 |
| 29 | 0 | 28 |
| 30 | 0 | 28 |
| 31 | 0 | 28 |
| 32 | 0 | 28 |
| 33 | 0 | 28 |
| 35 | 0 | 34 |
| 36 | 0 | 34 |
| 37 | 0 | 34 |
| 38 | 0 | 34 |
| 39 | 0 | 34 |
| 40 | 0 | 34 |
| 41 | 0 | 34 |
| 42 | 0 | 34 |
| 44 | 0 | 43 |
| 45 | 0 | 43 |
| 46 | 0 | 43 |
| 47 | 0 | 43 |
| 48 | 0 | 43 |
| 49 | 0 | 43 |
| 50 | 0 | 43 |
| 51 | 0 | 43 |
| 58 | 0 | 52 |
| 60 | 0 | 52 |
| 66 | 0 | 52 |
| 77 | 0 | 52 |
| 95 | 0 | 52 |
| 96 | 0 | 52 |
| 106 | 0 | 52 |
| 117 | 0 | 52 |
| 128 | 0 | 52 |
| 137 | 0 | 52 |
| 141 | 0 | 52 |
| 164 | 0 | 52 |
| 55 | 0 | 53 |
| 56 | 0 | 53 |
| 57 | 0 | 53 |
| 59 | 0 | 58 |
| 61 | 0 | 60 |
| 62 | 0 | 60 |
| 63 | 0 | 60 |
| 64 | 0 | 60 |
| 65 | 0 | 60 |
| 67 | 0 | 66 |
| 68 | 0 | 66 |
| 72 | 0 | 66 |
| 73 | 0 | 66 |
| 74 | 0 | 66 |Mi principal objetivo:
Quiero que para el mismo valor de la columna parent_id, el valor de node_priority debe estar en orden incremental desde 0 hasta el número de filas que tienen el mismo parent_id.
por lo que la salida sería como a continuación:
+-----+---------------+-----------+
| id | node_priority | parent_id |
+-----+---------------+-----------+
| 17 | 0 | 2 |
| 18 | 1 | 2 |
| 19 | 2 | 2 |
| 20 | 3 | 2 |
| 21 | 4 | 2 |
| 216 | 0 | 3 |
| 23 | 0 | 22 |
| 24 | 1 | 22 |
| 25 | 2 | 22 |
| 26 | 3 | 22 |
| 27 | 4 | 22 |
| 29 | 0 | 28 |
| 30 | 1 | 28 |
| 31 | 2 | 28 |
| 32 | 3 | 28 |
| 33 | 4 | 28 |
| 35 | 0 | 34 |
| 36 | 1 | 34 |No puedo escribir una consulta para el mismo. Amablemente ayuda.
En mysql puede lograr esto usando las variables definidas por el usuario algo como
select
id,node_priority,parent_id
from (
select
id,
@rn:= if(@prev_parent = parent_id,@rn+1,0) as node_priority,
@prev_parent:= parent_id,
parent_id
from node,(select @prev_parent:=0,@rn:=0)x
order by parent_id
)x
También
where parent_id in (Select distinct parent_id from node as n)
no es necesario en la consulta anterior.
Para actualizar puede utilizar lo siguiente
update node n1
join (
select
id,
@rn:= if(@prev_parent = parent_id,@rn+1,0) as node_priority,
@prev_parent:= parent_id,
parent_id
from node,(select @prev_parent:=0,@rn:=0)x
order by parent_id,id
)n2 on n1.id = n2.id
set n1.node_priority = n2.node_priority;
Este artículo se recopila de Internet, indique la fuente cuando se vuelva a imprimir.
En caso de infracción, por favor [email protected] Eliminar
Déjame decir algunas palabras