MySQL обновляет объединенный запрос, чтобы решить дублирующиеся Значения

Question

MySQL обновляет объединенный запрос, чтобы решить дублирующиеся Значения

У меня есть таблица категорий, которая имеет несколько повторяющихся категорий, как описано ниже,

`Categories`
+========+============+============+
| cat_id | cat_name   | item_count |
+========+============+============+
|      1 | Category 1 |         2  |
|      2 | Category 1 |         1  |
|      3 | Category 2 |         2  |
|      4 | Category 3 |         1  |
|      5 | Category 3 |         1  |
+--------+------------+------------+

Вот еще одна соединительная таблица, которая относится к другой таблице предметов. item_count в первой таблице указано общее количество предметов на cat_id,

`Junction`
+========+=========+
| cat_id | item_id |
+========+=========+
|      1 |     100 |
|      1 |     101 |
|      2 |     102 |
|      3 |     103 |
|      3 |     104 |
|      4 |     105 |
|      5 |     106 |
+--------+---------+

Как добавить или объединить эти элементы из дубликатов категорий в категории, каждая из которых имеет максимум item_count среди их дубликатов? (например Category 1).

Кроме того, если item_count то же самое для тех дубликатов, то категория с максимальным cat_id будет выбран и item_count будет объединен с этой записью. (например Category 3).

Примечание. Вместо удаления повторяющихся записей item_count будет установлен в 0,

Ниже приведен ожидаемый результат.

+========+============+============+
| cat_id | cat_name   | item_count |
+========+============+============+
|      1 | Category 1 |         3  |
|      2 | Category 1 |         0  |
|      3 | Category 2 |         2  |
|      4 | Category 3 |         0  |
|      5 | Category 3 |         2  |
+--------+------------+------------+

+========+=========+
| cat_id | item_id |
+========+=========+
|      1 |     100 |
|      1 |     101 |
|      1 |     102 |
|      3 |     103 |
|      3 |     104 |
|      5 |     105 |
|      5 |     106 |
+--------+---------+

В результате получается два дубликата Category 1 а также Category 3, И у нас есть 2 сценария,

cat_idзнак равно2 устраняется, потому что его item_countзнак равно1 меньше, чем у cat_idзнак равно1 который item_countзнак равно2,
cat_idзнак равно4 устраняется, хотя его item_count такой же, как у cat_idзнак равно5 поскольку 5 это максимум среди дубликатовCategory 3,

Пожалуйста, помогите мне, если какой-либо запрос, который может объединить и обновить обе таблицы для устранения дубликатов.

0

mysql foreign-keys relational-database junction-table

Источник

user3327081 24 ноя '15 в 14:43

4 ответа

Решение

Это не красиво и скопировано частично из SELECT Strawberry

UPDATE categories cat, 
    junction jun,
    (select 
    (z.cat_id = x.cat_id) * new_count c,
     x.cat_id newcatid,
     z.cat_id oldcatid
    from categories x 
      LEFT 
      JOIN categories y 
        ON y.cat_name = x.cat_name 
       AND (y.item_count > x.item_count OR (y.item_count = x.item_count AND y.cat_id > x.cat_id))
      LEFT
      JOIN 
         ( SELECT a.cat_id, b.*
             FROM categories a
             JOIN 
                ( SELECT cat_name, SUM(item_count) new_count, MAX(item_count) max_count FROM categories GROUP BY cat_name) b
               ON b.cat_name = a.cat_name
         ) z
        ON z.cat_name = x.cat_name

     WHERE
     y.cat_id IS NULL) sourceX

     SET cat.item_count = sourceX.c, jun.cat_id = sourceX.newcatid
     WHERE cat.cat_id = jun.cat_id and cat.cat_id = sourceX.oldcatid

1

Источник

user5559001 24 ноя '15 в 17:29

 DELIMITER $$
 DROP PROCEDURE IF EXISTS  cursor_proc $$
 CREATE PROCEDURE cursor_proc()
 BEGIN
   DECLARE @cat_id   INT;
   DECLARE @cat_name VARCHAR(255);
   DECLARE @item_count INT;

   DECLARE @prev_cat_Name VARCHAR(255);
   DECLARE @maxItemPerCategory INT;
   DECLARE @maxItemId INT DEFAULT 0;
   DECLARE @totalItemsCount INT;
   -- this flag will be set to true when cursor reaches end of table
   DECLARE exit_loop BOOLEAN;         
   -- Declare the cursor
   DECLARE categories_cursor CURSOR FOR
     SELECT select cat_id ,cat_name ,item_count from Categories Order By cat_name, cat_id;
   -- set exit_loop flag to true if there are no more rows
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   -- open the cursor
   OPEN categories_cursor;
   -- start looping
   categories_loop: LOOP
     -- read the name from next row into the variables 
     FETCH  categories_cursor INTO @cat_id, @cat_name, @item_count ;

     -- close the cursor and exit the loop if it has.
     IF exit_loop THEN
         CLOSE categories_loop;
         LEAVE categories_loop;
     END IF;

       IF(@prev_cat_Name <> @cat_name)
        THEN 
        -- Category has changed, set the item_count of the 'best' category with the total items count
        IF(@maxItemId > 0)
        THEN
          UPDATE Categories  
            SET Categories.item_count=@totalItemsCount
           WHERE Categories.cat_id=@maxItemId;         
        END IF;

       -- Reset Values with the actual row values                        
          SET @maxItemPerCategory = @item_count;
          SET @prev_cat_Name = @cat_name;
          SET @maxItemId = @cat_id
          SET @totalItemsCount = @item_count;
       ELSE
      -- increment the total items count
          SET @totalItemsCount = @totalItemsCount + @item_count

       -- if the actual row has the maximun item counts, then it is the 'best'
           IF (@maxIntPerCategory < @item_count)
           THEN 
             SET @maxIntPerCategory = @item_count
             SET @maxItemId = @cat_id
           ELSE
         -- else, this row is not the best of its Category
              UPDATE Categories  
                 SET Categories.item_count=0
               WHERE Categories.cat_id=@cat_id; 
           END IF; 

       END IF;


   END LOOP categories_loop;
 END $$
 DELIMITER ;

1

Источник

user1932499 24 ноя '15 в 14:57

Я думаю, что лучше делать то, что вы хотите, шаг за шагом:

Сначала получите данные, которые вам нужны:

SELECT Max(`cat_id`), sum(`item_count`) FROM `Categories` GROUP BY `cat_name`

С этими данными вы сможете проверить, правильно ли было выполнено обновление.

Затем, с циклом на полученных данных, обновите:

update Categories set item_count =
    (
    Select Tot FROM (
        Select sum(`item_count`) as Tot
        FROM `Categories`
        WHERE `cat_name` = '@cat_name') as tmp1
    )
WHERE cat_id = (
    Select MaxId
    FROM (
        select max(cat_id) as MaxId
        FROM Categories
        WHERE `cat_name` = '@cat_name') as tmp2)

Обратите внимание: если вы дважды запустите этот код, результат будет неверным.

Наконец, установите другие идентификаторы на 0

UPDATE Categories set item_count = 0
WHERE `cat_name` = '@cat_name'
AND cat_id <> (
    Select MaxId
    FROM (
        select max(cat_id) as MaxId
        FROM items
        WHERE `cat_name` = '@cat_name0') as tmp2)

0

Источник

user4293613 24 ноя '15 в 16:32

Другие вопросы по тегам mysql foreign-keys relational-database junction-table

user1529673 24 ноя '15 в 16:03 2015-11-24 16:03 · Accepted Answer · 2015-11-24 16:03

Вот ВЫБРАТЬ. Вы можете выяснить, чтобы адаптировать его к ОБНОВЛЕНИЮ;-)

Я проигнорировал таблицу jucntion для простоты

SELECT z.cat_id
     , z.cat_name
     , (z.cat_id = x.cat_id) * new_count item_count
  FROM categories x 
  LEFT 
  JOIN categories y 
    ON y.cat_name = x.cat_name 
   AND (y.item_count > x.item_count OR (y.item_count = x.item_count AND y.cat_id > x.cat_id))
  LEFT
  JOIN 
     ( SELECT a.cat_id, b.*
         FROM categories a
         JOIN 
            ( SELECT cat_name, SUM(item_count) new_count, MAX(item_count) max_count FROM categories GROUP BY cat_name) b
           ON b.cat_name = a.cat_name
     ) z
    ON z.cat_name = x.cat_name
 WHERE y.cat_id IS NULL;

+--------+------------+------------+
| cat_id | cat_name   | item_count |
+--------+------------+------------+
|      1 | Category 1 |          3 |
|      2 | Category 1 |          0 |
|      3 | Category 2 |          2 |
|      4 | Category 3 |          0 |
|      5 | Category 3 |          2 |
+--------+------------+------------+