我正在尝试创建一个查询以获得如下图所示的结果。
每人一行。
问题是,当我创建查询时,我只将它们用于 WHEN CLAUSE 的字段(我什至不需要将它们显示为结果),它显示为错误,要求将它们添加到 GROUP条款或聚合函数。这可能是在我的结果中为每个用户创建重复的行。
如果我将 UserLogon、JobPageFormat 和 JobIsDuplex 放在 group 子句中,我的查询就会运行。
我使用的数据库是 MS SQL Server 2008
请看我的sql查询
select UserLogon, count(TrackingId) as Jobs,
case
when JobPageFormat = 'A3' then SUM(TrackingPageCount*2)
when JobPageFormat != 'A3' then SUM(TrackingPageCount)
end as Total_Pages,
case
when JobPageFormat = 'A3' then SUM(TrackingColorPageCount*2)
when JobPageFormat != 'A3' then SUM(TrackingColorPageCount)
end as Color_Pages,
case
when JobPageFormat = 'A3' then (SUM(TrackingPageCount*2) - SUM(TrackingColorPageCount*2))
when JobPageFormat != 'A3' then (SUM(TrackingPageCount) - SUM(TrackingColorPageCount))
end as Mono_Pages,
case
when JobPageFormat = 'A3' and JobIsDuplex = 0 then SUM(TrackingPageCount*2)
when JobPageFormat != 'A3' and JobIsDuplex = 0 then SUM(TrackingPageCount)
end as Simplex,
case
when JobPageFormat = 'A3' and JobIsDuplex = 1 then SUM(TrackingPageCount*2)
when JobPageFormat != 'A3' and JobIsDuplex = 1 then SUM(TrackingPageCount)
end as Duplex
from scTracking
where ComputerName like '%ServerXXX%'
and JobType in (1,2,3)
and StartDateTime > '2019-09-01'
and StartDateTime < '2019-10-01'
group by UserLogon, JobPageFormat, JobIsDuplex
order by UserLogon
查看我在下面得到的结果
有谁知道如何在不复制结果的情况下执行此查询?
亲切的问候,卢西亚诺
您应该将总和更改为在 case 表达式之外:
SUM(case
when JobPageFormat = 'A3' then TrackingPageCount
ELSE TrackingPageCount
end) as Total_Pages,
SUM(CASE WHEN JobIsDuplex = 0
THEN
CASE when JobPageFormat = 'A3' then TrackingPageCount*2
ELSE TrackingPageCount
end) as Simplex,
etc.
然后你可以取出JobPageFormat
和JobIsDuplex
取出 group by 子句。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句