使用SQL计数的字符在Access数据库列 [英] Counting characters in an Access database column using SQL

问题描述

我有以下表

col1  col2  col3  col4
====  ====  ====  ====
1233  4566  ABCD  CDEF 
1233  4566  ACD1  CDEF
1233  4566  D1AF  CDEF

我要算在COL3的人物，所以从previous表中的数据将是：

I need to count the characters in col3, so from the data in the previous table it would be:

char  count
====  =====
A         3
B         1
C         2
D         3
F         1
1         2

这是可能实现只用SQL？

Is this possible to achieve by using SQL only?

目前，我在想传递一个参数中的SQL查询，并通过一个，然后求和计算的人物之一，但我并没有启动VBA部分着呢，坦言不希望这样做。

At the moment I am thinking of passing a parameter in to SQL query and count the characters one by one and then sum, however I did not start the VBA part yet, and frankly wouldn't want to do that.

这是我的时刻查询：

PARAMETERS X Long;
SELECT First(Mid(TABLE.col3,X,1)) AS [col3 Field], Count(Mid(TABLE.col3,X,1)) AS Dcount
FROM TEST
GROUP BY Mid(TABLE.col3,X,1)
HAVING (((Count(Mid([TABLE].[col3],[X],1)))>=1));

思路和帮助是多少AP preciated，因为我通常不使用Access和SQL。

Ideas and help are much appreciated, as I don't usually work with Access and SQL.

推荐答案

明知colum3有4固定的长度，这个问题是很容易的。

Knowing that colum3 has a fixed length of 4, this problem is quite easy.

假定有第3列一个视图V四列，每一个字符。

Assume there is a view V with four columns, each for one character in column 3.

V(c1, c2, c3, c4)

不幸的是，我不熟悉的访问特定的SQL，但是这是你所需要的一般的SQL语句：

Unfortunately, I'm not familiar with Access-specific SQL, but this is the general SQL statement you would need:

SELECT c, COUNT(*) FROM
(
SELECT c1 AS c FROM V
UNION ALL
SELECT c2 FROM V
UNION ALL
SELECT c3 FROM V
UNION ALL
SELECT c4 FROM V
)
GROUP BY c

这篇关于使用SQL计数的字符在Access数据库列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！