MySQL:填充字段的百分比

Question

我有以下 MySQL 表 user。

Name | Phone | City
---------------------
Mike | 154   | London
NULL | 659   | Paris
Matt | NULL  | NULL
NULL | NULL  | NULL
Marc | 514   | NULL

预期结果:

COUNT of 3/3 not NULL | COUNT of 2/3 not NULL | COUNT of 1/3 not NULL
---------------------------------------------------------------------
1                     | 2                     | 1

我想知道完成他的个人资料的用户数 100%、66%、33%、0%。

什么查询可以给我这个结果？

非常感谢!

Answer 1

您可以为此使用条件聚合:

Select  Sum(Case When TotalNotNull = 3 Then 1 Else 0 End) As `Count of 3/3 not NULL`,
        Sum(Case When TotalNotNull = 2 Then 1 Else 0 End) As `Count of 2/3 not NULL`,
        Sum(Case When TotalNotNull = 1 Then 1 Else 0 End) As `Count of 1/3 not NULL`,
        Sum(Case When TotalNotNull = 0 Then 1 Else 0 End) As `Count of 0/3 not NULL`
From
(
    Select  Case When Name  Is Not Null Then 1 Else 0 End 
        +   Case When Phone Is Not Null Then 1 Else 0 End
        +   Case When City  Is Not Null Then 1 Else 0 End
            As TotalNotNull
    From    `User`
) As A