问题

我刚开始学习MySQL和关系型数据库。我决定尝试构建一个非常简单的每日习惯跟踪数据库/应用程序，作为学习过程的一部分。我取得了一些进展，其中很大一部分归功于这里提供的所有出色信息，但我还没有能够解决这个问题。以下是我的两个表格。

habit_log

| id | date  | daily_habit_text | daily_habit_value |
-----------------------------------------------------
| 1  | date1 | Good             | 4                 | 
| 2  | date2 | Bad              | 2                 |
| 3  | date3 | OK               | 3                 |
| 4  | date4 | Good             | 4                 |
| 5  | date5 | Great            | 5                 |
-----------------------------------------------------

habit_values

| habit_value | habit_text |
----------------------------
| 1           | Terrible   | 
| 2           | Bad        |
| 3           | OK         |
| 4           | Good       |
| 5           | Great      |
----------------------------

habit_log 是我每日记录的表格，记录了我每天的表现，它会随着时间的推移而增长。habit_values 是包含评分值和文本的表格。daily_habit_value 列是与 habit_value 列相关联的外键。

我想要一个列，要么在 habit_values 表中，要么在一个新的表中，显示每个 habit_value 在 habit_log 表中出现的次数。然后我将用它来创建一个图表。这个列需要在每次向 habit_log 表中添加新行时更新。

我尝试了许多不同的方法，包括计数、连接和外键，但都没有成功。我查看了很多帖子，但似乎没有一个特别适合我的要求，或者我只是不了解足够多，无法看出它们之间的关系。看到解决方案，或者一个解决方案，将真正帮助我看清楚我所忽略的东西，并指引我深入学习的方向。

habit_log

| id | date  | daily_habit_text | daily_habit_value |
-----------------------------------------------------
| 1  | date1 | Good             | 4                 | 
| 2  | date2 | Bad              | 2                 |
| 3  | date3 | OK               | 3                 |
| 4  | date4 | Good             | 4                 |
| 5  | date5 | Great            | 5                 |
-----------------------------------------------------

habit_values

| habit_value | habit_text |
----------------------------
| 1           | Terrible   | 
| 2           | Bad        |
| 3           | OK         |
| 4           | Good       |
| 5           | Great      |
----------------------------

答案1

得分: 2

你提供的SQL查询:
``` sql
SELECT
  hv.habit_value,
  hv.habit_text,
  COUNT(hl.id) AS habit_count
FROM
  habit_values hv
LEFT JOIN
  habit_log hl ON hv.habit_value = hl.daily_habit_value
GROUP BY
  hv.habit_value,
  hv.habit_text;

这个查询执行了habit_values和habit_log表的连接，以计算每个habit_value的出现次数。它使用了LEFT JOIN以确保将habit_values表的所有行都包括在结果中，通过匹配habit_log表中的daily_habit_value来计数出现次数。结果包括每个habit_value，它对应的habit_text，以及habit_count列作为计数。

触发器方法:

CREATE TRIGGER update_habit_count AFTER INSERT ON habit_log
FOR EACH ROW
BEGIN
    UPDATE habit_values
    SET count = count + 1
    WHERE habit_value = NEW.daily_habit_value;
END;

这个触发器在每次向habit_log表插入新行时执行。触发器使用NEW关键字引用新插入的行。当触发时，它通过将count值增加1来更新habit_values表中与daily_habit_value匹配的行的计数值。

通过使用这个触发器，count值会在每次向habit_log表插入新行时自动更新。

这两种方法都达到了更新habit_values表中count列的目标。使用SQL查询，您可以根据需要手动执行查询以获取最新的结果。另一方面，触发器方法确保每当插入新行时，count值都会自动更新，保持最新。

<details>
<summary>英文:</summary>

**The SQL query you provided:**
``` sql
SELECT
  hv.habit_value,
  hv.habit_text,
  COUNT(hl.id) AS habit_count
FROM
  habit_values hv
LEFT JOIN
  habit_log hl ON hv.habit_value = hl.daily_habit_value
GROUP BY
  hv.habit_value,
  hv.habit_text;

This query performs a join between the habit_values and habit_log tables to calculate the occurrence count of each habit_value. It uses a LEFT JOIN to ensure all rows from the habit_values table are included in the result, and it counts the occurrences by matching the daily_habit_value from the habit_log table. The result includes each habit_value, its corresponding habit_text, and the count as habit_count column.

The trigger-based approach:

CREATE TRIGGER update_habit_count AFTER INSERT ON habit_log
FOR EACH ROW
BEGIN
    UPDATE habit_values
    SET count = count + 1
    WHERE habit_value = NEW.daily_habit_value;
END;

This trigger is executed whenever a new row is inserted into the habit_log table. The trigger references the newly inserted row using the NEW keyword. When triggered, it updates the count value by incrementing it by 1 for the row in the habit_values table that matches the daily_habit_value.

By using this trigger, the count value is automatically updated whenever a new row is added to the habit_log table.

Both approaches achieve the goal of updating the count column in the habit_values table. With the SQL query, you can manually execute the query whenever needed to obtain the latest results. On the other hand, the trigger-based approach ensures that the count value is always up to date by automatically updating it whenever a new row is inserted.

I thought it would be best to get the values with a SQL query and cache them.