SQL CE：限制查询中返回的行
在SQL Compact Edition 3.5中，请注意，我所说的是Compact Edition - 有没有办法将行数限制为只有2？比如使用 LIMIT 或 TOP。我真的不想使用 SqlCEDataReader 或 SqlCEResultSet 的任何内容。我想在查询中执行所有限制。现在这可能吗？我环顾四周，似乎并非如此。
编辑-
为了响应 Dave Swersky 的数据请求，并在某些列上使用 Min()/Max() 作为获取前 2 行的方法，这里是一些示例（已消毒）数据：
Line Site Function Status
1010 Las Vegas new 4
1020 DC send 1
1030 Portland copy 1
1040 SF copy 1
1050 Portland copy 1
1060 DC send 1
*还有比这更多的列，但这些是重要的。
抱歉缺乏直观的数据（但实际数据更不直观！），但为了安全起见，我需要更改数据。
因此，我需要确定：前一行中的记录位于哪个站点，以确定需要在何处获取它。
任何给定行上的站点（ >除了第一行function = 'new'）对应于项目接下来要去的地方。因此，简单地从同一行抓取该网站并不会告诉你我是从哪里来的。状态始终为 1 或 4。4 对应于已交付的位置，因此我不想在结果中包含这些记录。但它可能对获取接送地点有用。
对于这个数据表，我希望查询返回与状态为 1 的第一行上方的行相对应的站点。因此，这将是拉斯维加斯。
In SQL Compact Edition 3.5 , note that it is the Compact Edition I am talking about- Is there a way to limit the amount of rows to only 2? Something like using LIMIT or TOP. I really don't want to use anything with a SqlCEDataReader, or SqlCEResultSet. I want to do all the limiting in the query. Is this possible now? I have looked around and it doesn't seem so.
EDIT-
In response to Dave Swersky's request for data and using Min()/Max() on some columns as a means to get the top 2 lines, here is some sample (sterilized) data:
Line Site Function Status
1010 Las Vegas new 4
1020 DC send 1
1030 Portland copy 1
1040 SF copy 1
1050 Portland copy 1
1060 DC send 1
*There are more columns than this but these are the significant ones.
Sorry for the lack of intuitive data (but the actual data is even less intuitive!), but for security i need to change the data.
So- i need to determine: what site the record was at in the preceding line to determine where it needs to be picked up.
The site on any given line (except the first line with function = 'new') corresponds to where the item is going next. So simply grabbing that site off the same line wont tell me where it came from. The status will always be 1 or 4. The 4 corresponds to a where it has been delivered already and so i dont want to include those records in the result. But it might be useful in getting the pickup site.
For this table of data i want the query to return the site corresponding to the line just above the first line with status 1. So- for this it would be Las Vegas.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Sql CE 3.5支持TOP表达式：
http://technet.microsoft.com/en-us/library/bb686896.aspx
Sql CE 3.5 supports the TOP expression:
http://technet.microsoft.com/en-us/library/bb686896.aspx
这将取决于您的数据...如果您有保证顺序的 ID 或日期，您可以使用 MAX 和 NOT IN 子查询来返回有限数量的行。发布一些您的架构，我将尝试编写一个示例。
This will depend on your data... if you have guaranteed-sequential IDs or dates you could use MAX with NOT IN subqueries to return a limited number of rows. Post some of your schema and I'll try to cook up an example.
根据您需要执行的操作，您也许能够使用 DataReader 执行完整查询，并且仅调用其
.Read()方法两次。Depending on what you need to do, you might be able to execute the full query using a DataReader, and only call it's
.Read()method twice.我从未在查询本身中找到答案。
这是用于移动应用程序的还是桌面上的 SQL CE？
我真的没有直接的答案给你，但会分享我在自己的项目中观察到的内容......
我在桌面应用程序中使用 SQL CE 的经验是，在某些情况下，使用应用程序代码进行过滤会更高效。因此，如果您发现自己编写了一个复杂的查询，您可能还需要考虑在 data reader.Read() 循环内的应用程序代码中编写过滤器并比较结果。我发现这种情况会将需要几秒的操作时间缩短到亚秒级。
我知道这不是您想要做的，但我发现 SQL CE 要求您的优化与针对服务器运行时的优化略有不同。无需担心网络延迟，因此不会出现常见的担忧。
I never found an answer in the query itself.
Is this for a mobile app or is this SQL CE on a desktop?
I don't really have a direct answer for you, but will share what I've observed on my own projects...
My experience in using SQL CE in desktop apps is that there are situations where it's more performant to use your application code to do the filtering. So, if you find yourself writing a convoluted query, you may want to also look at writing the filters in your app code inside of data reader.Read() loop and compare results. I've found cases where this cut operations that were taking a couple of seconds down to sub-second.
I know it's not what you want to do, but I've found that SQL CE requires you optimize things a little differently from when you're running against server. There's no network latency to worry about, so the normal kinds of worries don't manifest.