gpt4 book ai didi

c# - 按照 IN 的顺序对 SQL Query 进行排序

转载 作者:太空狗 更新时间:2023-10-30 00:52:50 24 4
gpt4 key购买 nike

我正在写一个查询

SELECT * FROM EMPLOYEES WHERE EMP_ID IN (10,5,3,9,2,8,6)

我希望结果应该按以下顺序

Emp_id   Emp_Name
10 John
5 Joe
3 Tippu
9 Rich
2 Chad
8 Chris
6 Rose

基本上与 IN 子句的顺序相同。有可能这样做吗?请告诉我。

PS:我可以在 SQL 中执行此操作,也可以在获得结果集后执行此操作,如果我可以使用 LINQ 或其他在前端选项中进行排序的选项也对我有用(我在前端数组中有 Emp ID)

谢谢

最佳答案

字符串评论答案;这将给出与原始答案相同的结果,但匹配字符串:

  string orgList = "John,Joe,Tippu,Rich,Chad,Chris,Rose";
List<string> orderArray = new List<string>(orgList.Split(",".ToCharArray()));

// the linq to do the ordering
var result = ourList.OrderBy(e => {
int loc = orderArray.IndexOf(e.Name);
return loc == -1? int.MaxValue: loc;
});

作为旁注,原来的答案可能用这两行会更好:

  string orgList = "10,5,3,9,2,8,6";
List<int> orderArray = new List<int>(orgList.Split(",".ToCharArray()));

而不是使用整型常量。使用上面的代码将按任意逗号分隔的整数列表排序。

下面 Linq 中的解决方案给出了这个结果:

enter image description here

void Main()
{
// some test data
List<Person> ourList = new List<Person>()
{
new Person() { ID = 1, Name = "Arron" },
new Person() { ID = 2, Name = "Chad" },
new Person() { ID = 3, Name = "Tippu" },
new Person() { ID = 4, Name = "Hogan" },
new Person() { ID = 5, Name = "Joe" },
new Person() { ID = 6, Name = "Rose" },
new Person() { ID = 7, Name = "Bernard" },
new Person() { ID = 8, Name = "Chris" },
new Person() { ID = 9, Name = "Rich" },
new Person() { ID = 10, Name = "John" }
};

// what we will use to order
List<int> orderArray = new List<int>(){10,5,3,9,2,8,6};

// the linq to do the ordering
var result = ourList.OrderBy(e => {
int loc = orderArray.IndexOf(e.ID);
return loc == -1? int.MaxValue: loc;
});

// good way to test using linqpad (get it at linqpad.com
result.Dump();
}

// test class so we have some thing to order
public class Person
{
public int ID { get; set; }
public string Name { get; set; }
}

原始错误的 SQL 答案

WITH makeMyOrder
(
SELECT 10 as ID, 1 as Ord
UNION ALL
SELECT 5 as ID, 2 as Ord
UNION ALL
SELECT 3 as ID, 3 as Ord
UNION ALL
SELECT 9 as ID, 4 as Ord
UNION ALL
SELECT 2 as ID, 5 as Ord
UNION ALL
SELECT 8 as ID, 6 as Ord
UNION ALL
SELECT 6 as ID, 7 as Ord
),
SELECT *
FROM EMPLOYEES E
JOIN makeMyOrder O ON E.EMP_ID = O.ID
ORDER BY O.Ord

关于c# - 按照 IN 的顺序对 SQL Query 进行排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19531774/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com