Table1表结构: salesid,yy,mm,product_a,product_b,product_c,product_d,product_e,product_f,product_g记录表示:销售代表salesid在yy年mm月卖出的公司产品product_a到product_g的数量现在要求得到这样的结果: salesid,yy,mm,product_min,product_max,product_min/product_maxproduct_min为该销售代表在该月份卖出的产品中卖得最少的产品数量,product_max为该销售代表在该月份卖出的产品中卖得最多的产品数量。除了写case when语句,还有其它方法吗?
--或许没case when的效果好!
with cta as
(
select salesid,yy,mm,min(num)num
from
(select salesid,yy,mm,min(product_a) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_b) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_c) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_d) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_e) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_f) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,min(product_g) as num from tb group by salesid,yy,mm
)u
group by salesid,yy,mm
),cte as
(
select salesid,yy,mm,max(num)num
from
(select salesid,yy,mm,max(product_a) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_b) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_c) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_d) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_e) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_f) as num from tb group by salesid,yy,mm
union all
select salesid,yy,mm,max(product_g) as num from tb group by salesid,yy,mm
)u
group by salesid,yy,mm
)select a.salesid,a.yy,a.mm,b.num as min_num,c.num as max_num,(b.num*1./c.num) as per
from tb a,cta b,cte c
where a.salesid = b.salesid and a.yy = b.yy and a.mm = b.mm
and a.salesid = c.salesid and a.yy = c.yy and a.mm = c.mm
http://blog.163.com/soli1988_blog/blog/static/176895272201101034241771/