我需要根据其他记录中的其他值计算数据库中记录的值 [英] I need to calculate values for a record in a database based off of other values in other records

问题描述

我需要根据其他记录中的其他值计算数据库中记录的值.使用 SqlServer 2012，最好的方法是什么?我正在考虑在服务器上运行的某种类型的脚本，它可以查询它需要计算的值，计算它们并将它们插入到它需要的记录中.我知道您可以基于 SqlServer 中的其他列计算列，但是基于不同记录中不同列的新记录呢?

I need to calculate values for a record in a database based off of other values in other records. Using SqlServer 2012, what would be the best way to do this? I'm thinking some type of script that runs on the server that may be able to query for the values it needs to compute, compute them, and insert them into the record it needs to. I know you can have computed columns based off of other columns in SqlServer, but what about new records based off of different columns in different records?

谢谢！

我在 MVC4 Razor 网站上使用谷歌图表表格来显示特定用户按月和年购买的商品；看起来像这样:

I'm using a google charts table on an MVC4 Razor website to show items purchased by specific users by month and year; looks something like this:

电子邮件地址 |购买价值 |年 |月

Email Address | Purchase Value | Year | Month

这目前工作得很好.我查询数据库中按用户和按月和年分组的购买，并对购买求和，然后将值放入表中.我也有只显示一个月和一年的类别过滤器，所以一次只显示一个用户.

This currently works absolutely fine. I query the database for purchases by user and group by month and year and sum the purchases, and I put the values in the table. I also have category filters that only show one month and one year, so only one user is shown at a time.

现在管理层想要在类别过滤器上选择全部"，这意味着对于每年的每个月和每年的总计，我将不得不计算每个用户的累积购买价值并将其放入桌子;您可以想象，如果用户列表变得很长，这可能需要一些时间.因此，我认为最好的选择可能是拥有一个脚本，按年份和用户分组购买，并在该年度内的任何时间每次捐赠时更新新记录；显然，你会为一年中的每个月做同样的事情.这样，当用户请求页面时，我不必担心计算这个.我只是不确定如何为 SQLServer 编写一个能够执行此类操作的脚本.

Now management wants an 'All' selection on the category filter, which means that for every month of every year, and every year total, I'm going to have to compute a cumulative purchase value for each user and put it in the table; you can imagine, if the users list gets very long, this could take some time. So, I think the best option would probably be to have a script that groups purchases by year and by user and updates a new record every time a donation is made anytime within that year; obviously, you'd do the same for each month of the year. That way, I wouldn't have to worry about computing this when the user requests the page. I'm just not sure how to go about writing a script for SQLServer that would be able to do something like this.

推荐答案

这显示了如何根据其他记录中的其他值计算数据库中记录的值.该示例是用 TSQL 编写的，可以在 SQL Server 上执行.您将需要更改脚本以使用您的表和列.

This shows how to calculate values for a record in a database based off of other values in other records. The example is written in TSQL and can be executed on SQL Server. You will need to change the script to use your tables and columns.

DECLARE @total dec(12,2), @num int --Variable declaration

SET @total = (SELECT SUM(Salary) FROM Employee) --Capture sum of employee salaries

SET @num = (SELECT COUNT(ID) FROM Employee) --Capture the number of employees

SELECT @total 'Total', --calculate values for a record in a database based off of other values in other records
        @num 'Number of employees',
        @total/@num 'Average'
INTO
    dbo.AverageSalary

希望这会有所帮助.

这篇关于我需要根据其他记录中的其他值计算数据库中记录的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！