计算一行中非空字段的数量 [英] Count number of Not Null fields within a Row

问题描述

可能的重复:
在 SQL 中计算一行中的 Null 列

我已经四处寻找答案，但找不到有效或合适的答案，而且我是 SQL 新手.

I've had a dig around for answers to this, but can't find either a working or suitable answer and I'm a novice with SQL.

我有一个包含活动团队的表格，在其他列中我有 team_member_1、team_member_2、team_member_3、team_member_4、team_member_5.每个存储该团队成员的姓名，每个团队的成员数量不同(3 到 5 个).

I've got a table containing teams for an event, and amongst other columns I have team_member_1, team_member_2, team_member_3, team_member_4, team_member_5. Each one stores the name of that team memember, each team has a different amount of members (between 3 and 5).

我正在尝试通过计算该行/团队中有多少列/字段NOT NULL来计算团队中的人数.

I'm trying count the number of people in a team by counting how many columns/ fields within that row/team are NOT NULL.

如果我的头会变成这样:

If my head if would go something like:

Select Count NOT NULL team_member_1, team_member_2, team_member_3,  
                      team_member_4, team_member_5 
from Teams 
where team_id = 5

这当然行不通.然后我想把这个数字乘以 20 英镑(因为赛事费用是每人 20 英镑)，给我每个团队欠的金额，这将在网站上显示出来.希望一切都有意义.

Of course this doesn't work. I then want to times this figure by £20 (as the event cost is £20 per head), to give me the amount each team owes, and that will be echo'd out on the website. Hope all makes sense.

推荐答案

如果您的服务器产品支持布尔数据类型并在适当的上下文中将其隐式转换为整数，例如 MySQL，那么您可以尝试以下模式:

If your server product supports the boolean data type and implicitly converts it to integer when in a proper context, like MySQL does, for instance, then you could try the following pattern:

SELECT
  (team_member_1 IS NOT NULL) +
  (team_member_2 IS NOT NULL) +
  (team_member_3 IS NOT NULL) +
  (team_member_4 IS NOT NULL) +
  (team_member_5 IS NOT NULL) AS MemberCount
FROM Teams
WHERE team_id = 5

这篇关于计算一行中非空字段的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！