从不同的列加入同一个表上的多个计数？

I feel like this should be easy but it's late and I'm struggling.

我觉得这应该很容易，但已经很晚了，我正在努力。

Say (in an oracle 12 db) I have a table which represents which staff filled what roles in a bar, during different events, like this:

说（在oracle 12 db中）我有一个表，表示哪些员工在不同的事件中填充了酒吧中的角色，如下所示：

+----------+----------+-------+------------+----------+
| event_id | bar      | doors | cloak_room | keg_room |
+----------+----------+-------+------------+----------+
| 2        | bob      | bill  | john       | mary     |
+----------+----------+-------+------------+----------+
| 3        | bob      | bill  | mary       | kev      |
+----------+----------+-------+------------+----------+
| 4        | bob      | john  | louise     | mary     |
+----------+----------+-------+------------+----------+
| 5        | kyle     | kev   | sarah      | louise   |
+----------+----------+-------+------------+----------+
| 6        | jennifer | bob   | jay        | john     |
+----------+----------+-------+------------+----------+
| 7        | john     | bill  | mary       | steve    |
+----------+----------+-------+------------+----------+

and I want to get a count of, overall, how many events each staff member worked, like this:

我希望得到每个工作人员工作的事件总数，如下所示：

+-------+--------+
| count | person |
+-------+--------+
| 4     | bob    |
+-------+--------+
| 4     | john   |
+-------+--------+
| 3     | bill   |
+-------+--------+
| 3     | mary   |
+-------+--------+
| 2     | kev    |
+-------+--------+
| 2     | louise |
+-------+--------+
| 1     | jay    |
+-------+--------+
| 1     | steve  |
+-------+--------+

We see here that bob has a count of 4 - because he is associated with 4 distinct event_id: 3 as a barman, and 1 as a doorman.

我们在这里看到bob的计数为4 - 因为他与4个不同的event_id相关联：3作为酒保，1作为门卫。

(assuming no two staff members have the same name, and no one can work two jobs at once)

（假设没有两个工作人员有相同的名字，没有人可以同时工作两个工作）

How do I do this?

我该怎么做呢？

for one 'role' it's clear:

对于一个'角色'，很清楚：

select count(event_id), bar group by bar

but is there an elegant way to do this for all columns - without full joins and string concat?

但是有没有一种优雅的方法来为所有列执行此操作 - 没有完整的连接和字符串连接？

thanks!

谢谢！

2 个解决方案

#1

You may count by string columns in the nested inner query and then sum them up outside with your desired order :

您可以通过嵌套内部查询中的字符串列进行计数，然后使用所需顺序将它们汇总到外部：

SELECT sum(count) count, person
  FROM
(
 SELECT count(event_id) count, bar person FROM mytable GROUP BY bar UNION ALL
 --> P.S. Only aliasing as "person" is enough in this upper "select" for all 
 --> four "select" statements inside the parentheses.
 SELECT count(event_id)      , doors      FROM mytable GROUP BY doors UNION ALL
 SELECT count(event_id)      , cloak_room FROM mytable GROUP BY cloak_room UNION ALL
 SELECT count(event_id)      , keg_room   FROM mytable GROUP BY keg_room
)
GROUP BY person
ORDER BY 1 desc, 2;

COUNT   PERSON
   4    bob
   4    john
   3    bill
   3    mary
   2    kev
   2    louise
   1    jay
   1    jennifer
   1    kyle
   1    mary2
   1    sarah
   1    steve

SQL Fiddle Demo

SQL小提琴演示

#2

You should change the structure of your data, so you have one row per event/person/role. Then you could just use aggregation.

您应该更改数据的结构，因此每个事件/人/角色都有一行。然后你可以使用聚合。

You can do that in a query as well:

您也可以在查询中执行此操作：

select who, count(*)
from (select event_id, 'bar' as job, bar as who from t union all
      select event_id, 'doors' as job, doors as who from t union all
      select event_id, 'cloak_room' as job, cloak_room as who from t union all
      select event_id, 'keg_room' as job, keg_room as who from t
     ) jw
group by who;

If someone could have multiple jobs in one event, then use count(distinct event_id).

如果某人在一个事件中可以有多个作业，则使用count（distinct event_id）。

EDIT:

编辑：

I see you are using Oracle 12c. Then use a lateral join/cross apply:

我看到你正在使用Oracle 12c。然后使用横向连接/交叉应用：

select who, count(*)
from t cross apply
     (select t.event_id, 'bar' as job, t.bar as who from dual union all
      select t.event_id, 'doors' as job, t.doors as who from dual from dual union all
      select event_id, 'cloak_room' as job, cloak_room as who from dual union all
      select t.event_id, 'keg_room' as job, t.keg_room as who from dual
     ) jw
group by who;

#1