mysql - 带有 mySQL 连接的行号

Question

我有以下正常工作的查询:

SELECT  @row_num :=  IF(@prev_value=concat(o.CITY, o.keyword_text)  ,@row_num+1, 1) AS     POSITION
   ,o.idBUSINESS
   ,o.KEYWORD_TEXT
   ,o.CITY
 , o.BID_AMOUNT
  ,@prev_value := concat(o.CITY, o.keyword_text) 
FROM   (SELECT @row_num := 1) x,
  (SELECT @prev_value := '') y,
  (SELECT @prev_value1 := '') z,
elevated_business_queue o
ORDER BY o.CITY , o.KEYWORD_TEXT, o.BID_AMOUNT DESC

此查询返回:

# POSITION, idBUSINESS, KEYWORD_TEXT, CITY, BID_AMOUNT, @prev_value := concat(o.CITY, o.keyword_text)
'1', '7', '2', 'New Jersey', '3.50', 'New Jersey2'
'2', '5', '2', 'New Jersey', '2.50', 'New Jersey2'
'3', '1', '2', 'New Jersey', '2.50', 'New Jersey2'
'1', '5', '1', 'New York', '2.50', 'New York1'
'2', '7', '1', 'New York', '2.30', 'New York1'
'3', '1', '1', 'New York', '1.50', 'New York1'
'1', '9', '2', 'New York', '7.50', 'New York2'
'2', '1', '2', 'New York', '4.50', 'New York2'
'3', '5', '2', 'New York', '3.50', 'New York2'
'4', '7', '2', 'New York', '2.50', 'New York2'

这个数据是正确的。现在，我想将 elevated_business_queue 与另一个表连接起来。我是这样做的:

SELECT  @row_num :=  IF(@prev_value=concat(o.CITY, o.keyword_text)  ,@row_num+1, 1) AS POSITION
   ,o.idBUSINESS
   ,o.KEYWORD_TEXT
   ,o.CITY
 , o.BID_AMOUNT
   ,@prev_value := concat(o.CITY, o.keyword_text) 
    FROM   (SELECT @row_num := 1) x,
  (SELECT @prev_value := '') y,
  (SELECT @prev_value1 := '') z,
elevated_business_queue o
INNER JOIN funds_balance fb ON fb.idBUSINESS = o.idBUSINESS
 WHERE fb.PREMIUM_POSITIONS_CREDIT >= (o.BID_AMOUNT + ROUND((12.36/100)*o.BID_AMOUNT, 2))
ORDER BY o.CITY , o.KEYWORD_TEXT, o.BID_AMOUNT DESC

但是，当我加入时，我的 POSITION 就乱七八糟了。我现在得到:

# POSITION, idBUSINESS, KEYWORD_TEXT, CITY, BID_AMOUNT, @prev_value := concat(o.CITY, o.keyword_text)
'2', '7', '2', 'New Jersey', '3.50', 'New Jersey2'
'1', '1', '2', 'New Jersey', '2.50', 'New Jersey2'
'1', '5', '2', 'New Jersey', '2.50', 'New Jersey2'
'2', '5', '1', 'New York', '2.50', 'New York1'
'3', '7', '1', 'New York', '2.30', 'New York1'
'1', '1', '1', 'New York', '1.50', 'New York1'
'1', '1', '2', 'New York', '4.50', 'New York2'
'2', '5', '2', 'New York', '3.50', 'New York2'
'1', '7', '2', 'New York', '2.50', 'New York2'

有人可以帮忙吗

------------更新--------------------

我尝试了以下查询，但 POSITION 仍然关闭:

SELECT T1.*,fb.* FROM
(SELECT  @row_num :=  IF(@prev_value=concat(o.CITY, o.keyword_text)  ,@row_num+1, 1) AS       POSITION
,o.idBUSINESS
,o.KEYWORD_TEXT
,o.CITY
,o.BID_AMOUNT
,@prev_value := concat(o.CITY, o.keyword_text) 
FROM   (SELECT @row_num := 1) x,
 (SELECT @prev_value := '') y,
 (SELECT @prev_value1 := '') z,
elevated_business_queue o
ORDER BY o.CITY , o.KEYWORD_TEXT, o.BID_AMOUNT DESC)T1
INNER JOIN funds_balance fb ON fb.idBUSINESS = T1.idBUSINESS
WHERE fb.PREMIUM_POSITIONS_CREDIT >= (T1.BID_AMOUNT + ROUND((12.36/100)*T1.BID_AMOUNT,    2))
ORDER BY T1.CITY ,T1.KEYWORD_TEXT, T1.BID_AMOUNT DESC;

我现在得到以下结果集:

# POSITION, idBUSINESS, KEYWORD_TEXT, CITY, BID_AMOUNT, @prev_value := concat(o.CITY, o.keyword_text), idBUSINESS, PREMIUM_POSITIONS_CREDIT
'1', '7', '2', 'New Jersey', '3.50', 'New Jersey2', '7', '17.30'
'3', '1', '2', 'New Jersey', '2.50', 'New Jersey2', '1', '12.31'
'2', '5', '2', 'New Jersey', '2.50', 'New Jersey2', '5', '15.19'
'1', '5', '1', 'New York', '2.50', 'New York1', '5', '15.19'
'2', '7', '1', 'New York', '2.30', 'New York1', '7', '17.30'
'3', '1', '1', 'New York', '1.50', 'New York1', '1', '12.31'
'2', '1', '2', 'New York', '4.50', 'New York2', '1', '12.31'
'3', '5', '2', 'New York', '3.50', 'New York2', '5', '15.19'
'4', '7', '2', 'New York', '2.50', 'New York2', '7', '17.30'

在纽约，KEYWORD_TEXT=2 没有 POSITION=1。以下行的位置应为 1:

'2', '1', '2', 'New York', '4.50', 'New York2', '1', '12.31'

Answer 1

也许您可以用这样的窗口函数替换您的 row_num 计算:

row_number() over(partition by o.CITY , o.KEYWORD_TEXT order by o.BID_AMOUNT DESC)