问题描述
大家好!!!
我表中有一些值，我需要按如下方式计算总和
HI all !!!
I have some values in table and i need to calculate the sum as follows
MonthlyContri || Arrears || total || MonthlyTotal
1000 || 0 || 1000 1000
1000 || 0 || 1000 || 2000
1000 || 2000 || 3000 || 5000
1000 || 1000 || 2000 || 7000
MonthlyContri + Arrears =总计
现在，每月总计=最近的每月总计(在前一行中)+总计现在
MonthlyContri +Arrears = total
Now Monthly total = Last MonthlyTotal(in the previous row) +TotalNow
for ex here Monthly total in second row = 1000 +1000 =2000
third row = 2000 + 3000= 5000
[edit]已添加代码块-OriginalGriff [/edit]
[edit]Code block added - OriginalGriff[/edit]
推荐答案
IF NOT OBJECT_ID(N'#Sale',N'U') IS NULL
DROP TABLE #Sale
CREATE TABLE #Sale (ProductName VARCHAR(30), CountOfPosition INT, TotalPrice FLOAT, SaleDate DATETIME)
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-01-14')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-01-22')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 998, 4987.65, '2012-01-31')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1012, 5100.12, '2012-02-01')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1099, 5719.78, '2012-02-16')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 888, 4567.32, '2012-02-28')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-03-05')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-03-12')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 898, 4987.65, '2012-03-29')
DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR (2000)
DECLARE @pt VARCHAR(4000)
SET @cols = '[1],[2],[3]'
SET @dt = 'SELECT ProductName, TotalPrice, MONTH(SaleDate) AS SaleMonth ' +
'FROM #Sale ' +
'WHERE YEAR(SaleDate) = 2012 '
--EXEC(@dt)
SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' +
'ORDER BY PT.ProductName'
EXEC (@pt)
DROP TABLE #Sale
结果:
Result:
ProductName 1 2 3
Fruits 15300,65 15387,22 15300,65
如您所见，为每种产品(在这种情况下仅是水果)和2012年的每个月都提供了计算.
要获得每个产品的总和，我们需要更改数据透视查询，删除ORDER BY子句:
As you see, calculations are provided for each product (in this case only fruits) and for each month of year 2012.
To get total sum for each product, we need to change pivot query, removing ORDER BY clause:
SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' -- +
--'ORDER BY PT.ProductName'
EXEC (@pt)
--declare variables
DECLARE @fq VARCHAR(4000)
DECLARE @cols2 VARCHAR (200)
--set variables to get total sum for months: 1-3
SET @cols2 = '[1] + [2] + [3]'
SET @fq = 'SELECT ProductName, ' + @cols + ', ' + @cols2 + ' AS TotalSum ' +
'FROM (' + @pt + ') AS FQ'
EXEC (@fq)
更多信息:
使用PIVOT和UNPIVOT [ ^ ]
在此站点上搜索以找到更多示例.
More at:
Using PIVOT and UNPIVOT[^]
Search this site to find more examples.
Dim con As SqlConnection=New Sqlconnection("Your Connection string")
Dim cmd As SqlCommand
Dim ds As DataSet
Dim da As SqlDataAdapter
con.open()
da = New SqlDataAdapter("select * from sample order by mtotal desc", con)
ds = New DataSet()
da.Fill(ds)
dim lasttotal as string =ds.tables(0).rows(0)("lasttotal").tostring()
dim total as integer=txtmcontr+arrears
dim monthlytotal as integer=convert.toint32(lasttotal)+total
cmd=New SqlCommand("insert into sample (moncontr,arrears,total,mtotal) values('"+txtmcontr+"','"+arrears
+"','"+total+"','"+monthlytotal+"')")
cmd.executenonquary()
con.close()
这篇关于计算中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！