请大神帮忙修改一下SQL语句

如下语句取fourteen_status这个字段中带有付常减压(Ⅰ)的数值，比如说我想把语句修改为8:00取fourteen_status这个字段，16:00取另一个字段值怎么修改？20:00取得字段值名为lsix_status，其他条件不变。

select productname,sum(value) as valuex,case when fourteen_status like N'%付常减压(Ⅰ)%' then N'一期原油比例' when fourteen_status like N'%付常减压(II)%' then N'二期原油比例' end as typex from

(

select * from

(select

substring(a.TagName,6,3)+'_'+substring(a.TagName,9,1)+'_'+substring(a.TagName,10,2) as newTagName

a.TagName,a.TimeStamp,a.Value-b.Value as Value,a.Confidence,a.HostName,a.Units,a.FormatedTime

from

(select * from dbo.Fun_CO_GetPHDValue('TANK.211T01.MAS;TANK.211T02.MAS;TANK.211T03.MAS;TANK.211T04.MAS;TANK.212T01.MAS;TANK.212T02.MAS;TANK.212T03.MAS;TANK.212T04.MAS;TANK.240T03.MAS;TANK.240T04.MAS;TANK.600T08.MAS;TANK.600T09.MAS;TANK.240T01.MAS;TANK.240T02.MAS;TANK.240T05.MAS;TANK.240T06.MAS;TANK.241T01.MAS;TANK.241T02.MAS;TANK.241T03.MAS;TANK.241T04.MAS;TANK.241T05.MAS;TANK.241T06.MAS',dateadd(mi,-10,getdate())))a,

where a.TagName=b.TagName

)x1

left join

(select mesreport.dbo.t_Oilcan_Data.productname,mesreport.dbo.t_Oilcan_Config.oilcan_no,fourteen_status from mesreport.dbo.t_Oilcan_Data

left join mesreport.dbo.t_Oilcan_Config

on mesreport.dbo.t_Oilcan_Data.oilcanid= mesreport.dbo.t_Oilcan_Config.oilcanid

where oilcan_date=(select max(oilcan_date) from mesreport.dbo.t_Oilcan_Data )

)x2

on x1.newTagName=x2.oilcan_no

where value<0 and (fourteen_status like (N'%付常减压(Ⅰ)%') or fourteen_status like (N'%付常减压(II)%')) and oilcan_no in('211_T_01','211_T_02','211_T_03','211_T_04','212_T_01','212_T_02','212_T_03','212_T_04','240_T_01','240_T_02','240_T_03','240_T_04','240_T_05','240_T_06','241_T_01','241_T_02','241_T_03','241_T_04','241_T_05','241_T_06')

)mm

group by productname,fourteen_status

order by fourteen_status

潘强 发布于 2022-1-11 22:57 （编辑于 2022-1-12 00:36）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：20 F币 7人赏过› + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共5回答

最佳回答

潘强Lv2见习互助
发布于2022-1-23 12:56

select productname,fourteen_status,twtwo_status,lsix_status,sum(value) as valuex,systime,

case when ((systime>='08:30:00'and systime<'16:30:00') and fourteen_status like N'%付常减压(Ⅰ)%') or ((systime>='16:30:00' and systime<'00:30:00') and twtwo_status like N'%付常减压(Ⅰ)%') or ((systime>='00:30:00' or systime<'08:30:00') and lsix_status like N'%付常减压(Ⅰ)%') then N'一期原油比例'

when ((systime>='08:30:00' and systime<'16:30:00')and fourteen_status like N'%付常减压(II)%') or ((systime>='16:30:00' and systime<'00:30:00') and twtwo_status like N'%付常减压(II)%') or ((systime>='00:30:00' or systime<'08:30:00') and lsix_status like N'%付常减压(II)%')

then N'二期原油比例'end as typex

from

(

select x1.*,x2.*,CONVERT(varchar(100), GETDATE(), 8) as systime from

(select

substring(a.TagName,6,3)+'_'+substring(a.TagName,9,1)+'_'+substring(a.TagName,10,2) as newTagName,

a.TagName,a.TimeStamp,a.Value-b.Value as Value,a.Confidence,a.HostName,a.Units,a.FormatedTime

from

where a.TagName=b.TagName

)x1

left join

(select mesreport.dbo.t_Oilcan_Data.productname,mesreport.dbo.t_Oilcan_Config.oilcan_no,fourteen_status,twtwo_status,lsix_status from mesreport.dbo.t_Oilcan_Data

left join mesreport.dbo.t_Oilcan_Config

on mesreport.dbo.t_Oilcan_Data.oilcanid= mesreport.dbo.t_Oilcan_Config.oilcanid

where oilcan_date=(select max(oilcan_date) from mesreport.dbo.t_Oilcan_Data )

)x2

on x1.newTagName=x2.oilcan_no

where value <-10 and (fourteen_status like N'%付常减压(Ⅰ)%' or fourteen_status like N'%付常减压(II)%'or twtwo_status like N'%付常减压(Ⅰ)%' or twtwo_status like N'%付常减压(II)%' or lsix_status like N'%付常减压(Ⅰ)%' or lsix_status like N'%付常减压(II)%') and oilcan_no in('211_T_01','211_T_02','211_T_03','211_T_04','212_T_01','212_T_02','212_T_03','212_T_04','240_T_01','240_T_02','240_T_03','240_T_04','240_T_05','240_T_06','241_T_01','241_T_02','241_T_03','241_T_04','241_T_05','241_T_06')

)mm

group by productname,fourteen_status,twtwo_status,lsix_status,systime

order by fourteen_status,twtwo_status,lsix_status

shirokoLv6资深互助
发布于2022-1-11 23:30（编辑于 2022-1-14 15:37）

这个8点指的是哪个字段八点啊

其他时间呢？只有8点和20点两种吗不会有其他情况吗

假设你是系统时间而且是范围内全都是：前几行改成这样：

select productname,sum(value) as valuex,

case when (systime>='08:00:00' and systime<='16:00:00' and fourteen_status like N'%付常减压(Ⅰ)%') or (systime>='16:00:00' and systime<='24:00:00' and twtwo_status like N'%付常减压(Ⅰ)%') or (systime>='00:00:00' or systime<='08:00:00' and lsix_status like N'%付常减压(Ⅰ)%')

then N'一期原油比例'

when (systime>='08:00:00' and systime<='16:00:00' and fourteen_status like N'%付常减压(II)%') or (systime>='16:00:00' and systime<='24:00:00' and twtwo_status like N'%付常减压(II)%') or (systime>='00:00:00' or systime<='08:00:00' and lsix_status like N'%付常减压(II)%')

then N'二期原油比例'

end as typex

from

(

select x1.*,x2.*,CONVERT(varchar(100), GETDATE(), 8) as systime from

(select

substring(a.TagName,6,3)+'_'+substring(a.TagName,9,1)+'_'+substring(a.TagName,10,2) as newTagName

举报收起评论(9)

潘强(提问者) 我们实际上有3个字段，分别对应00:00,8:00,16:00，就这三种情况，我想在这三个时间区分别对应的字段值

2022-01-12 00:38

shiroko 回复潘强(提问者) 哪个字段是代表时间的呀，系统时间？

2022-01-12 08:44

潘强(提问者) 0：00，8：00，16：00这三种情况，当系统时间大于0：00小于8：00取lsix_status，当系统时间大于8：00小于16：00取fourteen_status，当系统时间大于16：00小于00：00取twtwo_status

2022-01-14 15:32

潘强(提问者) 请帮忙按我上面说的再修改一下感觉就差不多了，非常感谢！

2022-01-14 15:33

shiroko 回复潘强(提问者) 改了

2022-01-14 15:37

还有4条评论点击查看

墨残烛Lv5初级互助
发布于2022-1-12 08:33

能否将需要部分的代码拿出来？这全部一股脑放上去也不好查看

冷雨蝶舞Lv4见习互助
发布于2022-1-12 08:46（编辑于 2022-1-12 08:55）

select * from 表名

WHERE 1=1

AND CASE WHEN

convert(datetime,convert(char(8),getdate(),108)) BETWEEN CONVERT(datetime,'1900-01-01 08:00:00') and CONVERT(datetime,'1900-01-01 16:00:00')

THEN 字段名

else ''

END ='这里写值'

参照一下这个吧

多条件就是这样

SELECT * FROM 表名

WHERE 1=1

AND CASE WHEN

条件

THEN

字段名

WHEN

条件

THEN

字段名

END = 值参士大夫

CD20160914Lv8专家互助
发布于2022-1-12 08:56

猜测一下。。难道要这样判断就是和当前系统时间判断？假如是8点整就用fourteen_status字段？假如是20点整就用

lsix_status,否则就用其他字段？？不知道是不是这个意思。。。

case when ${if(format(now(),"HH:mm:ss")="08:00:00","fourteen_status",if(format(now(),"HH:mm:ss")="20:00:00","lsix_status","你要的其他字段"))}

like N'%付常减压(Ⅰ)%'

then N'一期原油比例'

when fourteen_status like N'%付常减压(II)%'

then N'二期原油比例' end as typex from

举报收起评论(3)

潘强(提问者) 前面改成下面这样 select productname,sum(value) as valuex, case when (systime>=\'08:00:00\' and systime<=\'16:00:00\' and fourteen_status like N\'%付常减压(Ⅰ)%\') or (systime>=\'16:00:00\' and systime<=\'24:00:00\' and twtwo_status like N\'%付常减压(Ⅰ)%\') or (systime>=\'00:00:00\' or systime<=\'08:00:00\' and lsix_status like N\'%付常减压(Ⅰ)%\') then N\'一期原油比例\' when (systime>=\'08:00:00\' and systime<=\'16:00:00\' and fourteen_status like N\'%付常减压(II)%\') or (systime>=\'16:00:00\' and systime<=\'24:00:00\' and twtwo_status like N\'%付常减压(II)%\') or (systime>=\'00:00:00\' or systime<=\'08:00:00\' and lsix_status like N\'%付常减压(II)%\') then N\'二期原油比例\' end as typex from ( select x1.*,x2.*,CONVERT(varchar(100), GETDATE(), 8) as systime from (select substring(a.TagName,6,3)+\'_\'+substring(a.TagName,9,1)+\'_\'+substring(a.TagName,10,2) as newTagName

2022-01-16 20:32

潘强(提问者) 下面这部分不知道怎么改 (select mesreport.dbo.t_Oilcan_Data.productname,mesreport.dbo.t_Oilcan_Config.oilcan_no,fourteen_status from mesreport.dbo.t_Oilcan_Data left join mesreport.dbo.t_Oilcan_Config on mesreport.dbo.t_Oilcan_Data.oilcanid= mesreport.dbo.t_Oilcan_Config.oilcanid where oilcan_date=(select max(oilcan_date) from mesreport.dbo.t_Oilcan_Data ) )x2 on x1.newTagName=x2.oilcan_no where value<0 and (fourteen_status like (N\'%付常减压(Ⅰ)%\') or fourteen_status like (N\'%付常减压(II)%\')) and oilcan_no in(\'211_T_01\',\'211_T_02\',\'211_T_03\',\'211_T_04\',\'212_T_01\',\'212_T_02\',\'212_T_03\',\'212_T_04\',\'240_T_01\',\'240_T_02\',\'240_T_03\',\'240_T_04\',\'240_T_05\',\'240_T_06\',\'241_T_01\',\'241_T_02\',\'241_T_03\',\'241_T_04\',\'241_T_05\',\'241_T_06\') )mm group by productname,fourteen_status order by fourteen_status

2022-01-16 20:33