多个表的SQL存储过程SUM [英] SQL Stored Procedure SUM of multiple tables
问题描述
你好，
我正在使用这种格式的几张桌子：
- dbo.January-
用户状态
=================
user1 123
user2 456
-dbo.February-
用户状态
== ===============
user1 149
user2 987
-dbo.YearToDate-
用户状态
=================
user1 ????
user2 ???
我想写一个存储过程，可以总结本月每个用户的统计数据表并将总数更新到YearToDate表。
我觉得应该很容易写，但我不知道如何开始。任何人都可以指出我正确的方向或有一些片段/例子吗？
谢谢你，非常感谢你的帮助！
Hello,
I'm working with a few tables with this format:
-dbo.January-
USER STATS
=================
user1 123
user2 456
-dbo.February-
USER STATS
=================
user1 149
user2 987
-dbo.YearToDate-
USER STATS
=================
user1 ???
user2 ???
I want to write a stored procedure that can sum up the stats for each user in the Month tables and update the total to the YearToDate table.
I feel should be easy to write, but I'm not sure how to start. Can anyone point me in the right direction or have some snippet/example?
Thank you, your help is much appreciated!
推荐答案
您还可以使用数据库视图来获得解决方案。这对您来说很容易，并且不需要通过存储过程手动更新视图，因为它将根据存储在表中的值自动更新。
如果您觉得此回复有用，那么您可以进一步询问我并澄清。
You can also use database views for the solution. It will be easy for you and there will be no need to update the view manually by stored procedure as it will update automatically according to the values stored in the tables.
If you find this reply useful then you can further ask me and get clarification.
在下面的链接中查看这三个概念。您可能最终使用一个创建表（temp或变量）的过程使用union来减少语句。然后使用子查询更新yearToDate。我将在这些链接下面写下更新声明。
UNION：
http://msdn.microsoft.com/en-us/library/ms180026.aspx
临时表，表变量
http://www.mssqltips.com/tip.asp?tip=1556
//如果你愿意，可以从工会声明创建bigTable ....
...
//更新yearToDate表中的值
update yearToDate
set stats =（select来自bigTable b的sum（b.stats），其中b.user = yearToDate.user）
Look at these three concepts in the links below. You might end up with a procedure with one create table (temp or variable) using a union to reduce the statements. Then do an update to yearToDate with a subquery. I'll write the update statement below these links.
UNION:
http://msdn.microsoft.com/en-us/library/ms180026.aspx
Temporary Tables, Table Variables
http://www.mssqltips.com/tip.asp?tip=1556
//create the bigTable from a union statement if you like here....
...
//update the values in the yearToDate table next
update yearToDate
set stats = (select sum(b.stats) from bigTable b where b.user = yearToDate.user)
插入YEARTODATE
SELECT iif（count（JAN.USER）> ;计数（FEB.USER），JAN.USER，FEB.USER）作为用户，Sum（FEB.STAR）+ SUM（JAN.STAR）AS STAR
来自FEB INNER JOIN JAN ON FEB。 USER = JAN.USER
GROUP BY FEB.USER，JAN.USER;
- try this
Insert into YEARTODATE
SELECT iif(count(JAN.USER)>count(FEB.USER), JAN.USER,FEB.USER) AS USER, Sum(FEB.STAR) + SUM(JAN.STAR) AS STAR
FROM FEB INNER JOIN JAN ON FEB.USER = JAN.USER
GROUP BY FEB.USER, JAN.USER;
--try this
这篇关于多个表的SQL存储过程SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
