mysql - 插入表(var1) 从自身中选择

Question

我想插入最后一列(该房间里的人数)并且我想使用

insert into table(n_people_in_room) 
select count(people_id)
from table
group by room

这显然是错误的，因为我需要在 people_id 上将表与其自身连接起来，但我没有。什么是正确的代码？

Answer 1

这是一种方法，使用内联 View 获取 N_People_In_Room 总数:

我首先将其作为选择:

 SELECT t.peopleid
      , t.room
      , t.n_people_in_room AS `old_npir`
      , s.n_people_in_room AS `new_npir` 
  FROM mytable t
   JOIN ( SELECT c.room
               , COUNT(1) AS n_people_in_room
            FROM mytable c
           GROUP BY c.room
        ) s
     ON s.room = t.room

通过用 UPDATE 替换 SELECT ... FROM 并将其转换为 UPDATE，并添加 SET 子句...

 UPDATE mytable t
   JOIN ( SELECT c.room
               , COUNT(1) AS n_people_in_room
            FROM mytable c
           GROUP BY c.room
        ) s
     ON s.room = t.room
    SET t.n_people_in_room = s.n_people_in_room