mySql @ variables与不同的客户端的行为不同

问题描述：

I have this query:

SELECT @ROWNUM := CASE WHEN @PREV_CAT_ID=T1.category_id THEN @ROWNUM+1 ELSE 1 END AS RANK, 
  @PREV_CAT_ID:=T1.category_id, 
  T1.* 
  FROM(d.category_id
      FROM some_table d,
      (SELECT @ROWNUM := 0)R 
      ORDER BY d.category_id
  }T1

I want this RANK column to increment for records with same category_id. When category_id increases, I want rank to reset to 1 and start incrementing again.

This works great when I execute my query from TOAD, but when inside my web application or when executed from phpMyAdmin the RANK is always 1, no matter what I do.

Does anyone have any idea, what the problem might be?

Thank You!

我有这样的查询： p>

  SELECT @ROWNUM：= 例如@ PREV_CAT_ID = T1.category_id那么@ROWNUM + 1 ELSE 1 END AS RANK，
 @PREV_CAT_ID：= T1.category_id，
 T1。* 
 FROM（d.category_id 
 FROM some_table d，
（  SELECT @ROWNUM：= 0）R 
 ORDER BY d.category_id 
} T1 
  code>  pre> 
 
 我想要为具有相同category_id的记录增加此RANK列。  category_id增加，我希望排名重置为1并再次开始递增。 p> 
 
 
当我从TOAD执行查询时，这很有用，但是在我的Web应用程序内部或从phpMyAdmin执行时，RANK 总是1，无论我做什么。 p> 
 
 
有没有人知道，问题可能是什么？ p> 
 
 
谢谢！ p  > 
  div>

答

You should reset all variables (@ROWNUM and @PREV_CAT_ID) every time. Try this query -

SELECT
  category_id, rank
FROM (
  SELECT 
    category_id
    , IF(@category_id = category_id, @num := @num + 1, @num := 1) AS rank
    , @category_id := category_id
  FROM some_table
  CROSS JOIN (SELECT @num := 0, @category_id:= NULL) reset_all_vars
  ORDER BY category_id
) t;

mySql @ variables与不同的客户端的行为不同

相关推荐