問題描述

我需要根據其他記錄中的其他值計算數據庫中記錄的值.使用 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

希望這會有所幫助.

這篇關于我需要根據其他記錄中的其他值計算數據庫中記錄的值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！