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.
我正在研究一个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
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'.
WebName视图具有不同的列表,例如“I-64 WB at MP 3.1”。他们有权访问的ListItem被列为'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?
有4个不同的州际公路,除了一个,I-55/70之外,它的工作正常。选择该选项时,列表不按纬度或经度排序,而是按照它们在表中的顺序排序。 I-55/70是唯一一个带有/的人。这会影响事情吗?
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.
编辑:这些在列表项中列为“I-55/70 WB”,依此类推,其风格与上面的示例相同。对此感到抱歉。
Edit2: That did the trick. Thanks!
Edit2:这就是诀窍。谢谢!
2 个解决方案
#1
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 @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
The slash should not affect anything.
斜线不应该影响任何东西。
#2
I-55/70 corresponds to neither of '%EB%, '%WB%', '%NB%' or '%SB%'.
I-55/70既不对应'%EB%','%WB%','%NB%'或'%SB%'。
Your ORDER BY clause is always NULL in that case.
在这种情况下,您的ORDER BY子句始终为NULL。
What field do you want to sort on when the WebName is I-55/70?
当WebName为I-55/70时,您想要对哪个字段进行排序?
#1
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 @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
The slash should not affect anything.
斜线不应该影响任何东西。
#2
I-55/70 corresponds to neither of '%EB%, '%WB%', '%NB%' or '%SB%'.
I-55/70既不对应'%EB%','%WB%','%NB%'或'%SB%'。
Your ORDER BY clause is always NULL in that case.
在这种情况下,您的ORDER BY子句始终为NULL。
What field do you want to sort on when the WebName is I-55/70?
当WebName为I-55/70时,您想要对哪个字段进行排序?