订单使用操作并且是数字 [英] Order By using operation and is numeric
问题描述
创建 表 #test(Tat varchar ( 20 ))
insert into #test(Tat)
选择 ' > 23'
union all
选择 ' 0-3 '
union all
选择 ' 11-15'
union all
选择 ' 6-23'
union all
选择 ' 4-7'
union all
选择 ' 8-10'
select * 来自 #test 订单 按 TAT
在此查询中不使用订单。
我需要输出
TAT
0-3
4-7
6-23
8-10
问题是你正在使用字符串。这意味着ORDER BY将使用字符串比较 - 并且通过比较字符起作用,并且第一个差异控制整个比较,后续字符甚至没有被查看。
所以排序顺序为:< br $> b $ bTat
> 23
0-3
11-15
4-7
6- 23
8-10
如果没有非常复杂的查询,你可以做很多事情。
为什么不使用两列?
创建 表 #test(mn int ,mx int )
插入 进入 #test VALUES ( 23 , 999999 )
insert into #test VALUES ( 0 , 3 )
insert < span class =code-keyword> into #test VALUES ( 11 , 15 )
插入 进入 #test VALUES ( 6 , 23 )
插入 进入 #test VALUES ( 4 , 7 )
insert 进入 #test VALUES ( 8 , 10 )
选择 CAST(mn AS NVARCHAR ( 10 ))+ ' - ' + CAST(mx AS NVARCHAR ( 10 )) AS Tat 来自 #test 订单 按 mn
继续解决方案1(我正在打字和OG比我快!!)
我实际上包含了复杂的查询,以显示它是多么尴尬所以我将继续发布这个解决方案纯粹为了突出OG在解决方案1中的建议实际上是多么优雅。
你的问题是订单是由字段中的字符决定的而不是它们的(明显的)数值这就是为什么'< 23'首先出现在输出中> 23
0-3
11-15
4-7
6-23
8-10为什么'11 -15'出现在'4-7'之前('11'对于字符串小于'4',因为它以'1'开头)
您需要某种方法来确定此列的排序顺序。乍一看只是在每行中查找连字符' - '并将其转换为整数可能会这样做(您的预期结果意味着您不希望在结果中看到'> 23',但我会来回到那个)。所以这样的事情会起作用......
选择 Tat, CASE WHEN PATINDEX(' % - %',Tat)> 0 那么 cast(SUBSTRING(Tat, 0 ,PATINDEX(' % - %',Tat)) AS INT ) ELSE -1 END
来自 #test
WHERE PATINDEX( % - %',Tat)> 0 AND cast(SUBSTRING(Tat, 0 ,PATINDEX(' % - %',Tat)) AS INT )< = 8
ORDER BY 2 , 1
那里有很多重复,并不完全清楚发生了什么,所以我实际上会使用< span class =code-keyword> with CTE(Tat,SortOrder) AS
(
选择 Tat,
CASE WHEN PATINDEX(' % - %',Tat)> 0
那么 cast(SUBSTRING(Tat, 0 ,PATINDEX(' % - %',Tat)) AS INT )
ELSE -1 END AS SortOrder
来自 #test
)
SELECT Tat FROM CTE
WHERE SortOrder< = 8 和 SortOrder> = 0
ORDER BY SortOrder如果您希望'> 23'出现在正确的地方然后你可以这样做
CTE(Tat,So rtOrder) AS
(
选择 Tat, CASE WHEN PATINDEX(' % - %',Tat)> 0 那么 cast(SUBSTRING(Tat, 0 ,PATINDEX(' % - %',Tat)) AS INT )
WHEN PATINDEX(' %>%',Tat)> 0 那么 cast(SUBSTRING(Tat,PATINDEX(' %>%',Tat)+ 1 ,LEN(Tat)) AS INT )
ELSE -1 < span class =code-keyword> END AS SortOrder
来自# test
)
SELECT Tat FROM CTE
ORDER BY SortOrder导致
Tat
0-3
4-7
6-23
8-10
11-15
> 23
正如你所看到的,这变得非常混乱。当然,如果这些值是某些东西的站立数据,并且没有很多值,那么在创建表格时包含排序顺序可能同样容易。创建 表 #test2(Tat varchar ( 20 ),SortOrder int )
插入 进入#test2(Tat,SortOrder)
select ' > 23', 60
union all
选择 ' 0-3', 10
union all
选择 ' 11-15', 50
union 全部
选择 ' 6-23', 30
union all
选择 ' 4-7', 20
union 全部
选择 ' 8-10', 40
从#test2 订单 by SortOrder请注意我在SortOrder中留下的大空白,以防我想稍后添加内容或者您可以根据CTE更新它我用上面的
和CTE(Tat,SortOrder)AS
(
选择Tat,CASE WHEN PATINDEX('% - %',Tat)> 0 THEN cast(SUBSTRING(Tat,0,PATINDEX('% - %',Tat))AS INT)
WHEN PATINDEX('%>%',Tat)> 0 THEN cast(SUBSTRING(Tat,PATINDEX('%>%',Tat)+ 1,LEN(Tat))AS INT)
ELSE -1 END AS SortOrder
来自#test2
)
UPDATE#test2
SET SortOrder = CTE.SortOrder
FROM#test2
INNER JOIN CTE ON#test2.Tat = CTE.Tat
create table #test(Tat varchar(20))
insert into #test (Tat)
select '>23'
union all
select '0-3'
union all
select '11-15'
union all
select '6-23'
union all
select '4-7'
union all
select '8-10'
select * from #test order by TAT
Order by not using in this query.
I Need Output
TAT
0-3
4-7
6-23
8-10
The problem is that you are using strings. Which means that ORDER BY will use a string comparison - and that works by comparing characters and the first difference controls the whole compare, subsequent characters aren't even looked at.
so the sort order is:
Tat >23 0-3 11-15 4-7 6-23 8-10
And there isn't a lot you can do about that without a very complex query.
Why not use two columns?
create table #test(mn int, mx int) insert into #test VALUES (23, 999999) insert into #test VALUES (0, 3) insert into #test VALUES (11, 15) insert into #test VALUES (6, 23) insert into #test VALUES (4, 7) insert into #test VALUES (8, 10) select CAST(mn AS NVARCHAR(10)) + '-' + CAST(mx AS NVARCHAR(10)) AS Tat from #test order by mn
Further to solution 1 (I was typing away and OG is quicker than me!!)
I actually included the complex query to show how awkward it is so I will continue to post this "solution" purely to highlight how elegant OG's suggestion in Solution 1 actually is.
Your problem is that the order is determined by the characters in the field not their (apparent) numeric value which is why '<23' appears first in the output>23 0-3 11-15 4-7 6-23 8-10and why '11-15' appears before '4-7' ('11' is less than '4' for strings as it begins with '1')
You need some way of determining what the sort order should be for this column. At first glance just looking for a hyphen '-' in each row and converting that to an integer might do it (your expected results imply that you don't want to see '>23' in your results, but I'll come back to that). So something like this would work...
select Tat, CASE WHEN PATINDEX('%-%',Tat) > 0 THEN cast(SUBSTRING(Tat, 0, PATINDEX('%-%', Tat)) AS INT) ELSE -1 END from #test WHERE PATINDEX('%-%',Tat) > 0 AND cast(SUBSTRING(Tat, 0, PATINDEX('%-%', Tat)) AS INT) <= 8 ORDER BY 2,1
There's a lot of repetition in there and it's not entirely clear what's going on, so I would actually usewith CTE(Tat, SortOrder) AS ( select Tat, CASE WHEN PATINDEX('%-%',Tat) > 0 THEN cast(SUBSTRING(Tat, 0, PATINDEX('%-%', Tat)) AS INT) ELSE -1 END AS SortOrder from #test ) SELECT Tat FROM CTE WHERE SortOrder <= 8 and SortOrder >= 0 ORDER BY SortOrderIf you want '>23' to appear in the correct place then you could do this instead
with CTE(Tat, SortOrder) AS ( select Tat, CASE WHEN PATINDEX('%-%',Tat) > 0 THEN cast(SUBSTRING(Tat, 0, PATINDEX('%-%', Tat)) AS INT) WHEN PATINDEX('%>%', Tat) > 0 THEN cast(SUBSTRING(Tat, PATINDEX('%>%', Tat) + 1, LEN(Tat)) AS INT) ELSE -1 END AS SortOrder from #test ) SELECT Tat FROM CTE ORDER BY SortOrderwhich results in
Tat 0-3 4-7 6-23 8-10 11-15 >23
As you can see, this is getting quite messy. Of course if these values are "standing" data for something, and there are not many values, then it might be just as easy to include the sort order when you are creating the table e.g.create table #test2(Tat varchar(20), SortOrder int) insert into #test2 (Tat, SortOrder) select '>23', 60 union all select '0-3', 10 union all select '11-15',50 union all select '6-23',30 union all select '4-7',20 union all select '8-10',40 select Tat from #test2 order by SortOrderNote the big gaps I left in the SortOrder in case I want to add something later or you can update it based on the CTE I used above
with CTE(Tat, SortOrder) AS ( select Tat, CASE WHEN PATINDEX('%-%',Tat) > 0 THEN cast(SUBSTRING(Tat, 0, PATINDEX('%-%', Tat)) AS INT) WHEN PATINDEX('%>%', Tat) > 0 THEN cast(SUBSTRING(Tat, PATINDEX('%>%', Tat) + 1, LEN(Tat)) AS INT) ELSE -1 END AS SortOrder from #test2 ) UPDATE #test2 SET SortOrder = CTE.SortOrder FROM #test2 INNER JOIN CTE ON #test2.Tat=CTE.Tat
这篇关于订单使用操作并且是数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!