如何将mysql查询转换为transact SQL查询

Question

我有一个在MySQL中工作的查询。我需要使用TRANSACT SQL创建相同的结果。我还没弄明白这个咒语。下面是MySQL查询：

CREATE TEMPORARY TABLE tempo AS 
(SELECT master, MAX(nev) AS max 
 FROM accounts 
 WHERE where-clause 
 GROUP BY master 
 ORDER BY max DESC LIMIT 3);

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

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

如能提供帮助或指向说明，将不胜感激

Answer 1

我从来没有用过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;

Answer 2

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引擎来说会更好。希望能有所帮助。

Answer 3

如下所示：

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中的单个查询)，但每个方法都有自己的方法。