从多个表中选择一列中的多个列，并使用SQL SERVER中的上一行计算金额 [英] Select From Multiple Tables Multiple Columns In One Column and Calculate an Amount using the previous row in SQL SERVER

问题描述

你好！



我会很简单：）



我有：



- 3桌子

- 每张桌子都有专栏（DATE）和（AMOUNT）





表X



日期|金额_X

01/01/2015 | 3000



表Y



日期|金额_Y

02/01/2015 | 1000



表Z



日期| Amount_Z

03/01/2015 | 1000





我需要通过4列获得此结果：



日期|金额类型|金额|计算



01/01/2015 | Amount_X | 3000 | 3000（= 0 + 3000）

02/01/2015 | Amount_Y | 1000 | 2000（= 3000 - 1000）

03/01/2015 | Amount_Z | 1000 | 1000（= 2000 - 1000）



等等...



所以：

如果我们有Amount_X我们添加（+）

如果我们有Amount_Y或Amount_Z我们减去（ - ）

所以我们必须得到的价值列（计算）中的上一行。第一行没有前一行所以值为Null（0）。



（注意：我想我会使用CROSS APLY和ROW_NUMBER但是我没有我知道如何摆弄它。但是你可以给我最好的解决方案。





如果需要，我会提出我的问题



谢谢：）

解决方案

一种方法是使用递归公用表表达式（ cte）。



您可以尝试这样的事情：

   -   将cte视为专用临时表，每个临时表都封装在括号（）中。 
   -    cte从使用with子句开始，确保使用;终止所有其他语句在使用with子句之前。 
   -   注意一些人喜欢写;在使用时with。 
   CombineTable  as （
   -   设置虚拟数据 
   -   < span class =code-comment>这里是有问题的表格数据 
   -    table_X数据 
 选择 
 转换（ datetime ，'  20150101'， 121 ）[日期]，
 '  Amount_X' [金额类型]，
  3000 金额
  union  所有 选择 转换（ datetime ，'  20150115'， 121 ），'  Amount_X'， 5000  
  union  所有 选择 转换（ datetime ，'  20150109'， 121 ），'  Amount_X'， 6000  
  union  全部 < span class =code-keyword>选择 转换（ datetime ，'  20150110'， 121 ），'  Amount_X'， 7000  
  union  所有 选择 转换（ datetime ，'  20150104' ， 121 ），'  Amount_X'， 8000  
 
   -    table_Y data  
  union   all  选择 转换（ datetime ， '  20150102'， 121 ），'   Amount_Y'， 1000  
  union  所有 选择 转换 （ datetime ，'  20150106'，< span class =code-digit> 121 ），'  Amount_Y'， 3000  
  union  所有 选择 转换（ datetime ，'  20150107'， 121 ），'  Amount_Y'， 2000  
 
 < span class =code-comment>  -    table_Z data  
  union  所有 选择 转换（ datetime ，'  20150103'， 121 ），'  Amount_Z'， 1000  
  union   all  选择 转换（ datetime ，'  <温泉n class =code-string> 20150121'， 121 ），'  Amount_Z'， 9000  
  union  < span class =code-keyword> all   select   convert （ datetime ，'  20150115'， 121 ），'  Amount_Z'， 3000  
 
），CombineTableRowID  as （
   -  查询上面的CombineTable cte以创建按[Date]排序的新rowid列 
  select  
 row_number（） over （ order  按 [日期]）rowid，
 * 
 来自 CombineTable 
），CombineTableCalc  as （
  -   现在这里有一个棘手的位置，递归cte  
 
   -   第一部分查询CombineTableRowID以获取锚记录 
   -   将锚记录视为其他记录将被取消的根（第一个父记录） 
 选择 
 ctrid.rowid，
 ctrid。[ Date ]，
 ctrid。[Amount  Type ]，
 ctrid。[金额]，
   -   检查是否金额应加或减 
   -   将是第一个记录，因此金额将被添加或减去0  
  case  
 何时 [金额类型] = '  Amount_X' 然后 
   -   将金额添加为金额_X  
  0  +金额
 其他 
   -   减去金额不是Amount_X因此将是Amount_Y或Amount_Z  
  0   - 金额
  end  
计算
 来自 CombineTableRowID ctrid 
   -   此过滤器将获取锚记录的第一行 
 其中 rowid =  1  
 
   -   现在这里是递归部分 
  union   all  
 选择 
 ctridC.rowid，
 ctridC。[日期]，
 ctridC。[金额类型]，
 ctridC。[Amount]，
   -   在派生计算中添加或减去金额 
 案例 
 何时 ctridC。[Amount  Type ] = '  Amount_X' 然后 
 ccc.Calculati on + ctridC.Amount 
  else  
 ctc.Calculation  -  ctridC.Amount 
  end  
计算
   -   再次查询CombineTableRowID  
   -   将此视为子表 
 来自 CombineTableRowID ctridC 
 
   -   记下连接表是否在本身是相同的cte  
   -   这是递归（魔法）发生的地方 
   -   将此视为父表 
 内部  join  CombineTableCalc ctc 
   -   如你所见，这里是父和子表之间匹配的地方 
   ctc.rowid = ctridC .rowid  -   1  
 
）
   -   最后从CombineTableCalc查询以获得递归结果 
 选择 * 
 来自 CombineTableCalc 
 订单 按 [日期] 
; 

希望有所帮助:)



以下是一些链接，可能有所帮助：

使用SQL Server CTE的Fibonacci序列：使用SQL Server CTE的Fibonacci序列 [ ^ ]

使用公用表格表达式： https://technet.microsoft.com/en-us/library/ms190766(v = sql.105）.aspx [ ^ ]

OVER条款（Transact-SQL）： https://msdn.microsoft.com/en-gb/library/ms189461 .aspx [ ^ ]

CASE（Transact-SQL）： https://msdn.microsoft.com /en-us/library/ms181765.aspx [ ^ ]

Hello !

I will make it simple : )

I have :

- 3 Tables
- Each Table has Columns (DATE) and (AMOUNT)


Table X

Date | Amount_X
01/01/2015 | 3000

Table Y

Date | Amount_Y
02/01/2015 | 1000

Table Z

Date | Amount_Z
03/01/2015 | 1000


I need to get this result with 4 Columns :

Date | Amount Type | Amount | Calculation

01/01/2015 | Amount_X | 3000 | 3000 ( = 0 + 3000 )
02/01/2015 | Amount_Y | 1000 | 2000 ( = 3000 - 1000 )
03/01/2015 | Amount_Z | 1000 | 1000 ( = 2000 - 1000 )

And So on ...

So :
If we have Amount_X We add ( + )
If we have Amount_Y Or Amount_Z We substract ( - )
So we have to get the value of the previous row in Column (Calculation). There is no previous row for the first row so The value is Null (0).

( NB : I think I will use CROSS APLY and ROW_NUMBER But I do not know how to hundle it. But it's up to you to give me the best solution )


I will improuve my question if needed

Thank You : )

解决方案

One way of doing it, is by using recursive common table expressions (cte).

You could try something like this:

--consider cte as being specialized temp tables, each one being encapsulated within the parenthesis "()".
--the cte starts off using the with clause, ensure all other statements are terminated using the ";" before using the with clause.
--note some like to write ;with when using the with.
with CombineTable as (
--setup dummy data
--here is the table data in question
	--table_X data
	select 
		convert(datetime, '20150101',121) [Date], 
		'Amount_X' [Amount Type], 
		3000 Amount
	union all select convert(datetime, '20150115',121), 'Amount_X', 5000
	union all select convert(datetime, '20150109',121), 'Amount_X', 6000
	union all select convert(datetime, '20150110',121), 'Amount_X', 7000
	union all select convert(datetime, '20150104',121), 'Amount_X', 8000

	--table_Y data
	union all select convert(datetime, '20150102',121), 'Amount_Y', 1000
	union all select convert(datetime, '20150106',121), 'Amount_Y', 3000
	union all select convert(datetime, '20150107',121), 'Amount_Y', 2000

	--table_Z data
	union all select convert(datetime, '20150103',121), 'Amount_Z', 1000
	union all select convert(datetime, '20150121',121), 'Amount_Z', 9000
	union all select convert(datetime, '20150115',121), 'Amount_Z', 3000

), CombineTableRowID as (
	--query the CombineTable cte above to create a new rowid column ordered by [Date]
	select
		row_number() over (order by [Date]) rowid,
		*
	from CombineTable
), CombineTableCalc as (
--now here comes the tricky bit with recursive cte

	--first part query the CombineTableRowID to get an anchor record
	--consider the anchor record to be the root (first parent) record the other records will feed off
	select
		ctrid.rowid,
		ctrid.[Date],
		ctrid.[Amount Type],
		ctrid.[Amount],
		--check to see if Amount should be added or subtracted
		--will be first record so the Amount will be added or subtracted to 0
		case
			when [Amount Type] = 'Amount_X' then
				--add the Amount as Amount_X
				0 + Amount
			else
				--minus the Amount as not Amount_X so will be either Amount_Y or Amount_Z
				0 - Amount
			end
		Calculation
	from CombineTableRowID ctrid
	--this filter will get the first row for the anchor record
	where rowid = 1

	--now here comes the recursive part
	union all
	select 
		ctridC.rowid,
		ctridC.[Date],
		ctridC.[Amount Type],
		ctridC.[Amount],
		--add or subtract the Amount to the derived Calculation
		case
			when ctridC.[Amount Type] = 'Amount_X' then
				ctc.Calculation + ctridC.Amount
			else
				ctc.Calculation - ctridC.Amount
			end
		Calculation
	--querying the CombineTableRowID again
	--consider this to be the Child table 
	from CombineTableRowID ctridC

	--make note the join table is in itself the same cte
	--this is where the recursion (magic) happens
	--consider this to be the Parent table
	inner join CombineTableCalc ctc
		--as you can see, here is where the match up occurs between the  Parent and Child table
		on ctc.rowid = ctridC.rowid - 1

)
--and lastly query from the CombineTableCalc to get the recursive results
select * 
from CombineTableCalc
	order by [Date]
;

Hope that helps out :)

Here are a few links, which may help:
Fibonacci sequence using SQL Server CTE: Fibonacci sequence using SQL Server CTE [^]
Using Common Table Expressions: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx[^]
OVER Clause (Transact-SQL): https://msdn.microsoft.com/en-gb/library/ms189461.aspx[^]
CASE (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms181765.aspx[^]

这篇关于从多个表中选择一列中的多个列，并使用SQL SERVER中的上一行计算金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！