mysql支持list更新_MySQL查詢更新列表計數

I have a MySQL problem which I'm struggling to solve.

我有一個MySQL問題,我正在努力解決。

I have two tables. The first is a table of many thousands of garden items each with a groupid, with many items falling into any given group id. The second table is a summary table, simply listing the groupids and the count of items in each.

我有兩張桌子。第一個是由數千個花園項目組成的表,每個花園項目都有一個groupid,許多項目都屬於任何給定的組ID。第二個表是一個匯總表,只列出了groupids和每個中的項目數。

I need a query that will regularly update this second table o be run as part of a batch process overnight.

我需要一個定期更新第二個表的查詢,作為批處理過夜的一部分運行。

So far I have got this far - the first query selects the groups and counts in the first table and next to them what the new counts should be - I am not sure how to complete it:

到目前為止,我已經做到了這一點 - 第一個查詢選擇第一個表中的組和計數,然后在它們旁邊顯示新計數應該是什么 - 我不知道如何完成它:

SELECT l.listid,l.subscribecount,count(ls.listid)

FROM `lists` as l,`list_items` as ls

where l.listid=ls.listid

group by listid;

update lists

left join list_items on

lists.listid = list_items.listid

set

lists.subscribecount = count(list_items.listid);

Any help would be appreciated as I am pretty new to mysql.

任何幫助將不勝感激,因為我是mysql的新手。

1 个解决方案

#1

You could use the update-join syntax:

您可以使用update-join語法:

UPDATE lists l

JOIN (SELECT listid, COUNT(*) AS cnt

FROM list_items

GROUP BY listid) li ON l.listid = li.listid

SET l.subscribecount = cnt