如何将MySQL查询转换为Transact-SQL查询

我从未使用过MySQL,但我认为这是您想要的:

DECLARE @min INT;

SELECT TOP 3
       master
     , MAX(nev) AS max
INTO #tempo
FROM accounts
WHERE WHERE-clause
GROUP BY master
ORDER BY max DESC;

SELECT @min = MIN(max)
FROM #tempo;

SELECT *
FROM accounts
WHERE master IN
      (
          SELECT master FROM #tempo
      )
      AND nev >= @min
ORDER BY NEV DESC;

像这样:

WITH tempo AS (
    SELECT TOP 3 master, 
        MAX(nev) AS max 
    FROM accounts 
    WHERE [WHERE-clause] 
    GROUP BY master 
    )

SELECT @min = MIN(max)
FROM tempo;

SELECT *
FROM accounts
WHERE master IN (
        SELECT master
        FROM tempo
        )
    AND nev >= @min
ORDER BY NEV DESC;

也就是说,有更好的方法来编写它(例如,SQL Server中的一个查询),但它们各自都有。

SELECT top 3 master, MAX(nev) AS max into #tempo FROM accounts WHERE where-clause GROUP BY master ORDER BY max DESC;

DECLARE @min NUMERIC(38);
SET @min = (SELECT MIN(max) FROM #tempo);

SELECT * FROM accounts WHERE master IN (SELECT master FROM #tempo) AND nev >= @min ORDER BY NEV DESC;

我会这样翻译的。但你不需要临时工。您可以编写一个查询来获得结果,这对SQL引擎更好。 希望它有帮助。

我们可以使用CTE作为比临时表更好的选项,以及 JOIN 很可能是一个更好的替代品 IN() 表达式,结果相同:

WITH temp0 As (
  SELECT TOP 3 master, MAX(nev) AS max 
  FROM accounts 
  WHERE where-clause 
  GROUP BY master 
  ORDER BY MAX(nev) DESC 
)
SELECT a.* 
FROM accounts a 
INNER JOIN temp0 t ON t.master = a.master
WHERE a.nev >= (SELECT MIN(max) FROM temp0) 
ORDER BY a.NEV DESC;

这也是在MySQL的最新版本中编写它的更好方法。