ASP.NET 中的 SQL - Order By 仅错误地排序一种类型

发布于 2024-07-12 23:39:04 字数 764 浏览 4 评论 0原文

我正在开发一个 ASP.NET 应用程序，该应用程序应该输出所选州际公路的探测器数据，并按英里标记排序。我们确定最好的方法是根据经度（西和东）或纬度（北和南）对其进行排序，具体取决于它的移动方向。这是填充它的查询。

SELECT [ShortWebID], [AvgSpeed], [update_time], [WebName] FROM [vwAverageSpeed] 

WHERE (([WebName] LIKE '%' + @WebName + '%') AND ([update_time] > @update_time)) 

ORDER BY CASE @WebName WHEN '%EB%'THEN [Longitude] WHEN '%WB%' THEN [Longitude] WHEN '%NB%' THEN [Latitude] WHEN '%SB%' THEN [Latitude] END

WebName 视图具有不同的列表，例如“I-64 WB at MP 3.1”。他们有权访问的 ListItems 被列为“I-64”。

有 4 条不同的州际公路，除了 I-55/70 之外，其他所有州际公路都运行良好。选择该选项后，列表不会按纬度或经度排序，而是仅按它们在表中的顺序排序。 I-55/70 是唯一一个带有 / 的。这会影响事情吗？

先感谢您。

编辑：这些在列表项中列为“I-55/70 WB”等，其样式与上面的示例相同。很抱歉造成混乱。

编辑2：这就成功了。谢谢！

原文

I'm working on an ASP.NET application which is supposed to output detector data for a chosen interstate, sorted by mile marker. We determined the best way to do this would be sorting it by longitude (West and East) or latitude(North and South) depending on which direction it goes. Here's the query that populates it.

SELECT [ShortWebID], [AvgSpeed], [update_time], [WebName] FROM [vwAverageSpeed] 

WHERE (([WebName] LIKE '%' + @WebName + '%') AND ([update_time] > @update_time)) 

ORDER BY CASE @WebName WHEN '%EB%'THEN [Longitude] WHEN '%WB%' THEN [Longitude] WHEN '%NB%' THEN [Latitude] WHEN '%SB%' THEN [Latitude] END

The WebName view has different listings such as 'I-64 WB at MP 3.1'.
The ListItems they have access to are listed just as 'I-64'.

There's 4 different interstates, and it's working fine for everything but one, I-55/70. When that option is chosen, the list is not sorted by the latitude or longitude, but instead just by the order that they're in the table. I-55/70 is the only one with a / in it. Would this affect things?

Thank you in advance.

Edit: These are listed in the list item as 'I-55/70 WB' and so on in the same style as the example above. Sorry about the confusion.

Edit2: That did the trick. Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

執念 2024-07-19 23:39:17

在我看来，您的 ORDER BY 子句在任何情况下都不应该起作用，因为“CASE @WebName WHEN '%EB%'”等不应评估为 true。您使用的 case 语句会进行等价比较，但通配符运算符（“%”）仅用于 LIKE。试试这个：

ORDER BY CASE 
WHEN @WebName LIKE '%EB%' THEN [Longitude] 
WHEN @WebName LIKE '%WB%' THEN [Longitude] 
WHEN @WebName LIKE '%NB%' THEN [Latitude] 
WHEN @WebName LIKE '%SB%' THEN [Latitude] 
END

斜杠不应该影响任何东西。

It seems to me your ORDER BY clause should not work in any cases because "CASE @WebName WHEN '%EB%'" etc. should not evaluate to true. The case statement as you are using it does an equivalence comparison but the wildcard operators ("%") are only used for LIKE. Try this instead:

ORDER BY CASE 
WHEN @WebName LIKE '%EB%' THEN [Longitude] 
WHEN @WebName LIKE '%WB%' THEN [Longitude] 
WHEN @WebName LIKE '%NB%' THEN [Latitude] 
WHEN @WebName LIKE '%SB%' THEN [Latitude] 
END

The slash should not affect anything.

回复收藏 0 原文