mysql - 在这里选择点赞数的 sum()

Question

在下表中，我尝试用总票数的总和来更新每行的父项。

这里一切正常，除了我想使用所有 type = 10 的 SUM() 来完成操作，而不是按照我现在的方式进行操作现在。现在，发生的情况是这样的:如果 type = 10 的总数发生变化，父级的总数不会调整，而是相加。我不知道如何在其中添加 sum() 。我已经走到这一步了。你能帮忙吗？

如果您检查我的存储过程，您就会看到它是如何工作的。

下面的东西工作正常。我不确定如何在 sqlfiddle 上设置存储过程来演示它。

我的表名为 likesd，具有所需的结果

这些结果实际上是由下面的存储过程完成的

"id"    "type"  "path"          "likes"
"1"     "1"     "0"             "20"
"2"     "1"     "0"             "20"
"3"     "2"     "1,2"           "20"
"4"     "2"     "1,2,3"         "20"
"5"     "2"     "1,2,3"         "0"
"6"     "3"     "1,2,3,4"       "0"
"7"     "3"     "1,2,3,4"       "0"
"8"     "4"     "1,2,3,4"       "20" 
"9"     "10"    "1,2,3,4,8"     "5" // These type 10 are books
"10"    "10"    "1,2,3,4,8"     "5" // These type 10 are books
"11"    "10"    "1,2,3,4,8"     "5" // These type 10 are books
"12"    "10"    "1,2,3,4,8"     "5" // These type 10 are books

这是我正在使用的存储过程

BEGIN
#---- Updater for like totals 21-11-2013

   # Declare variables
   DECLARE procId, procLikes INT(10);
   DECLARE procPath VARCHAR(50);
   DECLARE done INT DEFAULT 0;

   # Declare cursor
   DECLARE cur1 CURSOR FOR SELECT id, path, likes FROM testLikes WHERE type = 10;

   # Declare Handle
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   # Open Cursor
   OPEN cur1;

   # Begin the Loop
   the_loop: LOOP

    FETCH cur1 INTO procId, procPath, procLikes;

   IF done THEN
    LEAVE the_loop;
   END IF;

   # Do some post processing
    UPDATE testLikes t1 JOIN testLikes t2 ON FIND_IN_SET(t1.id, t2.path)
        SET t1.likes = t1.likes + procLikes // Trying to add a sum(of all type = 10) here.
            // Problem here is, New totals should be sum of all type = 10, but right now, it all adds up.
            WHERE t2.id = procId;

    END LOOP the_loop;

    # Dummy select for MySql's bug
    SELECT id INTO procId FROM testLikes where id = 1;

    CLOSE cur1;

END

Answer 1

我并不完全相信这是否有效，但是:

 select
type
,SUM(likes) as NUmberofLikes
into temp1
from testLikes
group by type

UPDATE testlikes t1
SET likes= (select NumberOFLikes from temp1 t2 where t1.type=t2.type)