按值列表排序查询结果 [英] Ordering query result by list of values

问题描述

我正在处理将值列表作为参数传递的 sql 查询，例如

I'm working on a sql query that is passed a list of values as a parameter, like

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)

此列表由数据库中使用的关系构成，必须按此顺序保存.

This list is constructed of relations used througout the database, and must be kept in this order.

我想按此列表对结果进行排序.我只需要第一个结果，但在这种情况下它可能是 GroupId 7 的结果.

I would like to order the results by this list. I only need the first result, but it could be the one with GroupId 7 in this case.

我无法查询

order by (24,12,7,14,65).indexOf(GroupId)

有人知道怎么做吗?

其他信息:
建立连接工作并在 mssql 查询编辑器中运行它，但是...

Additional info:
Building a join works and running it in the mssql query editor, but...

由于将查询发送到 mssql 的软件的限制，我必须将它作为 1 个参数传递给某个内部查询构建器，因此24,12,7,14,65".而且我不知道这个列表中会有多少个数字，可能是 2，可能是 20.

Due to limitiations of the software sending the query to mssql, I have to pass it to some internal query builder as 1 parameter, thus "24,12,7,14,65". And I don't know upfront how many numbers there will be in this list, could be 2, could be 20.

推荐答案

使用带有标识列的表变量或临时表，输入您的值并加入其中，例如

Use a table variable or temporary table with an identity column, feed in your values and join to that, e.g.

declare @rank table (
    ordering int identity(1,1)
    , number int    
    )

insert into @rank values (24)
insert into @rank values (12)
insert into @rank values (7)
insert into @rank values (14)
insert into @rank values (65)

select  pg.*
from    ProductGroups pg
left outer join 
    @rank r
on  pg.GroupId = r.number 
order by 
    r.ordering

这篇关于按值列表排序查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！