解决isnull,case when 不能将null值转换为0或其他值的状况

SQL SERVER

1、begin end用法
2、解决isnull,case when 不能将null值转换为0或其他值的状况，本文使用right join 解决，因为查询的表中本来就没有符合where的值，所以不能将null值转换为0或其他值，比如如下情况。

select * from [dbo].[Table_1] where ids=‘2’ and stime=‘2019-01-07 00:00:00.000’
这样查出来，是没有值的。。。。
这种在sql studio里面是空白的，不显示null值，

但是在Navicat等软件里面会显示NULL

所以，如果不是在sql studio进行查询的，一定要在sql studio里面执行语句，看看是否和其他的软件显示一致
这次遇到的问题就是在navicat里面一致显示null,所以不断的用isnull,case when 将这个null值转换为0，但是都没有成功，因为表中就符合where条件的值，所以isnull,case when不能做转换。
3、行列转换的麻烦用法。

最终换成这个语句的原因一个是由于2中的原因，另一个是用select对多个with进行查询时，其中一个with中出现null值，其他有值的with都会变成null。例如with查询的两个值都是null,那么用以下语句查询时

select * from with1,with2,with3,with4,with5,with6,with7,with8,with9,with10

会出现查询出的值全部是null的情况，这种可能就是由于2中的原因(表中没有null值)引起的

最终SQL中建立临时表的原因是:如果使用以下语句

with with0 as (
select device_number2 FROM [dbo].[sctj] where device_number2 in ('1','2','3','4','5','6','7','8','9','10')GROUP BY device_number2),
zuozhe as (
select isnull(worktime,0) as worktime,isnull(powerontime,0) as powerontime,b.device_number2 from [xcmg_sctj] a right join with0 b on a.device_number2=b.device_number2 and DATEDIFF(day, a.[time], GETDATE()) =1),

with1 as (
SELECT round(worktime/3600,2) as [1运行时长],round(powerontime/3600,2) as [1开机时长]  FROM zuozhe where device_number2='1'),
with2 as (
SELECT round(worktime/3600,2) as [2运行时长],round(powerontime/3600,2) as [2开机时长]  FROM zuozhe where device_number2='2'),
with3 as (
SELECT round(worktime/3600,2) as [3运行时长],round(powerontime/3600,2) as [3开机时长]  FROM zuozhe where device_number2='3'),
with4 as (
SELECT round(worktime/3600,2) as [4运行时长],round(powerontime/3600,2) as [4开机时长]  FROM zuozhe where device_number2='4'),
with5 as (
SELECT round(worktime/3600,2) as [5运行时长],round(powerontime/3600,2) as [5开机时长]  FROM zuozhe where device_number2='5'),
with6 as (
SELECT round(worktime/3600,2) as [6运行时长],round(powerontime/3600,2) as [6开机时长]  FROM zuozhe where device_number2='6'),
with7 as (
SELECT round(worktime/3600,2) as [7运行时长],round(powerontime/3600,2) as [7开机时长]  FROM zuozhe where device_number2='7'),
with8 as (
SELECT round(worktime/3600,2) as [8运行时长],round(powerontime/3600,2) as [8开机时长]  FROM zuozhe where device_number2='8'),
with9 as (
SELECT round(worktime/3600,2) as [9运行时长],round(powerontime/3600,2) as [9开机时长]  FROM zuozhe where device_number2='9'),
with10 as (
SELECT round(worktime/3600,2) as [10运行时长],round(powerontime/3600,2) as [10开机时长]  FROM zuozhe where device_number2='10')

select * from with1,with2,with3,with4,with5,with6,with7,with8,with9,with10

不知道为什么查询非常非常的慢，每个单独的with，查询的特别快，随便查询其中随便五个with时，查询也是比较快的，1秒之内肯定能查出来，但是查询的with数量一旦超过五个，查询时间就变成了130秒往上，10个with一起查询时，时间太长，我没有等到结果。所以将join on 的结果存成了一个临时表，最终再删除。

过程确实过于繁琐，我也在找优化的方法。。。。。。。。。哪位大神路过此处帮忙给指条明路呗。

最终SQL

 BEGIN

with with0 as (
select device_number2 FROM [dbo].[sctj] where device_number2 in ('1','2','3','4','5','6','7','8','9','10')GROUP BY device_number2)

select isnull(worktime,0) as worktime,isnull(powerontime,0) as powerontime,b.device_number2 into #zuozhe from [xcmg_sctj] a right join with0 b on a.device_number2=b.device_number2 and DATEDIFF(day, a.[time], GETDATE()) =1;
---建立临时表
with
with1 as (
SELECT round(worktime/3600,2) as [1运行时长],round(powerontime/3600,2) as [1开机时长]  FROM #zuozhe where device_number2='1'),
with2 as (
SELECT round(worktime/3600,2) as [2运行时长],round(powerontime/3600,2) as [2开机时长]  FROM #zuozhe where device_number2='2'),
with3 as (
SELECT round(worktime/3600,2) as [3运行时长],round(powerontime/3600,2) as [3开机时长]  FROM #zuozhe where device_number2='3'),
with4 as (
SELECT round(worktime/3600,2) as [4运行时长],round(powerontime/3600,2) as [4开机时长]  FROM #zuozhe where device_number2='4'),
with5 as (
SELECT round(worktime/3600,2) as [5运行时长],round(powerontime/3600,2) as [5开机时长]  FROM #zuozhe where device_number2='5'),
with6 as (
SELECT round(worktime/3600,2) as [6运行时长],round(powerontime/3600,2) as [6开机时长]  FROM #zuozhe where device_number2='6'),
with7 as (
SELECT round(worktime/3600,2) as [7运行时长],round(powerontime/3600,2) as [7开机时长]  FROM #zuozhe where device_number2='7'),
with8 as (
SELECT round(worktime/3600,2) as [8运行时长],round(powerontime/3600,2) as [8开机时长]  FROM #zuozhe where device_number2='8'),
with9 as (
SELECT round(worktime/3600,2) as [9运行时长],round(powerontime/3600,2) as [9开机时长]  FROM #zuozhe where device_number2='9'),
with10 as (
SELECT round(worktime/3600,2) as [10运行时长],round(powerontime/3600,2) as [10开机时长]  FROM #zuozhe where device_number2='10')

select * from with1,with2,with3,with4,with5,with6,with7,with8,with9,with10;
---在临时表中查询数据

drop table #zuozhe
---删除临时表
end