表中有三种类型的价格字段(还有其他字段):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的数据,应该怎么写呢?
比如:a_price,b_price,c_price
0 0 80
0 35 0
60 0 0
…… …… ……
三个字段中大于0的最小的值是35,这样就是查询b_price=35的数据,应该怎么写呢?
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
(
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
) tbl
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
)
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
)aselect * from tablename
where
a_price=@i or
b_price=@i or
c_price=@i
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>0select * from tablename
where
a_price=@i or
b_price=@i or
c_price=@i
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 @aselect * from #aaaselect * from #aaa x where not exists (select 1 from #aaa where a<x.a)drop table #aaa