MySQL -如何从所有记录中获取特定值的计数

I have course table and I am storing completion status of users with 'A'. Now I want to get how many A is available from CompletionStatus field for all records.

我有课程表，我正在用“A”存储用户的完成状态。现在，我想要知道所有记录的CompletionStatus字段有多少个A。

I want this result: A = 5.

我想要这个结果:A = 5。

Course Table:

CourseRecordIdx     User    CompletionStatus 
---------------     ----    --------------------
1                   152     A___A_______________
2                   147     AA_______A__________

I have tried with char_length but getting count with underscore and I want to get only total of A:

我尝试过char_length，但是用下划线计数，我只想得到A:

SELECT char_length(CompletionStatus) FROM `course` where CourseRecordIdx = 36

Any idea how to get result with select query?

您知道如何使用select查询获得结果吗?

Thanks.

谢谢。

4 个解决方案

#1

You can try with this simplest way:

你可以尝试用最简单的方法:

 SELECT length(REPLACE("field_name","_","")) FROM `tbl_name`;

#2

You can use LENGTH and REPLACE for that purpose :

你可以用长度代替这个目的:

SELECT LENGTH(CompletionStatus) - LENGTH(REPLACE(CompletionStatus, 'A', '')) as count_Char
 FROM `course`

This basically counts how many characters are in that string, and then checks the difference between that number, and the number of characters with out the specific character.

这基本上计算了字符串中有多少个字符，然后检查这个数字和具有特定字符的字符数之间的差异。

#3

I suppose the following will work, first replace all _ with ''. After that use char_length() function.

我想接下来的工作，首先要用“。”然后使用char_length()函数。

SELECT CHAR_LENGTH(REPLACE(CompletionStatus,'_','') as totalA FROM course where CourseRecordIdx = 36;

#4

You can write this query for this:

您可以为此编写此查询:

SELECT CourseRecordIdx,User,CompletionStatus,
ROUND (   
    (
    LENGTH(CompletionStatus)- LENGTH( REPLACE ( CompletionStatus, "A", "")) 
    ) / LENGTH("A")) AS count    
from `course` where CourseRecordIdx = 36

#1