mysql - 行号与mySQL联接

我有以下正确的查询：

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'

这个数据是正确的。现在，我想用另一个表加入提升的商业队列。我是这样做的：

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, 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'

有人能帮忙吗。
--------------更新----------------------
我尝试了以下查询，但该位置仍处于关闭状态：

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'

在纽约，关键字2没有位置=1。以下行的位置应为1：

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

最佳答案

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

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

关于mysql - 行号与mySQL联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22112330/