問題描述
下面是我用來創建視圖的 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 行靜態值之后使用公式的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！