SQL Server 带求和的查询(Case When)

vu8f3i0k 于 2023-02-15 发布在其他

关注(0)|答案(1)|浏览(254)

我在使用Sum（Case When）时遇到问题。当我使用以下查询时：

SELECT CONVERT(VARCHAR(10), [datetime], 111) as [Date],
       Installation_Id,
       Installation_Name,
              Round (ISNULL (sum (case when ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00') and (Installation_ID = '18374' or Installation_ID = '18466' or Installation_ID = '18375' or Installation_ID = '18372' )) then availability/240 
                                when ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00') and (Installation_ID = '18373')) then availability/216 
                                when ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00') and (Installation_ID = '18467' )) then availability/48 end),0),4) as [Availability from 7am to 9am or 4pm to 8pm]
from Reserve_Power
where Installation_Id = 18467
Group by CONVERT(VARCHAR(10), [datetime], 111),Installation_Id,Installation_Name

我得到的结果是：

可用性= 0.7736

但是当我运行下面的查询时：

SELECT CONVERT(VARCHAR(10), [datetime], 111) as [Date],
       Installation_Id,
       Installation_Name,
       sum (case when ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00') and (Installation_ID = '18467' )) then availability/48 end) as [Availability from 7am to 9am or 4pm to 8pm]

from Reserve_Power
where Installation_Id = 18467 and CONVERT(VARCHAR(10), [datetime], 111) = '2023-01-22'
Group by CONVERT(VARCHAR(10), [datetime], 111),Installation_Id,Installation_Name;

我得到的结果是：

可用性= 1

第二个查询的结果是正确的，所以我假设与Sum（）函数相关的部分有问题。
你能帮我解决这个问题吗？谢谢！

sql-server

来源：https://stackoverflow.com/questions/75448060/query-with-sum-case-when

1条答案

按热度按时间

ozxc1zmp1#

我解决了这个问题，我只是解释说以后可能有人会遇到类似的情况，问题是在OR子句中，缺少括号，当我在OR子句中使用括号时，问题就解决了。

SELECT CONVERT(date, [datetime]) as [Date],
       Installation_Id,
       Installation_Name,
              Round (ISNULL (sum (case when (cast([datetime] as time) >= '06:00:00' and cast([datetime] as time) < '22:00:00' and (Installation_ID = '18374' or Installation_ID = '18466' or Installation_ID = '18375' or Installation_ID = '18372' )) then availability/640 
                                when (cast([datetime] as time) >= '06:00:00' and cast([datetime] as time) < '22:00:00' and (Installation_ID = '18373')) then availability/576 
                                when (cast([datetime] as time) >= '06:00:00' and cast([datetime] as time) < '22:00:00' and (Installation_ID = '18467')) then availability/128 end),0),4) as [Availability in critical hours 6am to 10pm],
       Round (ISNULL (sum (case when (((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) and (Installation_ID = '18374' or Installation_ID = '18466' or Installation_ID = '18375' or Installation_ID = '18372' )) then availability/240 
                                when (((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) and (Installation_ID = '18373')) then availability/216 
                                when (((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) and (Installation_ID = '18467' )) then availability/48 end),0),4) as [Availability in critical hours 7am to 9am or 4pm to 8pm]
from Reserve_Power
where Installation_Id = 18373
Group by CONVERT(date, [datetime]),Installation_Id,Installation_Name

赞(0）回复(0）举报 2023-02-15

我来回答

SQL Server 带求和的查询(Case When)

1条答案

相关问题

热门标签

最新问答