如何生成基于列的第一个字符的组计数的报告?

I have this table:

我有这个表:

CREATE TABLE [dbo].[Phrase] (
    [PhraseId]     UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [English]      NVARCHAR (MAX)   NOT NULL,
    [Romaji]       NVARCHAR (MAX)   NULL,
    [EnglishAscii] AS               (ascii([English])) PERSISTED,
    PRIMARY KEY CLUSTERED ([PhraseId] ASC)
);

What I would like to do is to get a report that looks something like this:

我想做的是得到一个类似这样的报告:

A 25
B 35
C 10
D 99
...
Y 3

All the strings in the English column have a first character that is uppercase.

英语列中的所有字符串都有一个大写的第一个字符。

Can someone give me some hints as to how I can do this kind of a report?

有人能给我一些关于我如何做这种报告的提示吗?

3 个解决方案

#1

Use LEFT string function

使用了字符串函数

Select Left([English],1), Count(1)
From Yourtable
Group by Left([English],1)

or you can use SUBSTRING string function

也可以使用子字符串函数

Select Substring([English],1,1), Count(1)
From Yourtable
Group by Substring([English],1,1)

#2

Use the UPPER keyword in order to make the first character of the column [English] in to upper case ,if it is not.

如果不是，则使用UPPER关键字将列[English]的第一个字符设置为大写。

SELECT UPPER(Left([English],1)) Col, 
       Count(1) CNT
FROM Yourtable
GROUP BY Left([English],1)

#3

Have a derived table which simply returns that first character. GROUP BY it's result:

有一个只返回第一个字符的派生表。组的结果:

select letter, count(*)
from
(
    select substring([English], 1, 1) as letter
    from [dbo].[Phrase]
) dt
group by letter

This way you only have to write the substring expression once. Easier to write without errors, and easier and safer to maintain/update.

这样，您只需编写一次substring表达式。更容易编写，没有错误，更容易和更安全的维护/更新。

#1