计算表中的出现次数

Question

假设我想计算一个列中字符串中包含的名称出现的总次数，并在该列旁边的新列中显示该名称所有出现的总次数。例如，如果我有：

Name  | Home Address | Special ID 
==================================
Frank | 152414       | aTRF342
Jane  | 4342342      | rRFC432
Mary  | 423432       | xTRF353
James | 32111111     | tLZQ399
May   |    4302443   | 3TRF322

我如何计算特殊标签的出现次数，比如'TRF‘、'RFC’或'LZQ‘，这样表格看起来就像这样：

Name  | Home Address | Special ID  | Occurrences
================================================
Frank | 152414       | aTRF342     |     3
Jane  | 4342342      | rRFC432     |     1
Mary  | 423432       | xTRF353     |     3
James | 32111111     | tLZQ399     |     1
May   |    4302443   | 3TRF322     |     3

当前使用的是Access 2007。使用SQL查询能做到这一点吗？

Answer 1

使用Access 2007，我将示例数据存储在一个名为tblUser1384831的表中。下面的查询返回此结果集。

Name  Home Address Special ID special_tag Occurrences
----  ------------ ---------- ----------- -----------
Frank 152414       aTRF342    TRF                   3
Jane  4342342      rRFC432    RFC                   1
Mary  423432       xTRF353    TRF                   3
James 32111111     tLZQ399    LZQ                   1
May   4302443      3TRF322    TRF                   3

尽管您的问题有一个vba标记，但您不需要为此使用VBA过程。您可以使用SQL和Mid()函数来完成此任务。

SELECT
    base.[Name],
    base.[Home Address],
    base.[Special ID],
    base.special_tag,
    tag_count.Occurrences
FROM
        (
            SELECT
                [Name],
                [Home Address],
                [Special ID],
                Mid([Special ID],2,3) AS special_tag
            FROM tblUser1384831
        ) AS base
    INNER JOIN
        (
            SELECT
                Mid([Special ID],2,3) AS special_tag,
                Count(*) AS Occurrences
            FROM tblUser1384831
            GROUP BY Mid([Special ID],2,3)
        ) AS tag_count
    ON base.special_tag = tag_count.special_tag;

Answer 2

您必须对Special ID的子字符串执行GROUP BY。在MS Access中，您可以阅读有关如何计算子字符串here的内容。

在您的例子中，问题是Special ID列中的数据不遵循标准模式，这种模式很容易通过substring函数提取。您可能需要使用regular expressions来提取这些值，然后将GROUP BY应用于它们。

使用MSSQL、Oracle、PostgreSQL，您可以声明一个存储过程(在MS SQL Server中为example CLR function)，它将为您完成此操作。不确定是否有MS Access。

Answer 3

你可以这样做：

select Name, [Home Address], [Special ID], 
       (select count(*) from [your table] where [Special ID] = RemoveNonAlphaCharacters([Special ID]) ) as Occurrences
from [your table]

辅助函数(从这个link获取)：

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    While PatIndex('%[^a-z]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

    Return @Temp
End