SQL比较值并替换为数据库值

Question

我有以下数据库查询

USE Runtime

declare @day date = getdate()
declare @today datetime = convert(datetime,concat(convert(varchar(max),@day),' 07:00'))
declare @yest datetime = dateadd(DD,-1,@today)

select datetime,tagname,value 
from AnalogHistory
where tagname like 'MPS_VSD%_SPEED.PVAI' and (tagname ='MPS_FI794071.PVAI' or tagname = 'MPS_FI794051.PVAI')
and datetime between @yest and @today
and wwResolution = '300000'

它返回以下数据

2018-01-16 07:00:00.0000000 MPS_VSD1_SPEED.PVAI 61.5437278747559
2018-01-16 07:00:00.0000000 MPS_VSD1_TRQ_FROM_SPEED.PVAI    750.451904296875
2018-01-16 07:00:00.0000000 MPS_VSD2_SPEED.PVAI 63.0761566162109
2018-01-16 07:00:00.0000000 MPS_VSD2_TRQ_FROM_SPEED.PVAI    742.239990234375
2018-01-16 07:00:00.0000000 MPS_FI794071.PVAI   0.0443801879882813
2018-01-16 07:00:00.0000000 MPS_FI794051.PVAI   0.0189132690429688
2018-01-16 07:05:00.0000000 MPS_VSD1_SPEED.PVAI 61.6443176269531
2018-01-16 07:05:00.0000000 MPS_VSD1_TRQ_FROM_SPEED.PVAI    749.930480957031
2018-01-16 07:05:00.0000000 MPS_VSD2_SPEED.PVAI 62.8407287597656
2018-01-16 07:05:00.0000000 MPS_VSD2_TRQ_FROM_SPEED.PVAI    742.960021972656
2018-01-16 07:05:00.0000000 MPS_FI794071.PVAI   0.0407485961914063
2018-01-16 07:05:00.0000000 MPS_FI794051.PVAI   0.0189132690429688
2018-01-16 07:10:00.0000000 MPS_VSD1_SPEED.PVAI 61.4452743530273
2018-01-16 07:10:00.0000000 MPS_VSD1_TRQ_FROM_SPEED.PVAI    749.669799804688
2018-01-16 07:10:00.0000000 MPS_VSD2_SPEED.PVAI 62.7358551025391
2018-01-16 07:10:00.0000000 MPS_VSD2_TRQ_FROM_SPEED.PVAI    742.239990234375
2018-01-16 07:10:00.0000000 MPS_FI794071.PVAI   0.0443801879882813
2018-01-16 07:10:00.0000000 MPS_FI794051.PVAI   0.0189132690429688
2018-01-16 07:15:00.0000000 MPS_VSD1_SPEED.PVAI 61.5351638793945
2018-01-16 07:15:00.0000000 MPS_VSD1_TRQ_FROM_SPEED.PVAI    750.451904296875

我希望的是，对于相同的日期，如果MPS_FI794071 > 10，则使用MPS_VSD1_SPEED.PVAI，否则，将value设置为0。(本质上是比较同一列中的两个值)

做这件事最好的方法是什么？

我在考虑内循环，但肯定有一种使用case的更简单的方法。

谢谢

Answer 1

解决方案已解决。

由于value在同一列中，因此我将数据旋转到不同的列中。

然后使用case创建由另一列或固定值设置的新列

USE Runtime

declare @day date = getdate()
declare @today datetime = convert(datetime,concat(convert(varchar(max),@day),' 07:00'))
declare @yest datetime = dateadd(DD,-1,@today)

select *,
case when [MPS_FI794051.PVAI] < 5 then [MPS_VSD1_SPEED.PVAI] else 0 end as VSD133,
case when [MPS_FI794071.PVAI] < 5 then [MPS_VSD2_SPEED.PVAI] else 0 end as VSD232
from
(
select datetime,tagname,value 
from AnalogHistory
where tagname like 'MPS_VSD%_SPEED.PVAI' and (tagname ='MPS_FI794071.PVAI' or tagname = 'MPS_FI794051.PVAI')
and datetime between @yest and @today
and wwResolution = '300000'
) as le
PIVOT
(
sum(value)
for tagname in ([MPS_VSD1_SPEED.PVAI],[MPS_VSD2_SPEED.PVAI],[MPS_FI794071.PVAI],[MPS_FI794051.PVAI])
) as pvt




2018-01-16 07:00:00.0000000 61.5437278747559    63.0761566162109    0.0443801879882813  0.0189132690429688  61.5437278747559    63.0761566162109
2018-01-16 07:05:00.0000000 61.6443176269531    62.8407287597656    0.0407485961914063  0.0189132690429688  61.6443176269531    62.8407287597656
2018-01-16 07:10:00.0000000 61.4452743530273    62.7358551025391    0.0443801879882813  0.0189132690429688  61.4452743530273    62.7358551025391
2018-01-16 07:15:00.0000000 61.5351638793945    62.7722396850586    0.0443801879882813  0.0189132690429688  61.5351638793945    62.7722396850586
2018-01-16 07:20:00.0000000 61.4859390258789    62.7936401367188    0.0443801879882813  0.0189132690429688  61.4859390258789    62.7936401367188
2018-01-16 07:25:00.0000000 61.4003295898438    63.024787902832 0.0443801879882813  0.0189132690429688  61.4003295898438    63.024787902832