查询最小值的数据
表中有三种类型的价格字段（还有其他字段）：a_price,b_price,c_price,都是int型，字段的值都大于等于0，三个字段中一定有两个等于0，另外一个大于0，现要查询表中的一条数据，条件是：a_price,b_price,c_price中值大于0，但又是最小的数据。
比如：a_price,b_price,c_price
0 0 80
0 35 0
60 0 0
…… …… ……
三个字段中大于0的最小的值是35，这样就是查询b_price=35的数据，应该怎么写呢？
问题点数：20、回复次数：10Top
1 楼LouisXIV（夜游神）回复于 2006-07-02 17:20:46 得分 15
select min(newcol)
from
(
select rtrim(a_price) as newcol
from tablename
union all
select rtrim(b_price)
from tablename
union all
select rtrim(c_price)
from tablename
)a
where newcol>0Top
2 楼hyrongg（视爱情为奢侈品）回复于 2006-07-02 17:24:34 得分 5
select min(tbl.a) from
(
select min(a_price) a from table
union all
select min(b_price) a from table
union all
select min(c_price) a from table
) tblTop
3 楼cornerslippers（角落里的拖鞋）回复于 2006-07-02 17:25:53 得分 0
要查的是一整条的数据，因为我还需要其他字段进行计算Top
4 楼hyrongg（视爱情为奢侈品）回复于 2006-07-02 17:26:24 得分 0
我考，真快呀Top
5 楼LouisXIV（夜游神）回复于 2006-07-02 17:32:16 得分 0
--那这样，就是长了点
select * from tablename
where
a_price=
(
select min(newcol)
from
(
select rtrim(a_price) as newcol
from tablename
union all
select rtrim(b_price)
from tablename
union all
select rtrim(c_price)
from tablename
)a
where newcol>0
)
or
b_price=
(
select min(newcol)
from
(
select rtrim(a_price) as newcol
from tablename
union all
select rtrim(b_price)
from tablename
union all
select rtrim(c_price)
from tablename
)a
where newcol>0
)
or
c_price=
(
select min(newcol)
from
(
select rtrim(a_price) as newcol
from tablename
union all
select rtrim(b_price)
from tablename
union all
select rtrim(c_price)
from tablename
)a
where newcol>0
)
Top
6 楼LouisXIV（夜游神）回复于 2006-07-02 17:33:27 得分 0
--如果用变量替换可以变简单点，如下
declare @i int
select @i=min(newcol)
from
(
select rtrim(a_price) as newcol
from tablename
union all
select rtrim(b_price)
from tablename
union all
select rtrim(c_price)
from tablename
)a
select * from tablename
where
a_price=@i or
b_price=@i or
c_price=@iTop
7 楼cornerslippers（角落里的拖鞋）回复于 2006-07-02 17:46:38 得分 0
用了上上楼的语句，运行后出现了：“不允许从数据类型money到varchar的隐性转化”，是哪里出了问题呢？Top
8 楼LouisXIV（夜游神）回复于 2006-07-02 18:02:57 得分 0
--测试的时候用的是int,当时在考虑别的方案，用了rtrim的隐性转换功能，try
declare @i money
select @i=min(newcol)
from
(
select a_price as newcol
from tablename
union all
select b_price
from tablename
union all
select c_price
from tablename
)a
where newcol>0
select * from tablename
where
a_price=@i or
b_price=@i or
c_price=@iTop
9 楼cornerslippers（角落里的拖鞋）回复于 2006-07-02 20:59:37 得分 0
问题已解决，感谢！Top
10 楼losedxyz（我真的一无所有）回复于 2006-07-02 21:26:50 得分 0
declare @a table (a int, b int, c int)
insert @a
select
0, 0, 80 union all select
0, 35, 0 union all select
60, 0, 0
select a+b+c as a into #aaa from @a
select * from #aaa
select * from #aaa x where not exists (select 1 from #aaa where a<x.a)
drop table #aaaTop
