基于不同表的SELECT(计数)的mySQL UPDATE表 [英] mySQL UPDATE table based on SELECT (count) of a different table

问题描述

我有一个班级表和一个主题表.

I have a table of classes, and a table of subjects.

CLASS
|class_id|class_name|subject_id|date_time|
------------------------------------------
(imagine some rows here)

SUBJECT
|subject_id|subject_name|current_class_count|
---------------------------------------------
(imagine some rows here)

出于索引目的，我想每小时更新一次主题列表，其中的类别列表现在的date_time比现在要大.

For indexing purposes I'd like to update the list of subjects every hour with the list of classes which have a date_time greater than right now.

我可以执行以下选择语句:

I can do a select statement like this:

SELECT count(*) AS num, subject_id
FROM class
GROUP BY subject_id
where date_time > NOW()

我会得到类似

RESULT
|num|subject_id|
----------------
| 8 |        1 |
| 6 |        2 |
| 9 |        3 |
----------------

用current_class_count更新主题表的最有效方法是什么?我可以通过遍历并执行几个更新语句来使用PHP来做到这一点，但我认为mySql应该有一种更简单的方法.

What's the most efficient way to get the subject table updated with the current_class_count? I could do this with PHP by looping through and doing several update statements, but I think mySql should have an easier way.

推荐答案

编辑:如何操作:

UPDATE SUBJECT
LEFT JOIN (
SELECT count(*) AS num, subject_id
FROM class
GROUP BY subject_id
where date_time > NOW()) AS t ON SUBJECT.subject_id = t.subject_id
SET SUBJECT.current_class_count = coalesce( t.num, 0 )

只要我输入正确，基本上您应该可以每小时运行一次，它将更新您的SUBJECT表.

As long as I've typed it right, basically you should be able to run this once an hour and it will update your SUBJECT table.

在MySQL中，与Microsoft SQL相比，在UPDATE语句中加入表有些不同.这是关于它的链接:

Joining a table in an UPDATE statement is a bit different in MySQL compared to Microsoft SQL. Here is a link about it:

http://blog.ookamikun.com/2008/03/mysql-update-with-join.html

这篇关于基于不同表的SELECT(计数)的mySQL UPDATE表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！