如何在不使用宏的情况下对ACCESS 2010 SQL中具有相同ID的所有记录进行编号?

问题描述

我得到了一个Access 2010 SQL查询，其结果如下:

I got a Access 2010 SQL query, which has the following result:

ID -- Name  --- description
1 --- Bild1 --- Text1 
1 --- Bild2 --- Text2 
2 --- Bild3 --- Text3 
2 --- Bild4 --- Text4
2 --- Bild5 --- Text5
3 --- Bild2 --- Text6 
4 --- Bild3 --- Text7 

现在，我想改进SQL查询，所有具有相同ID的行都将被编号.
结果应如下所示:

Now I want to improve the SQL query, that all rows with the same ID will be numbered.
The result should look like this:

ID -- Name  --- description --- Number
1 --- Bild1 --- Text1 --- 1 
1 --- Bild2 --- Text2 --- 2 
2 --- Bild3 --- Text3 --- 1 
2 --- Bild4 --- Text4 --- 2 
2 --- Bild5 --- Text5 --- 3 
3 --- Bild2 --- Text6 --- 1 
4 --- Bild3 --- Text7 --- 1 

我该怎么做? ACCESS中不存在RowNumber()函数，我将在单词查询中使用此函数，因此我根本无法使用宏，并且DCount无法提供正确的结果.

How can I do that? A RowNumber() function does not exist in ACCESS, I will use this in query in word, so I can't use Macros at all and DCount does not provide the correct results.

感谢您的帮助.

橄榄

实际的查询有点复杂，因此我尝试使其变得更简单.

The actual query is a bit complicated, therefore I tried to make it a bit easier.

查询:

SELECT g1.akt.KraftAspektID, (
        SELECT Count(*) 
        FROM GesamtKraftAbfrage2 g2 
        WHERE (g1.akt.KraftAspektID = g2.akt.KraftAspektID) 
          AND (g1.Wirkung <=g2.Wirkung)
    ) AS RowNo
FROM GesamtKraftAbfrage2 AS g1
ORDER BY g1.ka.Name;

GesamtKraftAbfrage2是:选择ka.ID AS ID，ka.Name，ka.beschreibung，k.Bildname，k.Bild，ka.MindestkraftLevel，ka.MaximumLevel，kart.Name，k.Name，aazi.Name ASPrimärart，aazi2.Name ASSekundärart ，akt.，kt.，va.VerteidigungText，wt.*，wu.Name AS Wuefelname，DCount("[nummer]"，"[[GesamtKraftAbfrage2]"，"[nummer]< =" & [nummer])AS Reihe来自(((((((((((((((((Kraftaspekt AS ka INNER JOIN Kraft AS k ON k.ID = ka.KraftID)INNER JOIN Kraftart AS kart ON kart.ID = ka.KraftArtID) ka.AspektartZusatzID)左连接AspektArtZusaetze AS aazi2 on aazi2.ID = ka.SekundaereAspektArtID)左连接AktionskostenTransformation AS akt在akt上. va ON va.va.KraftaspektID = ka.ID)左联接KraftAspektWirkung AS wt ON wt.KraftaspektID = ka.ID)左联接Wuerfel AS wu ON wt.WuerfelID = wu.ID;

GesamtKraftAbfrage2 is:SELECT ka.ID AS ID, ka.Name, ka.beschreibung, k.Bildname, k.Bild, ka.MindestkraftLevel, ka.MaximumLevel, kart.Name, k.Name, aazi.Name AS Primärart, aazi2.Name AS Sekundärart, akt., kt., va.VerteidigungText, wt.*, wu.Name AS Wuefelname, DCount("[nummer]","[GesamtKraftAbfrage2]","[nummer]<=" & [nummer]) AS ReiheFROM ((((((((Kraftaspekt AS ka INNER JOIN Kraft AS k ON k.ID = ka.KraftID) INNER JOIN Kraftart AS kart ON kart.ID = ka.KraftArtID) INNER JOIN AspektArtZusaetze AS aazi ON aazi.ID = ka.AspektartZusatzID) LEFT JOIN AspektArtZusaetze AS aazi2 ON aazi2.ID = ka.SekundaereAspektArtID) LEFT JOIN AktionskostenTransformation AS akt ON akt.KraftaspektID = ka.ID) LEFT JOIN KostenTransformation AS kt ON kt.KraftaspektID = ka.ID) LEFT JOIN VerteidigungAbfrage AS va ON va.KraftaspektID = ka.ID) LEFT JOIN KraftAspektWirkung AS wt ON wt.KraftaspektID = ka.ID) LEFT JOIN Wuerfel AS wu ON wt.WuerfelID = wu.ID;

，还有更多与此相关的查询....我无法在此处将它们全部发布.也许只是GesamtKraftAbfrage2输出的列:

and there are many more querys related on that.... I cannot post them all here.Perhaps just the columns of the Output of GesamtKraftAbfrage2:

ID  
ka.Name  
beschreibung  
Bildname    
Bild    
MindestkraftLevel   
MaximumLevel    
kart.Name   
k.Name  
Primärart   
Sekundärart 
akt.KraftAspektID   
akt.Name    
Bewegung    
wt.KraftAspektID    
Anzahl  
WuerfelID   
Wirkung 
Wuefelname

推荐答案

您可以使用相关子查询:

You can use a correlated subquery:

SELECT ID, Name, description,
       (SELECT COUNT(*) + 1
        FROM mytable AS t2
        WHERE t2.ID = t1.ID AND t1.Name > t2.Name)
FROM mytable AS t1
ORDER BY ID, Name

这篇关于如何在不使用宏的情况下对ACCESS 2010 SQL中具有相同ID的所有记录进行编号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！