在第 13 行动态和第 12 行静态值之后使用公式

问题描述

下面是我用来创建视图的 SQL 查询

Below is the SQL Query I used to create as view

CREATE VIEW [v_AMP_C] AS


    SELECT     dbo.IC_Raw_In.I_Date, dbo.IC_Raw_In.I_O_P, dbo.IC_Raw_In.I_O_H, dbo.IC_Raw_In.I_O_L, dbo.IC_Raw_In.I_C_O, dbo.AMPS12_C.AMPS12_C
    FROM         dbo.IC_Raw_In INNER JOIN
                          dbo.AMPS12_C ON dbo.IC_Raw_In.I_Serial = dbo.AMPS12_C.i_serial

使用批量插入dbo.IC_Raw_In将数据导入该表，数据类型为Money，I_Date除外.

and the data is imported to this table by useing a bulk insert dbo.IC_Raw_In and data type are Money except I_Date.

然后当我运行查询时，即.

Then when i ran the query i.e,.

select * from v_AMP_C i 得到以下作为输出

I_Date  I_O_P   I_O_H   I_O_L   I_C_O   AMPS12_C
01/10/11    509.75  515 508 512.45  512.45
01/10/11    511.7   511.7   506.1499    506.5499    509.4999
01/10/11    507.1499    510.25  507.1499    510.25  509.7499
01/10/11    510 512.3499    509.2999    512.3499    510.3999
01/10/11    512.5   512.5   511.1499    512 510.7199
01/10/11    512.25  512.5   510.1   510.95  510.7583
01/10/11    510.5499    511.7999    510 511.7999    510.9071
01/10/11    511.1   511.85  508.1499    508.8999    510.6562
01/10/11    508.8999    510 508.5   509.95  510.5777
01/10/11    509.8999    509.8999    508.5   508.85  510.4049
01/10/11    509.5   511.2   509 510.5   510.4136
01/10/11    510.5   511.7999    510.1   510.2   [b]510.3958[/b]
01/10/11    510.2999    511.35  510.25  510.75  510.2541
01/10/11    510.35  512 510.35  510.95  510.6208
01/10/11    510.95  511.7999    510.6   511.1   510.6916
01/10/11    511.0499    511.35  509.1   509.1   510.4208
01/10/11    509.5   509.5   508.1   508.5   510.1291
01/10/11    508.45  508.95  507 507 509.7999
01/10/11    507 508.2   503.2999    503.2999    509.0916
01/10/11    504 505 503.5   504.6499    508.7374
01/10/11    505.45  506.35  504 504.7   508.2999
01/10/11    504.7   505.5   504.2   505.5   508.0208
01/10/11    505.35  505.7   503.1   503.6499    507.4499
01/10/11    504.5   504.5   499.5499    500.5   506.6416
01/10/11    500.45  502 500.25  501 505.8291
01/10/11    501 501.2999    499.5499    500.3999    504.9499
01/10/11    500.45  500.7999    498.6499    498.6499    503.9124
01/10/11    498.7   499.25  498.0499    498.35  503.0166
01/10/11    498.75  499.95  498.7   499 502.2249
01/10/11    499.25  499.6499    498.6499    499.45  501.5957
01/10/11    499.2999    501.1499    499.1   500.8999    501.3957
01/10/11    501.1   502.5   500.5499    502.5   501.2166
01/10/11    502.35  502.95  501 501.5   500.9499
01/10/11    501.5   501.5   500 500.5   500.5333
01/10/11    500 501.35  499.5   499.7999    500.2124
01/10/11    499.95  500.3999    499.2999    500.2999    500.1957
01/10/11    500 501.3999    499.5   499.6499    500.0832
01/10/11    499.7999    501.25  499.6499    500.0499    500.0541

现在我想编写一个 SQL 查询，以在名为 C12WR 的新列中获取结果以解决以下问题.

Now I want write a SQL Query to get results in new Column called C12WR for the below question.

我想排除(使用 NULL)C12WR 列的前 11 行，并在 C12WR 列的第 12 行使用AMPS12_C"中的静态值(在上表结果中，值为510.3958"标记为粗体.每次我将数据导入我的表时，该值都会发生变化，因此每次都会动态变化.并且在 AMPS12_C 列中，它应该在第 13 行到表末尾之后计算以下公式.

I want to exclude(Use NULL) the first 11 rows in C12WR Column, and in 12th row of C12WR Column "use a static value which is in "AMPS12_C" (In the above shown table results the value is "510.3958" marked as bold). This value will change every time i import the data to my table so the will change dynamically every time. And in the AMPS12_C Column it should calculate the below formula after 13th row to end of the table.

After 13th Row in C12WR Column = (the value of above row (that is currunt row number -1) from C12WR *11 + Current row value from I_C_O Column) /12

所以如果我计算它应该代表上面的公式如下..(我不想使用任何静态值......对于这个公式的例子，我在这里采用静态值只是为了结果容易解释)

so if i calculate it should represent above formulas as below..(I don't want to use any static values...for the example of this formula I am taking a static values here just for the results to easy of explanation)

=(510.3958*11+510.2)/12

在运行所需的查询后，我应该得到类似于下面的输出

and after run the desired query i should get the output similar to below

I_Date  I_O_P   I_O_H   I_O_L   I_C_O   AMPS12_C    C12WR
01/10/11    509.75  515 508 512.4500122 512.45  NULL
01/10/11    511.7000122 511.7000122 506.1499939 506.5499878 509.4999    NULL
01/10/11    507.1499939 510.25  507.1499939 510.25  509.7499    NULL
01/10/11    510 512.3499756 509.2999878 512.3499756 510.3999    NULL
01/10/11    512.5   512.5   511.1499939 512 510.7199    NULL
01/10/11    512.25  512.5   510.1000061 510.9500122 510.7583    NULL
01/10/11    510.5499878 511.7999878 510 511.7999878 510.9071    NULL
01/10/11    511.1000061 511.8500061 508.1499939 508.8999939 510.6562    NULL
01/10/11    508.8999939 510 508.5   509.9500122 510.5777    NULL
01/10/11    509.8999939 509.8999939 508.5   508.8500061 510.4049    NULL
01/10/11    509.5   511.2000122 509 510.5   510.4136    NULL
01/10/11    510.5   511.7999878 510.1000061 510.2000122 510.3958333 510.3958333
01/10/11    510.2999878 511.3500061 510.25  510.75  510.2541657 510.3795149
01/10/11    510.3500061 512 510.3500061 510.9500122 510.6208344 510.4103887
01/10/11    510.9500122 511.7999878 510.6000061 511.1000061 510.6916682 510.4553573
01/10/11    511.0499878 511.3500061 509.1000061 509.1000061 510.4208374 510.509078
01/10/11    509.5   509.5   508.1000061 508.5   510.1291707 510.3916554
01/10/11    508.4500122 508.9500122 507 507 509.8000031 510.2340174
01/10/11    507 508.2000122 503.2999878 503.2999878 509.0916697 509.964516
01/10/11    504 505 503.5   504.6499939 508.7375031 509.4091386
01/10/11    505.4500122 506.3500061 504 504.7000122 508.3000031 509.0125432
01/10/11    504.7000122 505.5   504.2000122 505.5   508.0208359 508.6531656
01/10/11    505.3500061 505.7000122 503.1000061 503.6499939 507.450002  508.3904018
01/10/11    504.5   504.5   499.5499878 500.5   506.6416677 507.9953678
01/10/11    500.4500122 502 500.25  501 505.8291677 507.3707539
01/10/11    501 501.2999878 499.5499878 500.3999939 504.9499995 506.8398577
01/10/11    500.4500122 500.7999878 498.6499939 498.6499939 503.9124985 506.3032024
01/10/11    498.7000122 499.25  498.0499878 498.3500061 503.0166651 505.665435
01/10/11    498.75  499.9500122 498.7000122 499 502.2249985 505.0558159
01/10/11    499.25  499.6499939 498.6499939 499.4500122 501.5958328 504.5511646
01/10/11    499.2999878 501.1499939 499.1000061 500.8999939 501.3958333 504.1260686
01/10/11    501.1000061 502.5   500.5499878 502.5   501.2166672 503.857229
01/10/11    502.3500061 502.9500122 501 501.5   500.9499995 503.7441266
01/10/11    501.5   501.5   500 500.5   500.5333328 503.557116
01/10/11    500 501.3500061 499.5   499.7999878 500.212499  503.3023564
01/10/11    499.9500122 500.3999939 499.2999878 500.2999878 500.1958313 503.0104923
01/10/11    500 501.3999939 499.5   499.6499939 500.0833308 502.784617
01/10/11    499.7999878 501.25  499.6499939 500.0499878 500.0541636 502.5233984

寻求帮助写上面的SQL Query

looking for help to write above SQL Query

推荐答案

这个概念有一定的错误" - 主要是因为 X 行和 Y 行之间的任何此类行相互关系都依赖于按指定顺序排列的行- 我没有看到现在.IT 与 SQL 语言的基于集合的方面背道而驰.

There is a certain 'wrongness' with the concept - primarily because any such row inter-relation between row X and Y relies on the rows being in a specified order - which I do not see present. IT goes against the set-based aspects of the SQL language in effect.

就 SQL 而言 - 缓慢/昂贵的方法是将表连接到自身，但您必须能够使用排名函数创建行号确定性才能使其工作.

In terms of SQL - the slow / expensive way to do it is to join the table to itself, but you have to be able to create row numbers deterministicallt using a ranking function for this to work.

标准 T-SQL 没有 Lag/Lead 能力(引用上一行/下一行的能力.) - 它确实存在于 MDX 语言中，但这使这进一步复杂化.

Standard T-SQL does not have a Lag / Lead ability (the abilty to reference the previous / nextrow.) - It does exist in the MDX langauge but that complicates this further.

这篇关于在第 13 行动态和第 12 行静态值之后使用公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！