Mysql -根据值的出现情况进行排名

Question

我在MySQL数据库中有一个表，不断向其中添加记录。我想对某个值的出现情况进行排名，并将其保存到新字段中。

我想为这个特定的人/名字根据水果的出现情况填充FruitRank字段。

这是预期的表-名称和水果实时添加到表中。FruitRank字段应实时计算和更新。该怎么做呢？

名称|水果|FruitRank(新字段)

艾米|苹果|1

艾米|苹果|1

艾米|苹果|1

艾米|橙色|2

艾米|橙色|2

Tom |葡萄|1

Tom |葡萄|1

艾米|奇异果|3

艾米|奇异果|3

Answer 1

这里有两种可能的方法，在数据库级别，取决于您的需求：

• 如果有理由在数据库中为每个人/水果的存储一条记录(例如，您需要保存水果被吃掉的时间等)，那么就没有理由在数据库中存储排名值，因为这将需要每个INSERT都有一个UPDATE。

使用聚合函数检索的排名

获取排名的查询：(假设每次出现都存储在Person_Fruit表中)

SELECT person, fruit, COUNT(*)
FROM person_fruit
WHERE person = 'the_person'
  AND fruit = 'the_fruit'
GROUP BY 1, 2;

存储在数据库中的排名

假设表Fruits (id，person，Fruits， )在person和fruit上有一个，所以每个唯一的组合只有一个匹配项。

在INSERT/UPDATE__，之前，检查人员/水果是否已经存在：

SELECT id
FROM fruits
WHERE name = 'the_name' AND fruit = 'the_fruit';

如果没有，则使用1__的等级值执行INSERT Person/Fruit，因为这是第一次出现：

INSERT INTO fruits (id, person, fruit, rank)
VALUES (NULL, 'the_person', 'the_fruit', 1);  /* NULL should be replaced by auto-generated value, if set up for that */

UPDATE fruits 
SET rank = rank +1
WHERE id = id        /* You can use id returned from previous `SELECT`, or Person AND Fruit */

Answer 2

您可以在当前表上编写AFTER INSERT触发器，用于计算水果排名并将记录插入到另一个表中。您可以使用以下查询来计算排名：

SELECT COUNT(*) into rank
FROM table 
WHERE person NEW.person and fruit = NEW.fruit;

获得排名后，可以执行INSERT查询将记录插入到另一个表中。

Here是AFTER INSERT触发器的一个例子。

Answer 3

从您的示例数据(以某种方式定义您脑海中的表结构的唯一可用方法)中，您不清楚为什么要保留具有绝对相同有效负载的不同记录。

基本上看起来你唯一在更新的就是排名。

因此，在这种情况下，排名自然与更新类似(而不是插入)：

UPDATE fruitRanks SET FruitRank=FruitRank+1 WHERE Fruit = 'apple' AND Name='Amy';

如果您的有效负载不同-那么使用单独的表(与AFTER UPDATE触发器结合使用)，那么就没有必要在每一行中保留Rank。

或者只需删除字段，并在需要时使用分组和聚合函数进行计算。