我的桌子是这样的：
0 Number Certification
1 400009 CSR
2 400009 MD
3 400009 MHP
4 400032 CSP
5 400032 MH
在我使用get dummies之后，那么桌子是这样的：
Number Certification_ACD ... Certification_TSC Certification_TSE
1 400009 0 ... 0 0
2 400009 0 ... 0 0
3 400009 0 ... 0 0
我希望数字列只显示一次。有人能帮忙吗？谢谢！
以下查询可以满足您的需要：
SELECT
Number,
SUM(Certification='ACD') AS Certification_ACD,
SUM(Certification='TSC') AS Certification_TSC,
SUM(Certification='TSE') AS Certification_TSE
FROM
YourTable
GROUP BY
Number
ORDER BY
Number;
如果知道它们的数量，您可以执行以下操作：
select
number,
sum(case when certification = 'ACD' then 1 else 0 end) as cert_asd,
sum(case when certification = 'TSC' then 1 else 0 end) as cert_tsc,
sum(case when certification = 'TSE' then 1 else 0 end) as cert_tse,
-- other cases here
from t
group by number
sum()函数可添加认证。如果一个人多次拥有相同的认证，它将显示2，或3等。
