从一行获取用户计数- Oracle SQL

Question

Create table test4(
id number,
user_1 varchar2(100),
user_2 varchar2(100),
user_3 varchar2(100),
user_4 varchar2(100)
)

Insert into test4(id,user_1,user_2,user_3,user_4) values (1,'MARK','CLARC','KING','KING');

如何得到结果

user        total
---------------------
MARC        1
CLARC       1
KING        2

Answer 1

使用UNPIVOT(只需要一次表扫描):

SELECT name,
COUNT(*)
FROM   test4
UNPIVOT (name FOR key IN (user_1, user_2, user_3, user_4))
GROUP BY name

输出:

<表类>名称COUNT (*)tbody><<tr>王2标记1CLARC1

可能是这样的:

select user, count(*) from (
select user_1 as user from test4 union all 
select user_2 as user from test4 union all 
select user_3 as user from test4 union all 
select user_4 as user from test4
) t 
group by user