触发器更新另一张表中的行

Question

触发器更新另一张表中的行

4

这是一个 select 语句，用于计算我数据库中歌曲的平均评分。

SELECT *, (tab.rating_sum/tab.rating_count) as rating_average FROM
(SELECT song_id, COUNT(rating) rating_count, SUM(rating) rating_sum FROM ratings
GROUP BY song_id) tab

INNER JOIN SONGS WHERE SONGS.id = tab.song_id

我需要在RATINGS表中插入列rating_average到SONGS表中，当在RATINGS中插入新的评分时。我还需要确保它被插入到正确的song_id列中。如何使用触发器实现此功能？我已经快被搞疯了。

这是我能想到的最好的方法，但我不知道如何使用触发器：

CREATE TRIGGER rating_trig ON ratings FOR INSERT AS
BEGIN
UPDATE SONGS SET
rating = SELECT (tab.rating_sum/tab.rating_count) as rating_average FROM
(SELECT song_id, COUNT(rating) rating_count, SUM(rating) rating_sum FROM ratings
GROUP BY song_id) tab

INNER JOIN SONGS WHERE SONGS.id = tab.song_id
END

表格布局:

评分 | 歌曲ID，评分，用户名歌曲 | ID，评分，歌曲名称

- Nick Nelson

我已经尝试了很多方法，但都没有用，我不记得我尝试过什么，因为我没有取得任何进展。我希望有人能给我一个基本的布局，告诉我可能需要做什么。 - Nick Nelson

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marc Alff · Accepted Answer

假设有如下表结构：

use test;

create table song(
  song_id integer,
  rating_avg double,
  rating_sum integer,
  rating_count integer);

create table rating(
  song_id integer,
  user_id integer,
  rating integer);

定义以下触发器：

delimiter $$

create trigger bi_song before insert on test.song
for each row
begin
  set NEW.rating_sum = 0;
  set NEW.rating_count = 0;
  set NEW.rating_avg = NULL;
end
$$

create trigger ai_rating after insert on test.rating
for each row
begin
  update song set
    rating_sum = rating_sum + NEW.rating,
    rating_count = rating_count + 1,
    rating_avg = rating_sum / rating_count
    where song_id = NEW.song_id;
end
$$
delimiter ;

它应该像这样工作：

mysql> insert into song(song_id) values (1);
Query OK, 1 row affected (0.06 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |       NULL |          0 |            0 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

mysql> insert into rating(song_id, user_id, rating) values (1, 1000, 5);
Query OK, 1 row affected (0.05 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |          5 |          5 |            1 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

mysql> insert into rating(song_id, user_id, rating) values (1, 1001, 7);
Query OK, 1 row affected (0.05 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |          6 |         12 |            2 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

这只是为了说明如何使用触发器而做出的简化。在真实应用中，你会有更多的列、索引等。