如何使用选择查询在SQL Server中并排添加列

debugcn 发表于 Dev

克里希纳·莫汉（Krishna Mohan）

我想在列中并列显示选择查询输出。当前即时消息显示在多个记录的一列中。

码

select sum(Fan1Hrs) as Fan1Hrs from (select CONVERT(Date, devicetimestamp) as Date, max(Convert(int, Fan1Hrs)) as Fan1Hrs from RawData where MONTH(DeviceTimeStamp) = MONTH(dateadd(dd, -1, GetDate())) AND YEAR(DeviceTimeStamp) = YEAR(dateadd(dd, -1, GetDate())) group by CONVERT(Date, devicetimestamp))comp1
union all
select sum(Fan2Hrs) as Fan2Hrs from (select CONVERT(Date, devicetimestamp) as Date, max(Convert(int, Fan2Hrs)) as Fan2Hrs from RawData where MONTH(DeviceTimeStamp) = MONTH(dateadd(dd, -1, GetDate())) AND YEAR(DeviceTimeStamp) = YEAR(dateadd(dd, -1, GetDate())) group by CONVERT(Date, devicetimestamp))comp2
union all
select sum(Fan3Hrs) as Fan3Hrs from (select CONVERT(Date, devicetimestamp) as Date, max(Convert(int, Fan3Hrs)) as Fan3Hrs from RawData where MONTH(DeviceTimeStamp) = MONTH(dateadd(dd, -1, GetDate())) AND YEAR(DeviceTimeStamp) = YEAR(dateadd(dd, -1, GetDate())) group by CONVERT(Date, devicetimestamp))comp3


Exp Op:
FAN1Hrs  FAN2Hrs  FAN3Hrs
1234     1123     2323

当前OP

史蒂夫·C

我想您正在寻找这样的东西

with fan_cte as (
    select CONVERT(Date, devicetimestamp) as Date, 
             max(Convert(int, Fan1Hrs)) as Fan1Hrs,
             max(Convert(int, Fan2Hrs)) as Fan2Hrs,
             max(Convert(int, Fan3Hrs)) as Fan3Hrs
    from RawData
    where MONTH(DeviceTimeStamp) = MONTH(dateadd(dd, -1, GetDate())) 
          AND YEAR(DeviceTimeStamp) = YEAR(dateadd(dd, -1, GetDate())) 
    group by CONVERT(Date, devicetimestamp))
select sum(Fan1Hrs) as Fan1Hrs, sum(Fan2Hrs) as Fan2Hrs, sum(Fan3Hrs) as Fan3Hrs
from fan_cte;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。