SQL CE(精简版)版本3.5中的字母数字字段排序 [英] Sorting Alphanumeric field in SQL CE (Compact Edition) version 3.5

问题描述

在SQL CE(精简版)3.5版中排序字母数字字段

Sorting Alphanumeric field in SQL CE (Compact Edition) version 3.5

TreeNumber是一个nvarchar字段，其中包含数字和值的字符串.我想对这些记录进行排序，以使包含字母字符的记录位于顶部，其余的按数字顺序进行排序.

TreeNumber is a nvarchar field with a mix of numbers and strings for the values. I want to sort these records so that the records that contain alpha characters are at the top and the rest are sorted in numeric order.

我想要类似于以下在SQL Server中运行的查询:

I want something similar to the following query which works in SQL Server:

SELECT * FROM Tree
ORDER BY 
    (CASE WHEN TreeNumber LIKE '%[a-z]%' THEN 0 ELSE TreeNumber END), TreeNumber

上面的查询似乎不起作用，因为CE不支持[]范围.下面是另一种与SQL Server兼容但由于不支持"IsNumber()"而无法在CE中使用的解决方案:

The above query doesn't seem to work because the [] range is not supported in CE. Another solution which works with SQL Server but doesn’t work in CE because "IsNumber()" is not supported is below:

SELECT * FROM Tree 
ORDER BY 
    (CASE IsNumeric(TreeNumber) WHEN 0 THEN 0 ELSE TreeNumber END), TreeNumber

推荐答案

好的，这种解决方案很丑陋，而不是胆小的人.我尚未在SQL CE上进行测试，但它仅使用基本的t-sql，因此应该可以.您将必须创建一个统计表(只需运行他的第一段代码，如果您不想阅读它，它使用tempdb，因此您需要更改它)和一个表来保存字母表中的每个字母(由于缺少模式匹配功能).创建理货表格后(不必像示例所示那样一直走到11000)，运行它们，您将看到所需的排序行为

Ok, this solution is ugly, and not for the faint of heart. I haven't tested on SQL CE, but it only uses basic t-sql so it should be ok. You will have to create a tally table (just run his first block of code, if you don't want to read it. It uses the tempdb so you'll want to change that), and a table to hold each letter of the alphabet (due to lack of pattern matching functions). After creating the tally table (you don't have to go all the way to 11000 as the example shows), run these, and you'll see the sorting behavior you want

创建字母表(用于演示的临时表):

Create the alphabet table (temp for demo purposes):

select *
into #alphatable
from
(

select 'A' as alpha union all
select 'B' union all
select 'C' union all
select 'D'
--etc. etc.
) x

创建树表(出于演示目的，使用临时表):

Create a tree table (temp for demo purposes):

select *
into #tree
from
(

select 'aagew' as TreeNumber union all
select '3' union all
select 'bsfreww' union all
select '1' union all
select 'xcaswf' 
) x

解决方案:

select TreeNumber
from
(
select t.*, tr.*, substring(TreeNumber, case when N >  len(TreeNumber) then len(TreeNumber) else N end, 1) as singleChar
from tally t
cross join #tree tr
where t.N < (select max(len(TreeNumber)) from #tree)

) z
left join
#alphatable a
on z.singlechar = a.alpha
group by TreeNumber

order by case when max(alpha) is not null then 0 else TreeNumber end 

这基本上是Moden描述为逐步浏览字符"的技术，然后将每个字符连接到alpha表上. alpha表中没有行的行是数字.

This is basically the technique that Moden describes as "Stepping through the characters", then each character is joined on the alpha table. Rows with no row in the alpha table are numeric.

这篇关于SQL CE(精简版)版本3.5中的字母数字字段排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！