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

Answer 2

嗯..。

select ((Name is not null) + (Phone is not null) + (City is not null)) as numNotNull
from user
group by numNotNull;

这会将信息放在单独的行中，而不是放在一行中，但这似乎很有用。

如果您想将其列起来：

select sum(3 = (Name is not null) + (Phone is not null) + (City is not null)) as 'COUNT of 3/3 not NULL',
       sum(2 = (Name is not null) + (Phone is not null) + (City is not null)) as 'COUNT of 2/3 not NULL',
       sum(1 = (Name is not null) + (Phone is not null) + (City is not null)) as 'COUNT of 1/3 not NULL'
from user;