mysql - 计算不为空的列数

房间：

+---------+---------+-----------+-------------+-------------+-------------+---------+
| room_id | room_no | room_stat | room_name   | player_a_id | player_b_id | turn_of |
+---------+---------+-----------+-------------+-------------+-------------+---------+
|       1 |       1 |         0 | blah        |           0 |           0 |       0 |
|       2 |       5 |         0 | second room |           1 |           3 |       0 |
|       3 |       3 |         0 | 3rd room    |           0 |           0 |       0 |
|       4 |       4 |         0 | 4th room    |           0 |           0 |       0 |
+---------+---------+-----------+-------------+-------------+-------------+---------+

我想数一数那个房间里有多少人，例如：
如果玩家身份证！=0或！无效，玩家id！=0或！空，然后
数到2
如果只有玩家id！=0或玩家id！=0然后
数到1
如果玩家和玩家都是
空则返回0；

最佳答案

你可以试试这样的方法：

select room_id,
       ((case when player_a_id <> 0 and player_a_id is not null then 1 else 0 end) +
        (case when player_b_id <> 0 and player_b_id is not null then 1 else 0 end)
       ) as NumInRoom
from room;

与NULL的比较实际上是不必要的。以下内容具有相同的效果：

select room_id,
       ((case when player_a_id <> 0 then 1 else 0 end) +
        (case when player_b_id <> 0 then 1 else 0 end)
       ) as NumInRoom
from room;

关于mysql - 计算不为空的列数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16474449/