SQL查询没有order by子句，如何显示查询结果的顺序？

Question

我正在SQL Server中编写一个简单的查询：

Select 
    EmpId, EmpName, Sal 
from 
    Emp 
where  
    EmpId in (10,9,5,7,3,8);

我希望能按照给定的顺序输出结果，即10,9,5,7,3,8

实际上，无论我提供什么结果，都将按照给定的顺序显示，而不是按升序或降序排序。

我该如何做呢？请帮忙。

- Shubhadeep Chattopadhyay

---

你从这个查询中得到了什么操作数？ - Ameya Deshpande

“IN”子句无法提供更改数据顺序的方法。 - Maciej Los

@AmeyDeshpande 目前它正在按升序显示。像3、5、7、8、9、10这样。 - Shubhadeep Chattopadhyay

@FelixPamittan，你能给我一个做这件事的例子吗？ - Shubhadeep Chattopadhyay

@MaciejLos 那么我怎样才能按照我的要求得到结果呢？有没有其他的方法可以做到这一点？ - Shubhadeep Chattopadhyay

Answer 1

无法在本地完成此操作。 尝试：

SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (10,9,5,7,3,8)
ORDER BY CASE EmpId
    WHEN 10 THEN 1
    WHEN 9 THEN 2
    WHEN 5 THEN 3
    WHEN 7 THEN 4
    WHEN 3 THEN 5
    WHEN 8 THEN 6
    ELSE 7
END;

- rabudde

Answer 2

您可以使用表变量来传递输入参数。必须按照所需的顺序将记录插入到此表变量中。

Declare @empids table(id int identity(1,1),empid int)
insert into @empids values(10),(9),(5),(7),(3),(8)

Select e.EmpId,e.empname,e.sal from Emp e
join @empids t on  e.EmpId = t.empid 
order by t.id

试试这个。

- Jasqlg

Answer 3

您可以使用CHARINDEX函数的一种奇怪的方式: 在逗号分隔的列表中搜索id，并按位置排序结果。

例如，考虑此列表10,9,5,7,3,8...子字符串10出现在第1个位置，而9出现在第4个位置。只需按子字符串位置排序即可。

CREATE TABLE Emp
    (EmpId int, EmpName varchar(100), Sal int)
;

INSERT INTO Emp
    (EmpId, EmpName, Sal)
VALUES
    (1, 'John', NULL),
    (2, 'Jane', NULL),
    (3, 'Smith', NULL),
    (4, 'Doe', NULL),
    (5, 'Ben', NULL),
    (6, 'Steve', NULL),
    (7, 'Andrew', NULL),
    (8, 'Simon', NULL),
    (9, 'Jack', NULL),
    (10, 'Allen', NULL)
;

SELECT
    EmpId, EmpName, Sal
FROM
    Emp
WHERE
    EmpId in (10,9,5,7,3,8)
ORDER BY
    CHARINDEX(CONCAT(',', EmpId, ','), CONCAT(',', '10,9,5,7,3,8', ','))
;

结果：

EmpId | EmpName | Sal
------+---------+-----
10    | Allen   | NULL
9     | Jack    | NULL
5     | Ben     | NULL
7     | Andrew  | NULL
3     | Smith   | NULL
8     | Simon   | NULL

- Salman A

---

虽然这绝对是一种hack，但这正是这种问题所应该采用的方法 - 我很乐意将其用于ad-hoc查询！ - Andrew Hanlon

Answer 4

如果您的列表是逗号分隔字符串，您可以动态地执行此操作。首先，您必须有一个分割函数。这里是由Jeff Moden编写的DelimitedSplit8k：

CREATE FUNCTION [dbo].[DelimitedSplit8K](
    @pString VARCHAR(8000), @pDelimiter CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,E4(N) AS (SELECT 1 FROM E2 a, E2 b)
,cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS(
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter
),
cteLen(N1, L1) AS(
SELECT 
    s.N1,
    ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1),0) - s.N1, 8000)
FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l

然后，您将empId列表声明为csv字符串并使用拆分器：

DECLARE @empIds VARCHAR(MAX) = '10,9,5,7,3,8';

SELECT e.EmpId, e.EmpName, e.Sal 
FROM Emp e
INNER JOIN dbo.DelimitedSplit8K(@empIds, ',') s
    ON s.Item = l.EmpId
ORDER BY s.ItemNumber

- Felix Pamittan

Answer 5

以下查询将为您提供精确结果（不包括 order by）：

SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (10)
union all
SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (9)
union all
SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (5)
union all
SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (7)
union all
SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (3)
union all
SELECT EmpId,EmpName,Sal
FROM Emp
WHERE EmpId IN (8)

- Biswabid

Answer 6

如果Ids是作为输入参数传递给存储过程的变量，您可以使用CTE对其进行拆分。

--@inputIDs VARCHAR(300) => '10,9,5,7,3,8'

;WITH MyIds AS
(
    SELECT 1 AS Position, CONVERT(INT, LEFT(@inputIds, CHARINDEX(',', @inputIDs)-1)) AS MyId, 
        RIGHT(@inputIds, LEN(@inputIds) - CHARINDEX(',', @inputIDs)) AS Remainder
    WHERE CHARINDEX(',', @inputIDs)>0
    UNION ALL
    SELECT Position +1  AS Position, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyId, 
        RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
    FROM MyIds 
    WHERE CHARINDEX(',', Remainder)>0
    UNION ALL
    SELECT Position +1 AS Position, CONVERT(INT, Remainder) AS MyId, 
        NULL AS Remainder
    FROM MyIds 
    WHERE CHARINDEX(',', Remainder)=0
)
SELECT e.EmpId, e.EmpName, e.Sal
FROM Emp AS e INNER JOIN MyIds AS a ON e.EmpId = a.MyId
ORDER BY a.Position

- Maciej Los

Answer 7

最简单的方法可能是使用表值构造函数作为包含ID和排序值的表达式，然后按照这个值进行排序：

Select 
    Emp.EmpId, Emp.EmpName, Emp.Sal 
from 
        Emp
    Inner Join
        (
            Values
                (10, 1),
                (9, 2),
                (5, 3),
                (7, 4),
                (3, 5),
                (8, 6)
        )
        EmpIds (EmpId, Sort)
            On
                Emp.EmpId = EmpIds.EmpId
Order By
    EmpIds.Sort Asc

- SQLDiver