如何对视图中的新列使用相关子查询？

Question

我正在尝试编写一个包含 3 列的视图：行星、月亮和最大。

该视图旨在显示行星、它们的卫星以及“是”或“否”列，指示它是否是该行星最大的卫星。

仅使用一个 Basetable，我引用的列是 MoonPlanetOrbit（只有当 bodyType = 'Moon' 时才不为 null）、bodyName（月亮的名称）和largest（'yes' 或 'no'）。

到目前为止，这是我的尝试：

CREATE VIEW Moons (Planet, Moon, Largest)
select moonPlanetOrbited, bodyName, ('Yes' if bodyName = (SELECT top 1 moonMeanRadius from Body where moonPlanetOrbited = bodyName order by moonMeanRadius) as Largest)

如果需要，我可以提供更多信息。

谢谢， 科迪

Answer 1

SQL 最适合处理数据集。我的建议是使用 SELECT 语句和 MAX() 函数获取最大卫星集，然后将结果集与整个表连接起来。然后测试月亮是否等于最大的，以便打印“是”或“否”。

这是一个使用 MySQL 的示例。我创建了一个包含 MoonPlanetOrbited、bodyName、moonMeanRadius 列的 Moons 表。以下 SQL 为给定的 MoonPlanetOrbited 选择最大的 MoonMeanRadius：

SELECT moonPlantedOrbited, MAX(moonMeanRadius) as maxMoonRadius
FROM Moons
GROUP BY moonPlanetOrbitede

现在我们有了 maxMoonRadius 的列表，将结果集与整个表连接起来并测试 MoonMeanRadius 是否等于 maxMoonRadius：

SELECT m1.moonPlanetOrbited, m2.bodyName,  
if(m1.moonMeanRadius = m2.maxMoonRadius, 'Yes', 'No') as Largest 
FROM Moons m1  
JOIN (   
  SELECT moonPlanetOrbited, MAX(moonMeanRadius) as maxMoonRadius   
  FROM Moons   
  GROUP BY moonPlanetOrbited 
) m2 
ON m1.moonPlanetOrbited = m2.moonPlanetOrbited;

IF 语法来自 MySQL 5.5：
http://dev.mysql.com/ doc/refman/5.5/en/control-flow-functions.html#function_if

使用以下 SQL 进行测试：

CREATE TABLE Moons( 
  moonPlanetOrbited VARCHAR(255), 
  bodyName VARCHAR(255), 
  moonMeanRadius FLOAT
);

INSERT INTO Moons('a', 'b', 1.01);
INSERT INTO Moons('a', 'c', 1.02);
INSERT INTO Moons('a', 'd', 1.03);
INSERT INTO Moons('a', 'e', 1.04);


+-------------------+----------+---------+
| moonPlanetOrbited | bodyName | Largest |
+-------------------+----------+---------+
| a                 | b        | No      |
| a                 | c        | No      |
| a                 | d        | No      |
| a                 | e        | Yes     |
+-------------------+----------+---------+
4 rows in set (0.00 sec)

Answer 2

这是我的 MS-SQL 语法：

SELECT
  B.moonPlanetOrbited
  , B.bodyName
  , CASE
      WHEN B.bodyName = 
      (SELECT TOP 1
         iB.bodyName
       FROM
         Body AS iB
       WHERE
         iB.moonPlanetOrbited = B.bodyName
       ORDER BY
         iB.moonMeanRadius DESC
       )
       THEN 'Yes'
       ELSE 'No'
     END CASE AS [Largest]
FROM
 Body AS B

如果表使用 ID 作为主键，则比较 ID 而不是名称可能会更好。

Answer 3

这是一个尽可能类似于您的方法的尝试（未经测试），因为您的想法并不遥远：

Select 
M.moonPlanetOrbited, 
M.bodyName, 
CASE
  WHEN M.bodyName = 
    (SELECT top 1 bodyName from Body 
    where moonPlanetOrbited = M.moonPlanetOrbited 
    order by moonMeanRadius DESC) 
Then 'Y' 
Else 'N'
AS Largest
FROM body

您只需要一个表前缀来实际执行与根表的关联，并确保您是在 CASE 语句中进行同类比较。