SQL计数不同列中出现的不同值

Question

我想计算不同的值，并报告它们在SQL中的出现情况。问题是，我需要计算不同列上的出现次数，然后为每列报告每个值的出现次数。所以，假设我从一个名为Result:的表中得到了这样的数据

教练Id><1>

	所有者Id	飞行员Id
A	1	2	3
B	2	2	3
C	3	3

我认为您需要将列取消平移为行，然后计算每个值的出现次数。你可以做：

select v.id,
sum(case when v.col = 'owner' then 1 else 0 end) owner,
sum(case when v.col = 'pilot' then 1 else 0 end) pilot,
sum(case when v.col = 'coach' then 1 else 0 end) coach
from mytable t
cross apply (values (t.ownerid, 'owner'), (t.pilotid, 'pilot'), (t.coachid, 'coach')) as v(id, col)
group by v.id

DB Fiddle上的演示：

id |所有者|飞行员|教练-：|----:|----：|----：1|1|1|02|1|2|03|1|0|3

使用基本的SQL命令也是如此。保证在每个DBMS上工作：

SELECT 
ids.id, 
SUM(CASE WHEN ro.ownerid = ids.id THEN 1 ELSE 0 END) AS Owner,
SUM(CASE WHEN po.pilotid = ids.id THEN 1 ELSE 0 END) AS Pilot,
SUM(CASE WHEN co.coachid = ids.id THEN 1 ELSE 0 END) AS Coach
FROM (
SELECT ownerID AS id FROM result UNION
SELECT pilotID FROM result UNION
SELECT coachID FROM result  
) ids
LEFT JOIN result ro ON ids.id = ro.ownerid
LEFT JOIN result po ON ids.id = po.pilotid
LEFT JOIN result co ON ids.id = co.coachid
GROUP BY ids.id

在SQLFiddler 中