PID Mon1 Mon2 Mon3 Mon4 Mon5 Mon6 Mon7 Mon8 Mon9 Mon10 Mon11 Mon12
================================================================================
1231 0 7.0 1.0 0 6.0 1.5 3.5 3.0 0 0 4.0 9.0
1234 2.0 0 2.5 5.5 0 0 6.0 6.5 9.0 0 3.5 7.0
2211 0 0 6.0 9.0 0 3.5 3.0 6.5 0 0 2.5 1.5
............上边是多种物品在不同月份的售价,售价变化是没有规律的,0 表示当月没货,现在需要使用一个查询得到物品的最低和最高价格,得到以下结果集PID 月份 最低价格
========================
1231 Mon3 1.0
1234 Mon1 2.0
2211 Mon12 1.5
............我现在用 CASE 实现了,可是 Case 表达式只能嵌套到 10 层,但一年
有 12 个月,我就没办法了。 大家有好的建议么?
================================================================================
1231 0 7.0 1.0 0 6.0 1.5 3.5 3.0 0 0 4.0 9.0
1234 2.0 0 2.5 5.5 0 0 6.0 6.5 9.0 0 3.5 7.0
2211 0 0 6.0 9.0 0 3.5 3.0 6.5 0 0 2.5 1.5
............上边是多种物品在不同月份的售价,售价变化是没有规律的,0 表示当月没货,现在需要使用一个查询得到物品的最低和最高价格,得到以下结果集PID 月份 最低价格
========================
1231 Mon3 1.0
1234 Mon1 2.0
2211 Mon12 1.5
............我现在用 CASE 实现了,可是 Case 表达式只能嵌套到 10 层,但一年
有 12 个月,我就没办法了。 大家有好的建议么?
select pid,min(min(mon1,mon2),min3).....
a.*
from
(select PID,'Mon1' as mon,Mon1 as val from 表 where Mon1 !=0 union
select PID,'Mon2' as mon,Mon2 as val from 表 where Mon2 !=0 union
select PID,'Mon3' as mon,Mon3 as val from 表 where Mon3 !=0 union
select PID,'Mon4' as mon,Mon4 as val from 表 where Mon4 !=0 union
select PID,'Mon5' as mon,Mon5 as val from 表 where Mon5 !=0 union
select PID,'Mon6' as mon,Mon6 as val from 表 where Mon6 !=0 union
select PID,'Mon7' as mon,Mon7 as val from 表 where Mon7 !=0 union
select PID,'Mon8' as mon,Mon8 as val from 表 where Mon8 !=0 union
select PID,'Mon9' as mon,Mon9 as val from 表 where Mon9 !=0 union
select PID,'Mon10' as mon,Mon10 as val from 表 where Mon10!=0 union
select PID,'Mon11' as mon,Mon11 as val from 表 where Mon11!=0 union
select PID,'Mon12' as mon,Mon12 as val from 表 where Mon12!=0) a
where
not exists(select 1
from (select PID,'Mon1' as mon,Mon1 as val from 表 where Mon1 !=0 union
select PID,'Mon2' as mon,Mon2 as val from 表 where Mon2 !=0 union
select PID,'Mon3' as mon,Mon3 as val from 表 where Mon3 !=0 union
select PID,'Mon4' as mon,Mon4 as val from 表 where Mon4 !=0 union
select PID,'Mon5' as mon,Mon5 as val from 表 where Mon5 !=0 union
select PID,'Mon6' as mon,Mon6 as val from 表 where Mon6 !=0 union
select PID,'Mon7' as mon,Mon7 as val from 表 where Mon7 !=0 union
select PID,'Mon8' as mon,Mon8 as val from 表 where Mon8 !=0 union
select PID,'Mon9' as mon,Mon9 as val from 表 where Mon9 !=0 union
select PID,'Mon10' as mon,Mon10 as val from 表 where Mon10!=0 union
select PID,'Mon11' as mon,Mon11 as val from 表 where Mon11!=0 union
select PID,'Mon12' as mon,Mon12 as val from 表 where Mon12!=0) b
where b.PID=a.PID and b.val<a.val)
PID int,
Mon1 dec(10,1),
Mon2 dec(10,1),
Mon3 dec(10,1),
Mon4 dec(10,1),
Mon5 dec(10,1),
Mon6 dec(10,1),
Mon7 dec(10,1),
Mon8 dec(10,1),
Mon9 dec(10,1),
Mon10 dec(10,1),
Mon11 dec(10,1),
Mon12 dec(10,1))insert into tb select 1231,0,7.0,1.0,0,6.0,1.5,3.5,3.0,0,0,4.0,9.0
union all select 1234,2.0,0,2.5,5.5,0,0,6.0,6.5,9.0,0,3.5,7.0
union all select 2211,0,0,6.0,9.0,0,3.5,3.0,6.5,0,0,2.5,1.5
gocreate function min_1(@PID int)
returns dec(10,1)
asbegin
return(select min(col) from
(select Mon1 as col from tb where PID=@PID
union all
select Mon2 from tb where PID=@PID
union all
select Mon3 from tb where PID=@PID
union all
select Mon4 from tb where PID=@PID
union all
select Mon5 from tb where PID=@PID
union all
select Mon6 from tb where PID=@PID
union all
select Mon7 from tb where PID=@PID
union all
select Mon8 from tb where PID=@PID
union all
select Mon9 from tb where PID=@PID
union all
select Mon10 from tb where PID=@PID
union all
select Mon11 from tb where PID=@PID
union all
select Mon12 from tb where PID=@PID)a where col<>0)
end
goselect PID,dbo.min_1(PID) from tbdrop function min_1
drop table tb
returns numeric(20,6) as
begin
declare @themin numeric(20,6)
if (@col1 < @col2)
begin
set @themin = @col1
end
else
begin
set @themin = @col2
end
return (@themin)
endcreate table #(pid int,
mon1 numeric(20,6),
mon2 numeric(20,6),
mon3 numeric(20,6),
mon4 numeric(20,6),
mon5 numeric(20,6),
mon6 numeric(20,6),
mon7 numeric(20,6),
mon8 numeric(20,6),
mon9 numeric(20,6),
mon10 numeric(20,6),
mon11 numeric(20,6),
mon12 numeric(20,6))
insert into #(pid,mon1,mon2,mon3,mon4,mon5,mon6,mon7,mon8,mon9,mon10,mon11,mon12)
select 1231,0,7.0,1.0,0,6.0,1.5,3.5,3.0,0,0,4.0,9.0
union all
select 1234,2.0,0,2.5,5.5,0,0,6.0,6.5,9.0,0,3.5,7.0
union all
select 2211,0,0,6.0,9.0,0,3.5,3.0,6.5,0,0,2.5,1.5select pid,dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(dbo.udf_getmins(mon1,mon2),mon3),mon4),mon5),mon6),mon7),mon8),mon9),mon10),mon11),mon12)
from #
drop table #