首页 / 技术问答 / Sql问答列表 / 问答详情

Sql 基于唯一值在Access查询中创建计数器

我正在努力在访问查询中创建计数器. 这个问题我已经看过了

Access query counter per group个

但我不能让它为我工作.

我的问题是:

我在一个关系数据库中有四个表，其中包含以下MWE数据

[考试年]

-----------------------------------------
|      year_id      |     year_date     |
-----------------------------------------
|                 1 |              2020 |
-----------------------------------------
|                 2 |              2021 |
-----------------------------------------

[纸][纸]

-----------------------------------------
|     paper_id      |    paper_name     |
-----------------------------------------
|                 1 | H556-01           |
-----------------------------------------
|                 2 | H556-02           |
-----------------------------------------

[问题]

-----------------------------------------------------------------------------------------------------
|    question_id    |  question_number  |     paper_id      |      year_id      |    is_selected    |
-----------------------------------------------------------------------------------------------------
|                 1 |                 1 |                 1 |                 1 |               Yes |
-----------------------------------------------------------------------------------------------------
|                 2 |                 1 |                 1 |                 2 |                No |
-----------------------------------------------------------------------------------------------------
|                 3 |                 2 |                 1 |                 1 |               Yes |
-----------------------------------------------------------------------------------------------------

[图像问题]

---------------------------------------------------------------------------------------------------
| question_image_id |           image_filename            |    question_id    |    image_page     |
---------------------------------------------------------------------------------------------------
|                 2 | 2020-H556-01-01-0.png               |                 1 |                 0 |
---------------------------------------------------------------------------------------------------
|                 3 | 2020-H556-01-01-1.png               |                 1 |                 1 |
---------------------------------------------------------------------------------------------------
|                 4 | 2021-H556-01-01-0.png               |                 2 |                 0 |
---------------------------------------------------------------------------------------------------
|                 5 | 2021-H556-01-01-1.png               |                 2 |                 1 |
---------------------------------------------------------------------------------------------------
|                 6 | 2021-H556-01-01-2.png               |                 2 |                 2 |
---------------------------------------------------------------------------------------------------
|                 7 | 2020-H556-01-02-0.png               |                 3 |                 0 |
---------------------------------------------------------------------------------------------------
|                 8 | 2020-H556-01-02-1.png               |                 3 |                 1 |
---------------------------------------------------------------------------------------------------

这些表具有以下关系

relationships

我有一个问题:

SELECT Question.question_id, Question.question_number, Paper.paper_name, Exam_Year.year_date, Question_image.image_filename, Question_image.image_page, Question.is_selected
FROM Exam_Year INNER JOIN ((Paper INNER JOIN Question ON Paper.paper_id = Question.paper_id) INNER JOIN Question_image ON Question.question_id = Question_image.question_id) ON Exam_Year.year_id = Question.year_id
WHERE (((Question.is_selected)=True));

它会生成以下内容:

enter image description here

我想在结尾处添加另一列，即从1开始的计数器，它在Quest_id值更改时递增1.

我想我必须使用dcount，但我不确定如何实现它.

我已经try 添加一个列作为(按照上面的链接)

DCount("question_id","Question","[Question.question_id] <= " & [Question.question_id]) AS QuestionNumber

this generates the following enter image description here

它分配了[Questions]表中与Quest_id字段相对应的行(即Quest_id 3位于[Questions]表的第3行)

我希望最后一列包含问题1的数字1(问题id的第一个唯一实例)和问题3的数字2(问题id的第二个唯一实例).

如果能帮上忙，我会非常感激.

谢谢 烈士

推荐答案

保存您的查询，您就可以在一个简单的查询中使用我的RowNumber函数Found here:

SELECT 
    *, RowNumber(CStr([question_id])) AS QuestionNumber
FROM
    YourSavedQuery
WHERE 
    (RowNumber("","",True)=0);

功能:

' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query having an ID with an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' Usage (typical select query having an ID without an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' Usage (with group key):
'   SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowNumber(vbNullString, True)
' 2. Run query:
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' 2020-05-29. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
    ByVal Key As String, _
    Optional ByVal GroupKey As String, _
    Optional ByVal Reset As Boolean) _
    As Long
    
    ' Uncommon character string to assemble GroupKey and Key as a compound key.
    Const KeySeparator      As String = "¤§¤"
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotRemoveKey   As Long = 5
  
    Static Keys             As New Collection
    Static GroupKeys        As New Collection

    Dim Count               As Long
    Dim CompoundKey         As String
    
    On Error GoTo Err_RowNumber
    
    If Reset = True Then
        ' Erase the collection of keys and group key counts.
        Set Keys = Nothing
        Set GroupKeys = Nothing
    Else
        ' Create a compound key to uniquely identify GroupKey and its Key.
        ' Note: If GroupKey is not used, only one element will be added.
        CompoundKey = GroupKey & KeySeparator & Key
        Count = Keys(CompoundKey)
        
        If Count = 0 Then
            ' This record has not been enumerated.
            '
            ' Will either fail if the group key is new, leaving Count as zero,
            ' or retrieve the count of already enumerated records with this group key.
            Count = GroupKeys(GroupKey) + 1
            If Count > 0 Then
                ' The group key has been recorded.
                ' Remove it to allow it to be recreated holding the new count.
                GroupKeys.Remove (GroupKey)
            Else
                ' This record is the first having this group key.
                ' Thus, the count is 1.
                Count = 1
            End If
            ' (Re)create the group key item with the value of the count of keys.
            GroupKeys.Add Count, GroupKey
        End If

        ' Add the key and its enumeration.
        ' This will be:
        '   Using no group key: Relative to the full recordset.
        '   Using a group key:  Relative to the group key.
        ' Will fail if the key already has been created.
        Keys.Add Count, CompoundKey
    End If
    
    ' Return the key value as this is the row counter.
    RowNumber = Count
  
Exit_RowNumber:
    Exit Function
    
Err_RowNumber:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotRemoveKey
            ' GroupKey is not present, thus cannot be removed.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_RowNumber
    End Select

End Function

Sql相关问答推荐

判断时间之间是否有时间

Group By子句返回太多行

在postgres中动态计算出现次数并插入到json中

转换表中的数据

使用WHERE子句进行筛选时，SQL SELECT查询返回总计数

如何在SQL中更新Json字符串

找到最新的连线

在xml.Modify方法中使用子字符串和可能的替代方法

SQL数据库规范化与数据插入

如何简化此PostgreSQL查询以计算平均值？

如何使用 join 和 where 子句从另一表中仅删除一个表中的值

Oracle PL/SQL长期运行问题

带聚合函数的 percentile_cont

如果 SQL 中不存在数据，如何根据某个 ID 为所有日期添加前一行

Postgres：表的累积视图

条件意外地显着降低性能的地方

如何从 2 个 SQLite 表构建嵌套对象？

使用 SAVE TRANSACTION 时 BEGIN 和 COMMIT 语句的数量不匹配

并非所有变量都绑定在 PL SQL 函数中

如何使用 pg-promise 创建要在复杂的 sql 查询中使用的 VALUES 列表？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630