排列同时包含数值和连字符的列
排列同时包含数值和连字符的列
提问于 2013-06-21 13:53:45
我正在尝试排列一个具有如下值的列：
6-3
11-1
3
8-5
5
6-2
1
7
11-4
8-12
2我希望他们是这样安排的：
1
2
3
5
7
6-2
6-3
8-5
8-12
11-1
11-4我现在有这样的查询：
select column_name from database_name.dbo.table_name
order by
(case when (BOX_NO not like '%-%')
then -1 when (BOX_NO like '%-%')
then cast(SUBSTRING(reverse(BOX_NO), LEN(BOX_NO)-(CHARINDEX('-', reverse(BOX_NO))-2), 0) as int) end),
(case when (column_name not like '%-%')
then cast(column_name as int)
when (column_name like '%-%')
then cast(SUBSTRING(column_name, LEN(column_name)-(CHARINDEX('-', reverse(column_name))-2), 8000) as int) end)我快要发疯了。如果有人能帮我那就太好了。谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-06-21 14:09:16
漂亮的雏菊！现在一切都好了。第一个案例是错误的。不应该有第二次逆转。搜索应该从8000开始，而不是0。我的错！
(case when (column_name not like '%-%') then 0 when (column_name like '%-%') then cast(SUBSTRING(reverse(column_name), LEN(column_name)-(CHARINDEX('-', column_name)-2), 8000) as int) end),Stack Overflow用户
发布于 2013-06-21 14:50:45
declare @test table ( value varchar(10))
insert @test values ('6-3'),
('11-1'),
('3'),
('8-5'),
('5'),
('6-2'),
('1'),
('7'),
('11-4'),
('8-12'),
('2')
select
T2.value,
case when CHARINDEX('-', T1.value) = 0 then cast(T1.value as int) else cast(LEFT(T1.value, CHARINDEX('-', T1.value) - 1) as int) end,
case when CHARINDEX('-', T1.value) = 0 then 0 else cast(substring(T1.value, CHARINDEX('-', T1.value) + 1, 10) as int) end
from @test T1, @test T2
where T1.value = T2.value
order by 2, 3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17228536
复制相关文章
相似问题
腾讯云开发者
