在 SQL CE（精简版）版本 3.5 中对字母数字字段进行排序

发布于 2024-10-04 17:46:23 字数 535 浏览 0 评论 0原文

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

我想要类似于以下查询的内容，该查询在 SQL Server 中有效：

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

上面的查询似乎不起作用，因为 CE 中不支持 [] 范围。另一个适用于 SQL Server 但不适用于 CE 的解决方案，因为不支持“IsNumber()”，如下所示：

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

原文

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

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.

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

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

分享到QQ

分享到微博

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

发布评论

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

南七夏 2024-10-11 17:46:23

好吧，这个解决方案很丑陋，不适合胆小的人。我还没有在 SQL CE 上进行过测试，但它只使用基本的 t-sql，所以应该没问题。您必须创建一个 tally 表（只需运行他的第一段代码，如果您不想阅读它。它使用 tempdb，因此您需要更改它），以及一个用于保存字母表中每个字母的表（由于缺乏模式匹配功能）。创建计数表后（您不必像示例所示一直到 11000），运行这些表，您将看到所需的排序行为

创建字母表（用于演示目的的临时表）

select *
into #alphatable
from
(

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

：树表（用于演示目的的临时表）：

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 表中没有行的行是数字。

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

The solution:

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

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.

回复收藏 0 原文