栏目表:Category字段如下
CategoryId // 栏目ID
ArticleNum // 该栏目下的内容总数现在重新统计。再SQL SERVER 2000 中的写法如下:UPDATE Category SET ArticleNum=ISNULL((SELECT COUNT(*) FROM Article WHERE CategoryId=Category.CategoryId),0)问题是再 ACCESS 数据库中 如何用一条更新语句实现呢??
CategoryId // 栏目ID
ArticleNum // 该栏目下的内容总数现在重新统计。再SQL SERVER 2000 中的写法如下:UPDATE Category SET ArticleNum=ISNULL((SELECT COUNT(*) FROM Article WHERE CategoryId=Category.CategoryId),0)问题是再 ACCESS 数据库中 如何用一条更新语句实现呢??
SET ArticleNum = b.cnt
from category a,
(select categoryid,count(1) as cnt from article group by categoryid) b
where a.CategoryId = b.CategoryId
set ArticleNum = n.ArticleNum
from CategoryId m ,
(select CategoryId , count(*) from Category group by CategoryId) n
where m.CategoryId = n.CategoryId
ACCESS 有的不是很多
update a
set a.ArticleNum = b.cn
from Category a join (
select CategoryId ,cn = sum(1)
from Article group by CategoryId
)b
on a.CategoryId = b.CategoryId