CREATE TABLE inventory_player (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
item_id int(11) NOT NULL,
slot_id int(11) NOT NULL,
deleted_at_time datetime DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO inventory_player
VALUES
(1, 4, 0, 0, NULL),
(2, 4, 0, 1, NULL),
(3, 4, 0, 2, NULL),
(4, 4, 0, 3, NULL),
(5, 4, 1, 4, NULL),
(6, 4, 1, 4, NULL),
(7, 4, 1, 4, NULL),
(8, 4, 1, 4, NULL),
(9, 4, 1, 4, NULL),
(10, 4, 1, 4, NULL);
SELECT
*
FROM
inventory_player
order by
id;
SET
@ids := null;
WITH old as (
SELECT
*,
DENSE_RANK() OVER (
order by
item_id DESC
) -1 dr,
ROW_NUMBER() OVER (
PARTITION BY item_id
order by
id
) rn
FROM
inventory_player
),
jn as (
SELECT
o1.id,
o1.user_id,
o1.item_id,
o2.slot_id
FROM
old o1
INNER JOIN old o2 ON o2.dr = o1.item_id
and o1.rn = o2.rn
)
UPDATE
inventory_player ip
INNER JOIN jn ON jn.id = ip.id
SET
ip.slot_id = jn.slot_id
WHERE
(
SELECT
@ids := CONCAT_WS(',', ip.id, @ids)
);
select
@ids;
SELECT
*
FROM
inventory_player
where
inventory_player.id IN(@ids);
DELETE FROM
inventory_player
where
id in (@ids);
SELECT
*
FROM
inventory_player
order by
id;
Telegram: DB developers channel