c# – 按IN的顺序对SQL查询进行排序

我正在写一个查询SELECT * FROM EMPLOYEES WHERE EMP_ID IN (10,5,3,9,2,8,6)我希望结果应按以下顺序排列Emp_id Emp_Name10 John5 Joe3 Tippu9 Rich2 Chad8 Chris...

我正在写一个查询

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中的解决方案给出了以下结果：

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