如何排序将结果查询信息插入到表中，同时将现有表记录保留在顶部？
我正在尝试将表中的值插入到另一个现有表中，并且仅将我插入的值根据特定列按降序排序，同时将现有记录保留在表的顶部。我该怎么做？我尝试使用 Order By 语句，但无论是使用从中提取的表的列名还是目标表的列名，我都会收到错误。这也是使用 DoCmd.RunSQL 在 VBA 中运行的。
这是我现有的查询：
插入 AllMetersAvgRSSI
（经度、纬度、AvgRSSI）
选择 前经度、前纬度、 DataByColl。[平均 RSSI]
来自[Prem]
左边 加入 DataByColl ON (Prem.meter_miu_id = DataByColl.[MIU ID])
按 [AvgRSSI] desc 排序
最终结果
我继续摆弄这个，发现你可以使用一个订单，就像我上面显示的那样，完全按照我想要做的那样。我显然遇到的问题是由我想要排序的列的名称仅从 Avg RSSI 更改为 AvgRSSI 引起的。当我将目标表更改为与源表具有相同的字段名称时，它会对传入信息进行排序，同时保留现有信息。我还做了一个测试，将目标表的名称更改为 AverageRSSI，它的工作方式相同。所以最终是字段名称仅因空格不同而导致了问题。最终的查询是：
插入 AllMetersAvgRSSI
（经度、纬度、[平均 RSSI]）
选择 前经度、前纬度、 DataByColl。[平均 RSSI]
来自[Prem]
左边 加入 DataByColl ON (Prem.meter_miu_id = DataByColl.[MIU ID])
按 [Avg RSSI] desc 排序
I am trying to insert values from a table into another existing table and have just the values I am inserting be sorted in descending order based on a specific column while leaving the existing records at the top of the table. How do I do that? I have tried to use an Order By statement but whether I use the column name of the table I'm pulling from or the destination table's column name I get an error. Also this is being run in VBA using DoCmd.RunSQL.
Here is my existing query:
INSERT INTO AllMetersAvgRSSI
(longitude,latitude,AvgRSSI)
Select
Prem.longitude, Prem.latitude,
DataByColl.[Avg RSSI]
From [Prem]
Left
Join DataByColl ON (Prem.meter_miu_id
= DataByColl.[MIU ID])
Order BY [AvgRSSI] desc
Final Result
I continued to fiddle with this and discovered than you can use an order by just like I have shown above to do exactly as I was trying to do. The problem I was apparently having was caused by the names of the column I wanted sorted being changed only from Avg RSSI to AvgRSSI. When I changed the destination table to have the same field name as the source table it orders the incoming information while leaving the existing information alone. I also did a test where I changed the name of the destination table to AverageRSSI and it worked the same way. So in the end it was the names of the fields being differed only by a space that was causing the problem. The final Query is:
INSERT INTO AllMetersAvgRSSI
(longitude,latitude,[Avg RSSI])
Select
Prem.longitude, Prem.latitude,
DataByColl.[Avg RSSI]
From [Prem]
Left
Join DataByColl ON (Prem.meter_miu_id
= DataByColl.[MIU ID])
Order BY [Avg RSSI] desc
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
从数据库的角度来看，INSERT 中的排序没有任何意义。数据库如何将行放入表中取决于表的底层物理结构，而不是它们的插入顺序。
也许您的应用程序依赖于按一定顺序的自动递增列，这将取决于插入的顺序，但如果是这种情况，那么我会说您在数据库设计中犯了一个错误，因为不应该是围绕自动递增列设计的业务逻辑。
从
INSERT语句中删除ORDER BY，如果您稍后需要按特定顺序检索行，则在其中使用ORDER BY。Ordering in an
INSERTmakes no sense from a database standpoint. How the database puts the rows into a table depends on the underlying physical structure of the table, not the order in which they are inserted.Maybe your application relies on an auto incrementing column being in a certain order which would then be dependent on the order of insertion, but if that's the case then I would say that you've made a mistake in your database design as there shouldn't be business logic designed around an auto incrementing column.
Remove the
ORDER BYfrom yourINSERTstatement and if you need to retrieve rows in a particular order later then use anORDER BYthere.创建临时表，按所需顺序添加第一个结果集。将新值插入表中，查询表以返回带有 order by 的新结果到临时表中，选择临时表，结果将按照您添加的顺序排列，除非您执行另一个 order by 。
显示结果后不要忘记删除临时表。
Create a temp table, add the first result set in the desired order. Insert your new values into the table, query the table to return your new results with an order by into your temp table, select your temp table the results will be in the order you added them unless you do another order by.
Don't forget to drop your temp table after displaying the results.