27,582
社区成员




--上面同1货号如果是多行,取属性为1的,同1货号是单行取这一行数据
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
create table #Tmp_Data(
Item_No varchar(10),
Price decimal(13,2),
Property int)
INSERT INTO #Tmp_Data
Select '001',1,1 union
Select '001',2,0 union
Select '002',1.5,0 union
Select '003',3,0
Select Item_No as '货号',Price as '价格', Property as '属性'
From (
Select *,Row_No=Row_Number() Over (Partition By Item_No order By Property Desc)
From #Tmp_Data
) a
Where Row_No=1
(4 row(s) affected)
货号 价格 属性
---------- --------------------------------------- -----------
001 1.00 1
002 1.50 0
003 3.00 0
(3 row(s) affected)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([货号] nvarchar(23),[价格1] int,[价格2] int,[价格3] int)
Insert #T
select N'001',3,5,4 union all
select N'002',1,2,3
Go
--测试数据结束
SELECT * ,
CASE WHEN 价格1 < 价格2
AND 价格1 < 价格3 THEN '价格1最低'
WHEN 价格2 < 价格1
AND 价格2 < 价格3 THEN '价格2最低'
WHEN 价格3 < 价格1
AND 价格3 < 价格2 THEN '价格3最低'
ELSE '价格1最低'
END AS 最低价格列
FROM #T
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([货号] nvarchar(23),[价格] decimal(18,7),[属性] int)
Insert #T
select N'001',1,1 union all
select N'001',2,0 union all
select N'002',1.5,0 union all
select N'003',3,0
Go
--测试数据结束
;WITH cte AS (
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY 货号 ORDER BY CASE WHEN 属性 = 1 THEN 1
ELSE 0
END DESC ) rn
FROM #T
)
SELECT 货号 ,
价格 ,
属性
FROM cte
WHERE rn = 1