行内非空字段的计数 [英] 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
.
将会像:
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屋!