具有复杂排序要求的Access查询
具有复杂排序要求的Access查询
提问于 2019-01-19 08:33:44
我必须从不断扩展的数据集生成Q-Q图。为了做到这一点，我必须让两列相互独立地排序。
如下所示：
主表
F1 | F2
---+----
6 | 10
8 | 7
2 | 5最终的表应该是这样排序的：
F1 | F2
---+----
2 | 5
6 | 7
8 | 10我尝试使用两个连接的子查询和一个向每个子查询添加行号的VBA函数来完成此操作。它没有返回任何值，尽管每个子查询都是独立工作的。
这是我尝试使用的SQL查询：
SELECT
wautonumber([Au_org_gpt]) AS index_no,
ORG.Project, ORG.Au_org_gpt AS Au_org_gpt_QQ,
MTS.Au_mts_gpt AS Au_mts_gpt_QQ
FROM
(SELECT
wautonumber([Au_org_gpt]) AS index_no,
MCIPLD_geochem_mts.Project, MCIPLD_geochem_mts.Au_org_gpt
FROM
MCIPLD_geochem_mts
ORDER BY
MCIPLD_geochem_mts.Au_org_gpt) AS ORG
INNER JOIN
(SELECT
wautonumber([Au_mts_gpt]) AS index_no,
MCIPLD_geochem_mts.Project, MCIPLD_geochem_mts.Au_mts_gpt
FROM
MCIPLD_geochem_mts
ORDER BY
MCIPLD_geochem_mts.Au_mts_gpt) AS MTS ON ORG.index_no = MTS.index_no;回答 1
Stack Overflow用户
发布于 2019-01-19 09:39:33
假设两个字段的每条记录中都有数据。
如果两个字段都有唯一的数值，请考虑：
SELECT Query1.Seq, Query1.F1, Query2.F2
FROM
(SELECT DCount("*","Main","F2<=" & [F2]) AS Seq, Main.F2
FROM Main ORDER BY Main.F2) AS Query2
INNER JOIN
(SELECT DCount("*","Main","F1<=" & [F1]) AS Seq, Main.F1
FROM Main ORDER BY Main.F1) AS Query1
ON Query2.Seq = Query1.Seq
ORDER BY Query1.Seq;另一种查询方法需要两个查询对象：
Query1
SELECT "F1" AS Src, F1 AS Data FROM Main
UNION SELECT "F2", F2 FROM Main;查询2
TRANSFORM First(Query1.Data) AS FirstOfData
SELECT DCount("*","Query1","Src='" & [Src] & "' AND Data<=" & [Data]) AS Seq
FROM Query1
GROUP BY DCount("*","Query1","Src='" & [Src] & "' AND Data<=" & [Data])
PIVOT Query1.Src;对于大型数据集，这两种解决方案的执行速度都可能很慢。
下面是VBA方法。构建一个名为MainSorted的表，其中包含3个字段：
序列号-自动编号
F1 -编号
F2 -编号
Sub MainSort()
Dim rsSrc As Recordset, db As Database
Dim lngRow As Long
Set db = CurrentDb
db.Execute "DELETE FROM MainSorted"
db.Execute "INSERT INTO MainSorted(F1) SELECT F1 FROM Main ORDER BY F1"
Set rsSrc = db.OpenRecordset("SELECT F2 FROM Main ORDER BY F2")
lngRow = DMin("Seq", "MainSorted")
While Not rsSrc.EOF
db.Execute "UPDATE MainSorted SET F2=" & rsSrc!F2 & " WHERE Seq = " & lngRow
rsSrc.MoveNext
lngRow = lngRow + 1
Wend
End Sub或者这个版本：
Sub MainSort()
Dim rsSrc1 As Recordset, rsSrc2 As Recordset, db As Database
Set db = CurrentDb
db.Execute "DELETE FROM MainSorted"
Set rsSrc1 = db.OpenRecordset("SELECT F1 FROM Main ORDER BY F1")
Set rsSrc2 = db.OpenRecordset("SELECT F2 FROM Main ORDER BY F2")
While Not rsSrc1.EOF
db.Execute "INSERT INTO MainSorted(F1,F2) VALUES(" & rsSrc1!F1 & "," & rsSrc2!F2 & ")"
rsSrc1.MoveNext
rsSrc2.MoveNext
Wend
End Sub考虑@CPerkins的评论：
Sub MainSort()
Dim rsSrc1 As Recordset, rsSrc2 As Recordset, rsDest As Recordset, db As Database
Set db = CurrentDb
db.Execute "DELETE FROM MainSorted"
Set rsSrc1 = db.OpenRecordset("SELECT F1 FROM Main ORDER BY F1")
Set rsSrc2 = db.OpenRecordset("SELECT F2 FROM Main ORDER BY F2")
Set rsDest = db.OpenRecordset("SELECT * FROM MainSorted")
While Not rsSrc1.EOF
rsDest.AddNew
rsDest!F1 = rsSrc1!F1
rsDest!F2 = rsSrc2!F2
rsDest.Update
rsSrc1.MoveNext
rsSrc2.MoveNext
Wend
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54263080
复制相关文章
相似问题
腾讯云开发者
