34,593
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([HoteilId] nvarchar(21),[HotelType] int)
Insert #T
select N'A',2 union all
select N'A',1 union all
select N'B',2
Go
--测试数据结束
SELECT t.HoteilId ,
t.HotelType
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY HoteilId ORDER BY HotelType) AS rn
FROM #T
) t
WHERE rn=1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([HoteilId] nvarchar(21),[HotelType] int)
Insert #T
select N'A',2 union all
select N'A',1 union all
select N'B',2
Go
--测试数据结束
SELECT t.HoteilId ,
t.HotelType
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY HoteilId ORDER BY CASE
WHEN HotelType = 1
THEN 1
ELSE -1
END DESC ) AS rn
FROM #T
) t
WHERE rn=1