例如,简单表 Inventory 中包含:Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH ROLLUPItem Color QtySum -------------------- -------------------- -------------------------- Chair Blue 101.00 Chair Red 210.00 Chair ALL 311.00 Table Blue 124.00 Table Red 223.00 Table ALL 347.00 ALL ALL 658.00 (7 row(s) affected)
晕,我的意思是统计每个分类下的新闻条数,然后直接更新对应的分类表中的新闻条数字段Select Count([新闻ID]),[新闻所属分类ID] From [新闻表] Group By [新闻所属分类ID]这样查出来后,还得用循环去更新分类表中每个分类对应的条数.Update [分类表] Set [新闻数] Where [分类ID] = @ID 有没有办法一条SQL命令或存取过程来实现?
存取过程如何来循环记录集? SQL有没有办法来做到
汗..再加30分帮我看看如何做到,SQL或存取过程如何写,谢谢
update 分类表 set 分类表.新闻数 = T1.新闻数 from(select count(新闻ID) 新闻数, 新闻所属ID from 新闻表 group by 新闻所属ID)T1 where 分类表.分类ID = T1.新闻所属ID
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00 (7 row(s) affected)
有没有办法一条SQL命令或存取过程来实现?
SQL有没有办法来做到
set 分类表.新闻数 = T1.新闻数
from(select count(新闻ID) 新闻数, 新闻所属ID from 新闻表 group by 新闻所属ID)T1
where 分类表.分类ID = T1.新闻所属ID