选择第一个字母字符的出现次数之和 [英] Select the sum of occurances of first alphabetical character

问题描述

我需要做的是创建一条select语句，该语句输出表中字段中第一个字符的总和，这样输出看起来就像

Hi what i need to do is create a select statement which outputs the sum of the first character in a field within the table so the output would look something like

A,12
B,0
C,20
D,14
E,0
ect...

该表称为联系人"，在上面出现了12个人的名字以字母A开头的人

The table is called contacts, in the above there was 12 occurrences of people whose names begin with the letter A

我希望我已经正确解释了

I hope i have explained this correctly

推荐答案

让我们用EMP表示例来了解这一点.

Let's understand this with EMP table example.

SQL> with
  2      letters
  3      as
  4      (select chr( ascii('A')+level-1 ) letter
  5         from dual
  6      connect by level <= 26
  7      )
  8  SELECT substr(ename, 1, 1) AS init_name,
  9  count(*) cnt
 10  FROM emp
 11  WHERE substr(ename, 1, 1) IN (SELECT letter from letters)
 12  GROUP BY substr(ename, 1, 1)
 13  UNION
 14  SELECT l.letter AS init_name,
 15  0 cnt
 16  FROM letters l
 17  WHERE l.letter NOT IN (SELECT substr(ename, 1, 1) FROM emp)
 18  ORDER BY init_name
 19  /

I        CNT
- ----------
A          2
B          1
C          1
D          0
E          0
F          1
G          0
H          0
I          0
J          2
K          1
L          0
M          2
N          0
O          0
P          0
Q          0
R          0
S          2
T          1
U          0
V          0
W          1
X          0
Y          0
Z          0

26 rows selected.

SQL>

因此，它给出每个名字字母的count，对于名字中不存在的其他字母，计数为0.

So, it gives the count of each letter of first name, and for the other letters which does not exist in the first name, the count is 0.

这篇关于选择第一个字母字符的出现次数之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！