最快的方法判断最小值

[想飞的狼]

产品编号 价格A 价格B 价格C 价格D 价格E


价格为FLOAT,如何用最快的办法判断出价格A 价格B 价格C 价格D 价格E，中哪个价最低，字段名是什么，当价格为0的时候
不参加比较

[想飞的狼]

字段名是什么怎么加进去呢
这样的结果


产品编号 最低价 价格名称
1300120201 1 价格E
1300141201 2 价格D
1300260105 3 价格B
1300750501 3 价格E
1303192201 2 价格C
1303720901 1 价格A

[xman_78tom]

;with t as (
select [产品编号],[价格名],[价格],
row_number() over (partition by [产品编号] order by [价格]) rn
from tb unpivot ([价格] for [价格名] in ([价格A],[价格B],[价格C],[价格D],[价格E])) upvt
where [价格]<>0
)
select [产品编号],[价格名],[价格] from t where rn=1;

[想飞的狼]

谢谢各位高手回复，我用的是SQL2000，实在抱歉

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (产品编号 varchar(1),价格A int,价格B int,价格C int,价格D int,价格E int)
insert into #tb
select 'a',1,2,3,6,3 union all
select 'b',5,2,4,2,1 union all
select 'c',2,1,4,3,5


;with cte as
(
	select 产品编号,价格名,价格 
	from #tb
	unpivot 
	(
		价格 for 价格名 in(价格A,价格B,价格C,价格D,价格E)
	)t
)
select * from cte t1
where not exists(select * from cte where 产品编号=t1.产品编号 and 价格<t1.价格)



产品编号 价格名                                                                                                                              价格
---- -------------------------------------------------------------------------------------------------------------------------------- -----------
a    价格A                                                                                                                              1
b    价格E                                                                                                                              1
c    价格B                                                                                                                              1

(3 行受影响)

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (产品编号 varchar(1),价格A int,价格B int,价格C int,价格D int,价格E int)
insert into #tb
select 'a',1,2,3,6,3 union all
select 'b',5,2,4,2,1 union all
select 'c',2,1,4,3,5


 
select 产品编号,价格A as 价格 ,'价格A' as 价格名 into #t from #tb where 价格A!=0
union all
select 产品编号,价格B,'价格B' as 价格名  from #tb where 价格B!=0
union all
select 产品编号,价格C,'价格C' as 价格名   from #tb where 价格C!=0
union all
select 产品编号,价格D,'价格D' as 价格名   from #tb where 价格D!=0
union all
select 产品编号,价格E,'价格E' as 价格名   from #tb where 价格E!=0
 
select * from #t t1
where not exists(select * from #t where 产品编号=t1.产品编号 and 价格<t1.价格)

产品编号 价格          价格名
---- ----------- -----
a    1           价格A
c    1           价格B
b    1           价格E

(3 行受影响)


drop table #t



 

[xman_78tom]

select * into #temp
from (
select [产品编号],'价格A' [价格名],[价格A] [价格] from tb
union all
select [产品编号],'价格B' [价格名],[价格B] [价格] from tb
union all
select [产品编号],'价格C' [价格名],[价格C] [价格] from tb
union all
select [产品编号],'价格D' [价格名],[价格D] [价格] from tb
union all
select [产品编号],'价格E' [价格名],[价格E] [价格] from tb
) t
where [价格]<>0;

select * 
from #temp t
where not exists (select * 
 from #temp where t.[产品编号]=[产品编号] and t.[价格]>[价格]);

[飘零一叶]

if OBJECT_ID('tb') is not null drop table tb
go
create table tb
(
   id int,
   价格A float,
   价格B float,
   价格C float,
   价格D float,
   价格E float
)
insert into tb
select 1,1.0,0,123,345,1.1

SELECT MIN(VALUE) AS VALUE
FROM(
SELECT * FROM tb
UNPIVOT(VALUE FOR 价格 in([价格A],[价格B],[价格C],[价格D],[价格E])) as p) PP
WHERE VALUE<>0
-------
VALUE
1

[百年树人]

select 产品编号,min(价格) as 最低价格
from
(
  select 产品编号,价格A as 价格 from tb where 价格A!=0
  union all
  select 产品编号,价格B from tb where 价格B!=0
  union all
  select 产品编号,价格C from tb where 价格C!=0
  union all
  select 产品编号,价格D from tb where 价格D!=0
  union all
  select 产品编号,价格E from tb where 价格E!=0

) t
group by 产品编号

[飘零一叶]

SELECT id,MIN(VALUE) AS VALUE
FROM(
SELECT * FROM tb
UNPIVOT(VALUE FOR 价格 in([价格A],[价格B],[价格C],[价格D],[价格E])) as p) PP
WHERE VALUE<>0
group by id

[billpu]

[Quote=引用 2 楼 josy 的回复:]

SQL code
select 产品编号,min(价格) as 最低价格
from
(
select 产品编号,价格A as 价格 from tb where 价格A!=0
union all
select 产品编号,价格B from tb where 价格B!=0
union all
select 产品编号,价格C from tb where 价格C!=0
union……
[/Quote]
up

[xman_78tom]

select [产品编号],[价格名],[价格] into #temp
from (
select [产品编号],'价格A' [价格名],[价格A] [价格] from tb
union all
select [产品编号],'价格B' [价格名],[价格B] [价格] from tb
union all
select [产品编号],'价格C' [价格名],[价格C] [价格] from tb
union all
select [产品编号],'价格D' [价格名],[价格D] [价格] from tb
union all
select [产品编号],'价格E' [价格名],[价格E] [价格] from tb
) t
where [价格]<>0;

delete t from #temp t 
where exists (select * from #temp 
 where t.[产品编号]=[产品编号] and t.[价格]>t.[价格]);

select * 
from #temp t
where not exists (select * from #temp 
 where t.[产品编号]=[产品编号] and t.[价格名]>t.[价格名]);

[想飞的狼]

如果两个价格重复，就会这样
产品编号 价格 价格名
001 1 价格A
001 1 价格B
如何让当两个价格名中的价格相同时，取靠前的价格名体现，比如A和B就取A，使产品编号是唯一的