Oracle中有一个非常强大的分析函数的功能over(partition by).但是在mysql中没有这样的函数,这里介绍如何在mysql中实现分析函数的类似功能。
需求:需要得到一些数据, 如最新的价格是按照某些字段分组,并且更新时间是最新的。在一段sql实现的条件下。
第一种方法:利用substring_index
这里实现是通过对相应字段分组,然后通过group_concat函数将price字段按照更新时间updateTime字段排序并连接成一个字符串,group函数默认的缺省值是按照逗号分割,最后通过subString_index函数分割逗号并调用第一个SELECT
product_id,
branch,
SUBSTRING_INDEX(GROUP_CONCAT(t.priceORDER BY t.updateTime DESC ),',',1) stock
FROM product_stock t
GROUP BY product_id,branch
这样我们就可以得到最新的价格,以及商品的编号,分支。
但是这样有一个缺陷,如果我们还想获取其他的字段,比如这个商品的所属种类,但是种类不按照group_by去分组,这个时候就又要select 所需要字段 去匹配上面得出的一大段,因为group by中没有的字段,不能直接写在select中。所以需要再次去关联。
在数据量比较大的情况下速度不是很理想。
这里仅仅是提供一种实现分析函数的一种思路。
下面介绍的是比较通用的方法。
2.使用mysql变量来实现SELECT
product_id,
branch,
price,
infomation,
remark,
from
(select
product_id,
branch,
price,
infomation,
remark,
if((@mainid = product_id and @mainbra = branch) , @rn := @rn + 1, @rn := 1) as rn,
@mainid := product_id,
@mainbra := branch
FROM product_stock t
order by product_id,branch,updateTime desc) a,
(select @rn := 0, @mainid := '', @mainbra := '') b
)c
where rn<2;
大概讲解一下,声明mysql变量(select @rn := 0, @mainid := '', @mainbra := '') b,三个变量rn,mainid,mainbra.然后在上面做判断,如果product_id和branch和前一条数据一样,那么+1,相当于实现了分组(因为全部数据是按照product_id,branch,updateTime desc这三个字段排序的),然后在最上面select 出需要的数据,判断条件rn<2或者rn=1,因为是按照updateTime降序的,所以便得到了最新的数据信息。
这种方式没有涉及大表关联,在性能以及获取字段方面优势很大。