Как определить позицию (место) в зависимости от рейтинга пользователей или товаров?
есть таблица
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL,
positions int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
)
ENGINE = INNODB
AVG_ROW_LENGTH = 630
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
если одним запросом и учитывать то что у людей или товаров могут быть
одинаковые рейтинги то можно например так, но хранить рейтингуи нужно будет в отдельной таблице
SET @position = 0;
DELETE
FROM raiting_place_user;
INSERT INTO raiting_place_user (id, raiting_place, rating)
SELECT
id,
@position := @position + 1 AS pos,
rating
FROM (SELECT
*
FROM user
WHERE utype = 0
ORDER BY rating DESC) user
WHERE utype = 0;
CREATE TABLE raiting_place_user (
id int(11) NOT NULL,
raiting_place int(11) NOT NULL DEFAULT 0,
rating int(11) DEFAULT NULL COMMENT 'просто для проверки (можно удалить)',
PRIMARY KEY (id),
CONSTRAINT FK_raiting_place_user_user_id FOREIGN KEY (id)
REFERENCES user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AVG_ROW_LENGTH = 512
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'место рейтингов пользователей';
сами запросы лучше упокавать в процедуру, которую вызывать триггером
CREATE DEFINER = 'root'@'127.0.0.1'
PROCEDURE update_positions()
BEGIN
SET @position = 0;
DELETE
FROM raiting_place_user;
INSERT INTO raiting_place_user (id, raiting_place, rating)
SELECT
id,
@position := @position + 1 AS pos,
rating
FROM (SELECT
*
FROM user
WHERE utype = 0
ORDER BY rating DESC) user
WHERE utype = 0;
END
триггер при удалении пользователя
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER deleteuser
AFTER DELETE
ON user
FOR EACH ROW
BEGIN
CALL update_positions();
END
при вставке не получится сделать триггер, вызывайте сами
CALL update_positions();
и при обновлении
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER addeditstatus
AFTER UPDATE
ON user
FOR EACH ROW
BEGIN
IF (@DISABLE_TRIGGERS IS NULL) AND (OLD.rating <> NEW.rating) THEN
# изменение рейтинга и пересчет позиций
CALL update_positions();
END IF;
END
а можно переписать результаты одним запросы в основную таблицу (будет использоваться при сортировке)
??
да , можно
CREATE DEFINER = 'root'@'127.0.0.1'
PROCEDURE copy_positions()
BEGIN
UPDATE user, raiting_place_user
SET user.positions = IF( ISNULL(raiting_place_user.raiting_place),0, raiting_place_user.raiting_place)
WHERE user.id = raiting_place_user.id;
END
вызывайте так
CALL copy_positions();
Комментарии
Оставить комментарий
Базы данных - MySql (Maria DB)
Термины: Базы данных - MySql (Maria DB)