GROUP BY子句错误

Question

我想从GROUP BY子句中排除一个术语，该术语阻止我将术语汇总在一起。奇怪的是，我在case语句中使用了导致这种情况的术语，而查询仍然希望我在group by子句中使用它，否则将生成错误。如何解决这个问题？

更具体地说:在SELECT子句的用例中使用的vr.krebel迫使我将其添加到GROUP BY中。如何避免这种情况？

SELECT vr.ktonr,
       vr.foretagkod,
       '' AS persign,
       '' AS TYPE,
       NULL,
       1 AS antal,
       SUM(vr.debbel-vr.krebel),
       0,
       vr.kostbar,
       vr.koststallekod,
       vr.projcode,
       vr.redovisnar,
       vr.period,
       'Allocated vouchers' AS artbeskr,
       ko.ktobeskr AS kodescr,
       CASE
           WHEN ((vr.ktonr LIKE '9600'
                  OR vr.ktonr LIKE '3%')
                 AND (vr.krebel>0)) THEN 'Revenues'
           ELSE 'Costs'
       END
FROM vr
INNER JOIN ko ON ko.ktonr=vr.ktonr
AND ko.redovisnar=datepart(YEAR,vr.bokfdat)
AND ko.foretagkod=vr.foretagkod
WHERE vr.foretagkod = 300
  AND vr.intsource = 20
  AND vr.projcode = 50040
  AND (vr.bokfdat BETWEEN CAST('2012-12-01' AS DATETIME) AND CAST('2012-12-31' AS DATETIME))
GROUP BY vr.ktonr,
         vr.foretagkod,
         vr.kostbar,
         vr.koststallekod,
         vr.projcode,
         vr.redovisnar,
         vr.period,
         ko.ktobeskr,
         vr.krebel

更具体地说:在SELECT子句中的用例中使用的vr.krebel迫使我将其添加到GROUP BY中，如何避免这种情况？

Answer 1

这里有不同的选项。但我认为对您的查询更好的方法是在GROUP BY中添加您的CASE。

select  vr.ktonr, 
        vr.foretagkod,
        '' as persign, 
        '' as Type,
        NULL, 
        1 as antal, 
        SUM(vr.debbel-vr.krebel),
        0, 
        vr.kostbar, 
        vr.koststallekod, 
        vr.projcode,
        vr.redovisnar, 
        vr.period,  
        'Allocated vouchers' as artbeskr, 
        ko.ktobeskr as kodescr,
        case when ((vr.ktonr like '9600' or vr.ktonr like '3%')
                     AND (vr.krebel>0)) then 'Revenues' else 'Costs' end
from vr
inner join ko 
    on ko.ktonr=vr.ktonr 
    AND ko.redovisnar=datepart(year,vr.bokfdat) 
    AND ko.foretagkod=vr.foretagkod
where vr.foretagkod = 300 
AND vr.intsource = 20 
AND vr.projcode = 50040 
AND (vr.bokfdat between CAST('2012-12-01' AS DATETIME) 
                AND CAST('2012-12-31' AS DATETIME))
group by vr.ktonr, 
         vr.foretagkod, 
         vr.kostbar, 
         vr.koststallekod, 
         vr.projcode, 
         vr.redovisnar, 
         vr.period, 
         ko.ktobeskr, 
         case when ((vr.ktonr like '9600' or vr.ktonr like '3%')
                     AND (vr.krebel>0)) then 'Revenues' else 'Costs' end