是否可以在 Access Query 中添加每組排序（排序/組）欄位，以便按照下表按值排序：
注： ID為唯一索引（Auto Number）
uj5u.com熱心網友回復：
考慮：
SELECT Data.ID, Data.Group1, Data.Value,
DCount("*","Data","Group1='" & [Group1] & "' AND Value<" & [Value]) 1 AS GrpSeq
FROM Data
ORDER BY Data.Value;
或者
SELECT Data.ID, Data.Group1, Data.Value, (
SELECT Count(*) AS Cnt FROM Data AS Dupe
WHERE Dupe.Value<Data.Value AND Dupe.Group1=Data.Group1) 1 AS GrpSeq
FROM Data
ORDER BY Data.Value;
uj5u.com熱心網友回復：
在真正的 RDBMS 上，通常會為此使用視窗函式ROW_NUMBER。
select *
, row_number() over (partition by Group1 order by Value, ID) as Rownum
from yourtable
但另一種方法是使用相關子查詢。
select *,
(select count(*) from yourtable t2
where t2.Group1 = t.Group1
and (t2.Value < t.Value
or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum
from yourtable t
order by Group1, Rownum
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/362090.html