我有一个记录列表:
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( 并创建一个新查询,代码会给我一个"输入参数值 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 的列表。查询 1 看起来像
UniqueID Num1 Order1
110000 1 1
110000 1 2
110001 1 3
110002 1 4
110002 1 5
110002 1 6
查询 1 的访问 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;
为什么不做这样的事情:
SELECT a.UniqueId, count(a.uniqueId)
FROM Table a
GROUP BY a.uniqueId
尝试分析函数。像这样:
select UniqueID
, row_number() over(partition by UniqueID order by UniqueID) times
from query1
最好的选择(除了更改数据库(是:
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;
这假定inflow
对于每个uniqueid
都是唯一的。
如果这不起作用,则可以将数据复制到具有自动编号列的新表中。 您可以使用自动编号列代替 inflow
。