我有一份记录清单:
UniqueID
110000
110000
110001
110002
110002
110002
并希望获得每条记录显示的次数的结果,例如:
UniqueID times
110000 1
110000 2
110001 1
110002 1
110002 2
110002 3
我的代码是这样的:
SELECT m.UniqueID,
(SELECT Count(*)
From [Query1] AS m2
Where m2.UniqueID =m.UniqueID And m2.Order1 <= m.Order1) AS COL2b
FROM [Query1] AS m;
这会给我一个“无效的函数参数”的结果,如果我移除order1的参数,代码就会正常工作,比如
SELECT m.UniqueID,
(SELECT Count(*)
From [Query1] AS m2
Where m2.UniqueID =m.UniqueID) AS COL2b
FROM [Query1] AS m;
如果我将代码更改为count(B.Num1)并创建一个新查询,代码将给出一个"Enter parameter value A.Order1“结果,如下所示:
SELECT A.UniqueID, (Select Count(B.Num1)
From [Query1] As B
Where B.UniqueID = A.UniqueID AND B.Order1 <= A.Order1) AS COL2b
FROM [Query1] AS A
ORDER BY A.UniqueID;
我确信Order1字段或访问权限有问题。我怎么才能修复它?
作为参考,Num1是1的列表。Query1看起来像
UniqueID Num1 Order1
110000 1 1
110000 1 2
110001 1 3
110002 1 4
110002 1 5
110002 1 6
查询1的access sql代码是:
SELECT A.UniqueID, A.Num1, (Select Count(B.Num1)
From [TableRecords] As B
Where B.UniqueID <= A.UniqueID) AS Order1
FROM [TableRecords] AS A
ORDER BY A.UniqueID;
发布于 2019-03-26 23:16:21
为什么不这样做呢:
SELECT a.UniqueId, count(a.uniqueId)
FROM Table a
GROUP BY a.uniqueId
发布于 2019-03-26 23:57:04
尝试分析函数。如下所示:
select UniqueID
, row_number() over(partition by UniqueID order by UniqueID) times
from query1
发布于 2019-03-27 09:00:40
你最好的选择(除了改变数据库)是:
SELECT m.UniqueID,
(SELECT Count(*)
FROM [Query1] AS m2
WHERE m2.UniqueID = m.UniqueID AND
m2.inflow <= m.inflow
) AS COL2b
FROM [Query1] AS m;
这里假设每个uniqueid
的inflow
都是唯一的。
如果这不起作用,那么您可以将数据复制到一个具有自动编号列的新表中。您可以使用自动编号列而不是inflow
。
https://stackoverflow.com/questions/55360463
复制相似问题