mysql - MYSQL中的交叉应用

Question

我正在尝试将此 Sql 服务器脚本转换为 mysql。

SELECT A.*
FROM TableA A
CROSS APPLY 
(
SELECT TOP 1 UID
FROM TableB 
WHERE BetID = A.BetID
AND BETCODE = A.BETCODE
ORDER BY CASE WHEN InfoCode > '' THEN 0 ELSE 1 END,UID
)Z
WHERE Z.UID = A.UID

请告知如何操作，我知道我可以更改 CROSS JOIN 和 Limit 1 但仍然失败

Answer 1

您不能使用 TOP，但可以使用 limit，因此您可以尝试，在 MySql 中，子查询表的范围与 SQL_SERVER 不同，因此您必须在子查询内构建联接

  SELECT A.*
  FROM TableA A
  CROSS JOIN  
  ( SELECT UID
    FROM TableB 
    INNER JOIN TABLEA A ON  BetID = A.BetID
      AND BETCODE = A.BETCODE
      ORDER BY CASE WHEN InfoCode > '' THEN 0 ELSE 1 END,UID
      LIMIT 1
       ) Z 
    WHERE  Z.UID = A.UID

或者具有单个结果的交叉联接也可以转换为内部联接

  SELECT A.*
  FROM TableA A
  INNER JOIN  
  ( SELECT UID
    FROM TableB 
   INNER JOIN TABLEA A ON  BetID = A.BetID
      AND BETCODE = A.BETCODE
      ORDER BY CASE WHEN InfoCode > '' THEN 0 ELSE 1 END,UID
      LIMIT 1
       ) Z ON   Z.UID = A.UID