将一列中的value实例求和到不同的列中

Question

我有一个表session，其中有一个appointmentType列(nvarchar)。aptType可以是三个值之一(hour, halfhour, pair)。

我需要的是客户名，小时数，半小时数，配对数。

所以数据可能看起来像这样

bob | Hour
bob | Hour
bob | halfhour
bob | halfhour
bob | halfhour
bob | Pair

我想要的是

bob | 2 | 3 | 1

我尝试了这个主题的变体

select c.firstname,
count(shour.clientid),
count(shalfhour.clientid),
count(sHour.clientid)
From Client as c 
                left  outer join [session] as sHour on c.Entityid = shour.ClientId
                left  outer join [session] as sHalfHour on c.Entityid = sHalfHour.ClientId
                left outer join [session] as sPair on c.Entityid = sPair.ClientId 
                where c.entityid =1 and  (shour.appointmentType = 'Hour' or sHalfHour.appointmentType = 'HalfHour') 
                group by c.firstname

客户端1的数据是他有35个小时的apttypes，其余的为0。

当我执行上述操作时，得到

bob | 1135 | 1135 | 1135

如果我把where改为or，我得到0行。

有办法做我想做的事吗?

Answer 1

这可以使用单个连接完成，并使用带有聚合函数的CASE语句来透视数据:

select c.firstname,
    SUM(case when s.appointmentType = 'Hour' then 1 else 0 end) Hour,
    SUM(case when s.appointmentType = 'HalfHour' then 1 else 0 end) HalfHour,
    SUM(case when s.appointmentType = 'Pair' then 1 else 0 end) Pair
From Client as c 
left outer join [session] as s 
    on c.Entityid = s.ClientId
where c.entityid =1
group by c.firstname;

参见SQL Fiddle with Demo

你没有指定什么RDBMS，但如果你使用的数据库有一个PIVOT函数(Oracle 11g+， SQL Server 2005+)，那么你的查询看起来像这样:

select firstname, Hour, HalfHour, Pair
from
(
  select c.firstname, s.appointmentType
  from Client as c 
  left outer join [session] as s 
      on c.Entityid = s.ClientId
  where c.entityid =1
) src
pivot
(
  count(appointmentType)
  for appointmentType in (Hour, HalfHour, Pair)
) piv

参见SQL Fiddle with Demo

两个查询的结果是:

| FIRSTNAME | HOUR | HALFHOUR | PAIR |
--------------------------------------
|       Bob |    2 |        3 |    1 |

Answer 2

您只能计算组是由什么定义的，因此返回计数的最佳方法是作为单独的行，而不是全部在一行中。换句话说，就是:

bob | Hour | 2
bob | halfhour | 3
bob | Pair | 1

而不是:

bob | 2 | 3 | 1

那么这个查询就像:

SELECT 
  c.firstname,
  c.Entityid,
  count(c.clientid) as ct
FROM Client as c  
GROUP BY c.firstname, c.Entityid

一旦你把它们作为单独的行，你可以"透视"这个表，如果你真的需要的话，把它们合并成一行。如果您有灵活性，也可以在应用程序级别执行此操作。下面这些行应该可以完成，但没有实际测试，所以希望它接近:

SELECT
   t.firstname,
   SUM(CASE(t.Entityid WHEN 'hour' THEN t.ct ELSE 0)) as hour,
   SUM(CASE(t.Entityid WHEN 'halfhour' THEN t.ct ELSE 0)) as halfhour,
   SUM(CASE(t.Entityid WHEN 'Pair' THEN t.ct ELSE 0)) as Pair
FROM (
    SELECT 
      c.firstname,
      c.Entityid,
      count(c.clientid) as ct
    FROM Client as c  
    GROUP BY c.firstname, c.Entityid
) t
GROUP BY t.firstname