SQL Query Help after 13th row Dynamic and in 12th row static values with a formula [英] SQL Query Help after 13th row Dynamic and in 12th row static values with a formula
问题描述
我有下面的表格结构，类似于下面的输出.
I have below table structure with the similar to below output.
S_NO_ T_P O_P H_P L_P C_P SC_12
1 1 509.75 515 508 512.4500122
2 2 511.7000122 511.7000122 506.1499939 506.5499878
3 4 507.1499939 510.25 507.1499939 510.25
4 5 510 512.3499756 509.2999878 512.3499756
5 3 512.5 512.5 511.1499939 512
6 8 512.25 512.5 510.1000061 510.9500122
7 1 510.5499878 511.7999878 510 511.7999878
8 2 511.1000061 511.8500061 508.1499939 508.8999939
9 5 508.8999939 510 508.5 509.9500122
10 6 509.8999939 509.8999939 508.5 508.8500061
11 8 509.5 511.2000122 509 510.5
12 9 510.5 511.7999878 510.1000061 510.2000122 **510.4**
13 12 510.2999878 511.3500061 510.25 510.75 510.25
14 14 510.3500061 512 510.3500061 510.9500122 510.62
15 15 510.9500122 511.7999878 510.6000061 511.1000061 510.69
16 17 511.0499878 511.3500061 509.1000061 509.1000061 510.42
17 13 509.5 509.5 508.1000061 508.5 510.13
18 13 508.4500122 508.9500122 507 507 509.8
19 19 507 508.2000122 503.2999878 503.2999878 509.09
20 11 504 505 503.5 504.6499939 508.74
21 17 505.4500122 506.3500061 504 504.7000122 508.3
22 18 504.7000122 505.5 504.2000122 505.5 508.02
23 19 505.3500061 505.7000122 503.1000061 503.6499939 507.45
我想写一个 SQL 查询来获取 C12WR 列中的结果.
I want write a SQL Query to get results in C12WR column.
我想在 C12WR 列的行上使用SC_12"中的静态值(在显示的表中，值为510.4")，在 SC_12 列中，它应该在第 13 行号之后计算以下公式，它继续直到表记录结束C12WR 列第 13 行后 =(上一行的值*11 + C_P 列的当前行值)/12
And I want on row of C12WR Column use a static value which is in "SC_12" (In the shown table the value is "510.4") and in the SC_12 Column it should calculate the below formula after 13th row number and it continue it till the end of the table records After 13th Row in C12WR Column = (the value of above row*11 + Current row value from C_P Column) /12
The final output should be similar to below
S_NO_ T_P O_P H_P L_P C_P SC_12 c12-WWR
1 1 509.75 515 508 512.4500122
2 2 511.7000122 511.7000122 506.1499939 506.5499878
3 4 507.1499939 510.25 507.1499939 510.25
4 5 510 512.3499756 509.2999878 512.3499756
5 3 512.5 512.5 511.1499939 512
6 8 512.25 512.5 510.1000061 510.9500122
7 1 510.5499878 511.7999878 510 511.7999878
8 2 511.1000061 511.8500061 508.1499939 508.8999939
9 5 508.8999939 510 508.5 509.9500122
10 6 509.8999939 509.8999939 508.5 508.8500061
11 8 509.5 511.2000122 509 510.5
12 9 510.5 511.7999878 510.1000061 510.2000122 510.4 510.4
13 12 510.2999878 511.3500061 510.25 510.75 510.25 510.3833344
14 14 510.3500061 512 510.3500061 510.9500122 510.62 510.4138898
15 15 510.9500122 511.7999878 510.6000061 511.1000061 510.69 510.4585667
16 17 511.0499878 511.3500061 509.1000061 509.1000061 510.42 510.51202
17 13 509.5 509.5 508.1000061 508.5 510.13 510.3943521
18 18 508.4500122 508.9500122 507 507 509.8 510.2364895
19 19 507 508.2000122 503.2999878 503.2999878 509.09 509.966782
20 11 504 505 503.5 504.6499939 508.74 509.4112158
21 17 505.4500122 506.3500061 504 504.7000122 508.3 509.0144473
22 18 504.7000122 505.5 504.2000122 505.5 508.02 508.6549111
23 19 505.3500061 505.7000122 503.1000061 503.6499939 507.45 508.3920018
推荐答案
我不知道您所说的SC_12"中的静态值是什么意思 但是如果您在表然后使用这个:
I don't know what you mean by a static value which is in "SC_12" But if you have that value in the table then use this:
select T_P, O_P, H_P, L_P, C_P, SC_12
case when t1.S_NO_ > 12
then cast ((t2.SC_12 * 11 + t1.C_P)/12 as varchar(50))
else ''
end as [c12-WWR]
from t as t1
inner join t as t2 on t2.S_NO_ = t1.S_NO_ -1
order by t1.S_NO_
这篇关于SQL Query Help after 13th row Dynamic and in 12th row static values with a formula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
