blocks|key|273947|text|您应该有一个关系表TeamMember+->+Team，其关系为1:+n+:n。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|273948|然后在查询中加入两个表，并计数团队成员。|273949|select+t.Id,+count(tm.Id)
from+teams+t,+teams_members+tm
where+t.Id+=+tm.Team_Id
group+by+t.Id|code-block|syntax|javascript|273950|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You should have a relation table TeamMember -> Team with relation of 1:n \ n:n.

Then in your query join the two tables,Group by the team and count the team members.

<pre><code>select t.Id, count(tm.Id)
from teams t, teams_members tm
where t.Id = tm.Team_Id
group by t.Id
</code></pre>

blocks|key|83280|text|Imho，这是一个非常糟糕的设计:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|83281|首先，我将规格化数据库。如果这样做了，您关于团队成员数量的问题是一个简单的SELECT+COUNT(*)+...。|offset|length|style|CODE|83282|为团队做一张桌子，一张给成员，第三张，在那里你把团队成员和一个团队“连接”起来。|83283|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Database_normalization^0|0|11|J|5|3|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@$D|V|E|W|1|X]]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Imho, that's a really bad design :)

First of all, I would <a href="http://en.wikipedia.org/wiki/Database_normalization" rel="nofollow">normalize</a> the db. If that is done, your question about the number of team members is a simple <code>SELECT COUNT(*) ...</code>

Make a table for the teams, one for the member and a third where you "concat" the team member with a team.

blocks|key|565442|text|解决问题的关键是使用特定的if函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|565443|对于甲骨文来说，它可能是解码器。|offset|length|565444|SELECT+
+DECODE(COL1,+null,+0,+1)+%2B
+DECODE(COL2,+null,+0,+1)+%2B
+DECODE(COL3,+null,+0,+1)+%2B
+DECODE(COL4,+null,+0,+1)+AS+Result
FROM+YOUR_TABLE|code-block|syntax|javascript|565445|对MySQL来说，可能是如果。|565446|SELECT+
+IF(COL1+is+null,+0,+1)+%2B
+IF(COL2+is+null,+0,+1)+%2B
+IF(COL3+is+null,+0,+1)+%2B
+IF(COL4+is+null,+0,+1)+AS+Result
FROM+YOUR_TABLE|565447|如果您使用的是Server，则应该使用案例语句|565448|SELECT
++++CASE+COL1+WHEN+IS+NULL+THEN+0+ELSE+1+END+%2B+
++++CASE+COL2+WHEN+IS+NULL+THEN+0+ELSE+1+END+%2B
++++CASE+COL3+WHEN+IS+NULL+THEN+0+ELSE+1+END+
FROM+YOUR_TABLE|565449|entityMap|0|LINK|mutability|MUTABLE|url|http://www.techonthenet.com/oracle/functions/decode.php|1|http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if|2|http://msdn.microsoft.com/pl-pl/library/ms181765.aspx^0|0|C|3|0|0|0|C|2|1|0|0|J|2|2|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|9|@$D|1B|E|1C|1|1D]]|A|$]]|$1|M|3|N|5|H|7|1E|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1F|8|@]|9|@$D|1G|E|1H|1|1I]]|A|$]]|$1|Q|3|R|5|H|7|1J|8|@]|9|@]|A|$I|J]]|$1|S|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]|12|$5|V|W|X|A|$Y|13]]]]

The key to solve your problem is to use specific if function 

For Oracle it could be <a href="http://www.techonthenet.com/oracle/functions/decode.php" rel="nofollow">DECODE</a>. 

<pre><code>SELECT 
 DECODE(COL1, null, 0, 1) +
 DECODE(COL2, null, 0, 1) +
 DECODE(COL3, null, 0, 1) +
 DECODE(COL4, null, 0, 1) AS Result
FROM YOUR_TABLE
</code></pre>

For MySQL it could be <a href="http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if" rel="nofollow">IF</a>.

<pre><code>SELECT 
 IF(COL1 is null, 0, 1) +
 IF(COL2 is null, 0, 1) +
 IF(COL3 is null, 0, 1) +
 IF(COL4 is null, 0, 1) AS Result
FROM YOUR_TABLE
</code></pre>

I you are using the SQL Server, you should use <a href="http://msdn.microsoft.com/pl-pl/library/ms181765.aspx" rel="nofollow">CASE</a> statement

<pre><code>SELECT
 CASE COL1 WHEN IS NULL THEN 0 ELSE 1 END + 
 CASE COL2 WHEN IS NULL THEN 0 ELSE 1 END +
 CASE COL3 WHEN IS NULL THEN 0 ELSE 1 END 
FROM YOUR_TABLE
</code></pre>

blocks|key|273999|text|正如其他人所指出的，这是一个糟糕的设计，你应该尝试正常化。然而，我会尝试这样的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|274000|Select+Count(*)+from+teams+where
+(team_id+=+5)+and
+(team_member_1+is+not+null+or
++team_member_2+is+not+null+or
++team_member_3+is+not+null+or
++team_member_4+is+not+null+or
++team_member_5+is+not+null)|code-block|syntax|javascript|274001|您至少应该通过创建Team表和成员表并将成员引用到Team，从而将其规范化为第一个普通形式。|274002|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

As others have pointed out, this is a bad design and you should try to normalize. However, I'd try something like this:

<pre><code>Select Count(*) from teams where
 (team_id = 5) and
 (team_member_1 is not null or
 team_member_2 is not null or
 team_member_3 is not null or
 team_member_4 is not null or
 team_member_5 is not null)
</code></pre>

You should at least normalize to the first normal form by creating a Team table and a Member table and reference Member to Team.

blocks|key|83358|text|如果您的服务器产品支持布尔数据类型，并在适当的上下文中(例如MySQL+)将其隐式转换为整数，那么您可以尝试以下模式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|83359|SELECT
++(team_member_1+IS+NOT+NULL)+%2B
++(team_member_2+IS+NOT+NULL)+%2B
++(team_member_3+IS+NOT+NULL)+%2B
++(team_member_4+IS+NOT+NULL)+%2B
++(team_member_5+IS+NOT+NULL)+AS+MemberCount
FROM+Teams
WHERE+team_id+=+5|code-block|syntax|javascript|83360|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>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
</code></pre>

<blockquote>
 Possible Duplicate: 
 <a href="https://stackoverflow.com/questions/8596500/count-the-null-columns-in-a-row-in-sql">Count the Null columns in a row in SQL</a> 
</blockquote>



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.

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

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

If my head if would go something like:

<pre><code>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
</code></pre>

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.

Count number of Not Null fields within a Row

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

可能重复：我一直在寻找这个问题的答案，但我既找不到有用的答案，也找不到合适的答案，而且我是SQL的新手。我有一个包含事件团队的表，除其他列外，还有team_member_1、team_member_2、team_member_3、team_member_4、team_member_5。每个团队都存储该团队成员的名称，每...

问列中的非空字段数
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列中的非空字段数EN