mysql - 统计单行中 'NA'的个数-MySQL

Question

如果有一行在两个不同的列中有大于或等于 2 个 NA，那么我想选择该行并显示它的所有列。有 SQL 可以做到这一点吗？

原始数据:

 Overall_Score_ID   Company_ID  Operating_Margin_Score  Negative_Earning_Surprise_Score Acquisition_Score   TSR_Score   Enterprise_Score
 54                 399473107   0.0                     2.3770491803278686              5.666666666666666   NA          1.1475409836065573
 116                92847A200   0.3305785123966942      3.0327868852459017              8.75                NA          2.0491803278688527
 337                57164Y107   2.0689655172413794      8.85057471264368                7.558139534883721   NA          3.2558139534883725
 415                007767106   NA                      9.52                            NA                  NA          NA
 462                36112J107   NA                      1.28                            NA                  NA          NA

输出:

Overall_Score_ID    Company_ID  Operating_Margin_Score  Negative_Earning_Surprise_Score Acquisition_Score   TSR_Score   Enterprise_Score
415                 007767106   NA                      9.52                            NA                  NA          NA
462                 36112J107   NA                      1.28                            NA                  NA          NA

现在我只有一个查询来计算列数并显示 >= 2 的列，我知道这是完全错误的，但我认为我可以将其修改为我想要的查询？

select * from overall_scores
group by overall_Score_id
having count(*) >=2;

Answer 1

这可能对你有用:

select *, 
((Operating_Margin_Score='NA') +
(Negative_Earning_Surprise_Score='NA') +
(Acquisition_Score='NA') +
(TSR_Score='NA') +
(Enterprise_Score='NA')) as total
from overall_scores having total >=2;