我正在SQL Server中编写一个简单的查询:
Select
EmpId, EmpName, Sal
from
Emp
where
EmpId in (10,9,5,7,3,8);
我希望能按照给定的顺序输出结果,即10,9,5,7,3,8
实际上,无论我提供什么结果,都将按照给定的顺序显示,而不是按升序或降序排序。
我该如何做呢?请帮忙。
我正在SQL Server中编写一个简单的查询:
Select
EmpId, EmpName, Sal
from
Emp
where
EmpId in (10,9,5,7,3,8);
我希望能按照给定的顺序输出结果,即10,9,5,7,3,8
实际上,无论我提供什么结果,都将按照给定的顺序显示,而不是按升序或降序排序。
我该如何做呢?请帮忙。
无法在本地完成此操作。 尝试:
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;
您可以使用表变量来传递输入参数。必须按照所需的顺序将记录插入到此表变量中。
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
试试这个。
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
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
以下查询将为您提供精确结果(不包括 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)
如果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
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