Пронумеровать поле в MySQL таблице с сортировкой и группировкой?

Есть две таблицы article (article_id, sort) и category (article_id, category_id, number)

Нужно заполнить поле number от 1..., используя сортировку по полю sort и чтобы в каждой category_id счет опять начинался с 1, а не продолжался, то есть должно получится так:
category:

article_id — category_id — number

51 1 1

96 1 2

12 1 3



62 2 1

51 2 2

28 2 3

… и т.д.


Например, если делать отдельно для каждой категории, так как не придумал как можно в запросе сделать для всех категорий сразу со сбросом на 1 в новой, то так:

UPDATE `category`
JOIN `article` ON `article`.`article_id`=`category`.`article_id`
SET `number`=(select @n:=@n+1 from (select @n:=0) as t)
WHERE `category`.`category_id`=123
ORDER BY `sort` ASC


Но пишет, нельзя использовать order by к таблице из join


Хотелось бы не отдельно для каждой категории запрос делать, так как категорий очень много, и было бы быстрее одним запросом, чем для каждой категории свой
  • Вопрос задан
  • 5726 просмотров
Пригласить эксперта
Ответы на вопрос 2
pel
@pel
Че-т не вышло сделать сходу без дополнительных телодвижений, поэтому предложу пока такой вариант (дальше скрипты с create'ами. Убирать их не стал — мне кажется, так «нагляднее» будет):

-- Ваша таблица article
CREATE TABLE a (
a_id INT( 10 ) NOT NULL ,
s INT( 10 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO  a (a_id , s) VALUES 
(1, 3), 
(2, 5), 
(3, 7), 
(4, 2), 
(5, 4);

-- Ваша таблица category
CREATE TABLE c (
a_id INT( 10 ) NOT NULL ,
c_id INT( 10 ) NOT NULL ,
n INT( 10 ) NOT NULL DEFAULT 0
) ENGINE = MYISAM ;

INSERT INTO c (a_id , c_id , n) VALUES 
(1, 2, 0), 
(2, 2, 0), 
(3, 1, 0), 
(4, 2, 0), 
(5, 1, 0);

-- В таблицу категорий добавляем временное поле, 
ALTER TABLE c ADD s_tmp INT NOT NULL DEFAULT '0';
-- в которое закинем соответствующие значения sort из article
UPDATE c 
SET c.s_tmp = ( 
	SELECT s
	FROM a
	WHERE a.a_id = c.a_id 
);

-- Создадим временную таблицу, в которую с помощью как раз той магии, которая вам, собственно, нужна, проставим позиции внутри групп
DROP TABLE IF EXISTS c_tmp;

SET @cat:='', @num:=1;

CREATE TABLE c_tmp 
SELECT c_id, a_id,
	@num := IF( @cat = c_id, @num +1, 1 ) AS row_num, 
	@cat := c_id AS dummy
FROM c
ORDER BY c_id, s_tmp;

-- Теперь эти сгенерированные значения перекидываем в основную таблицу
UPDATE c 
SET c.n = ( 
	SELECT row_num
	FROM c_tmp AS ct
	WHERE c.a_id = ct.a_id AND c.c_id = ct.c_id 
);

-- Ну и убираем 
ALTER TABLE  c DROP  s_tmp;
-- за собой мусор
DROP TABLE  c_tmp;
Ответ написан
FreeTibet
@FreeTibet
dharma supplier
UPDATE category SET number = 0;

SET @old_category = -1;
SET @num = -1;
UPDATE category AS c
INNER JOIN (
	SELECT 
	    t.article_id,
	    t.sort,
	    t.category_id,
	    @old_category,
	    @num := CASE
	    	WHEN @old_category <> t.category_id THEN 0
	        ELSE @num + 1
	    END AS number,
	    @old_category := t.category_id
	FROM (
		SELECT
			a.article_id, 
		    a.sort, 
		    c.category_id
		FROM category AS c
		INNER JOIN article AS a ON a.article_id = c.article_id
		ORDER BY c.category_id, a.sort
	) AS t
) AS t
ON t.article_id = c.article_id
SET c.number = t.number;

SELECT c.*, a.sort FROM category AS c 
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort


Результат:
aricle_id | category_id  | number | sort
5         | 1            | 0      | 4
3         | 1            | 1      | 7
4         | 2            | 0      | 2
1         | 2            | 1      | 3
2         | 2            | 2      | 5
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы