我试图弄清楚这个查询究竟执行了什么。特别是使用变量@
和赋值的部分:=
。第一部分非常简单,因为我们有一个来自派生表 t1 的嵌套查询,但我不太清楚的是列 rn 的结果。这是查询:
SELECT
t1.user_id,
t1.percentage,
t1.id,
t1.name,
(@rn := if(@uid = t1.user_id, @rn + 1,
if(@uid := t1.user_id, 1, 1))
) as rn
FROM
(SELECT
pbt.user_id,
pbt.percentage,
t.id, t.name
FROM
user_purchased_brand_tags AS pbt
JOIN tags t on t.id = pbt.tag_id
ORDER BY pbt.user_id, pbt.percentage desc) t1
它创建一个行号,当 user_id 更改时该行号重置为 1。
if 函数的参数是 (condition, true part, false part)。
(@rn := /* assign the new value to the variable @rn */
if(@uid = t1.user_id, @rn + 1, /* when user_id is the same as the value in @uid, return @rn + 1.
The comparison is done before assigning the value of the current row below. Therefore the @uid variable still holds the value of the previous row */
if(@uid := t1.user_id, 1, 1)) /* this applies when above condition is not true.
It's a clever combination of assigning the value of the current row to @uid and returning 1 at the same time. */
) as rn
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句