簡化示例：
select top(1) team.points
from teams
where team.ID = '123' and team.season IN ('2021', '2015', '2010')
我一直在尋找，但找不到可靠的答案。我想使用該IN子句來獲取某些行，但我希望它從左到右作業。
在上面的示例中，我想要 2021 年的一行，然后是 2015 年，然后是 2010 年（假設沒有季節為空）。
有沒有辦法讓 IN 子句關心訂單？
編輯：所以通過評論，我可以說我的例子不好。我已經對其進行了編輯，希望能顯示出問題所在。我正在尋找一個結果，但我需要檢查 IN 子句 IN ORDER 中的值，并且我的欄位中的實際資料不是數字或容易排序的。在評論中，建議在 order by 子句中使用 case 陳述句，以使該欄位可以排序。我不知道您可以在 order by 子句中使用 case 陳述句。這似乎解決了我的問題。
uj5u.com熱心網友回復：
以下代碼演示了幾種過濾和排序資料的方法。Any 可以修改為回傳 just top (1) Points，但為了清楚起見，顯示了完整的輸出。
-- Sample data.
declare @Teams as Table ( Id VarChar(4), Season VarChar(4), Points VarChar(4) );
insert into @Teams ( Id, Season, Points ) values
( '1', '2010', 'VC' ), ( '12', '2010', 'CX' ), ( '123', '2010', 'XI' ),
( '1', '2015', 'VCI' ), ( '12', '2015', 'CXI' ), ( '123', '2015', 'XII' ),
( '1', '2021', 'VCII' ), ( '12', '2021', 'CXII' ), ( '123', '2021', 'XIII' );
select * from @Teams;
-- OP's original query without any explicit order.
select Id, Season, Points
from @Teams
where Id = '123' and Season in ( '2021', '2015', '2010' );
-- With order imposed by a case expression.
-- For each Season the case expression needs to be extended to supply an appropriate value.
select Id, Season, Points
from @Teams
where Id = '123' and Season in ( '2021', '2015', '2010' )
order by case Season
when '2021' then 1
when '2015' then 2
when '2010' then 3
else 42 end; -- Put unexpected values last.
-- With order and filtering by a values clause (equivalent to a separate table).
-- For each Season there needs to be an additional row in Seasons to specify both the
-- season and its order. This has the benefit of keeping the pairs of values together.
select T.Id, T.Season, T.Points
from @Teams as T inner join
( values ( '2021', 1 ), ( '2015', 2 ), ( '2010', 3 ) )
as Seasons( Season, SeasonOrder ) on T.Season = Seasons.Season
where T.Id = '123'
order by Seasons.SeasonOrder;
uj5u.com熱心網友回復：
由于沒有資料可以測驗，我不確定結果
試試這個：
DECLARE @YearList TABLE (ID int IDENTITY(1,1), Year int)
-- insert them in the desired order from left to right.
INSERT INTO @YearList VALUES ('2021'), ('2015'), ('2010');
SELECT
T.points
FROM teams T
JOIN @YearList Y ON Y.Year = T.season
WHERE T.ID = '123'
ORDER BY Y.ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/419563.html
標籤：