SQL LEFT OUTER JOIN 与子查询语法

Question

提问by verkter

I am learning SQL trough a GalaXQL tutorial.

我正在通过 GalaXQL 教程学习 SQL。

I can't figure out the following question (Exercise 12):

我想不通以下问题（练习 12）：

Generate a list of stars with star ids below 100 with columns "starname", "startemp", "planetname", and "planettemp". The list should have all stars, with the unknown data filled out with NULL. These values are, as usual, fictional. Calculate the temperature for a star with ((class+7)*intensity)*1000000, and a planet's temperature is calculated from the star's temperature minus 50 times orbit distance.

使用列“starname”、“startemp”、“planetname”和“planettemp”生成一个star id低于100的恒星列表。该列表应包含所有星号，未知数据填充为 NULL。像往常一样，这些值是虚构的。用((class+7)*intensity)*1000000计算恒星的温度，行星的温度是恒星温度减去轨道距离的50倍。

What is the syntax to write a LEFT OUTER JOIN query when you have sub-query items "AS" that you need to join together?

当您需要将子查询项“AS”连接在一起时，编写 LEFT OUTER JOIN 查询的语法是什么？

Here is what I have:

这是我所拥有的：

SELECT stars.name AS starname, startemp, planets.name AS planetname, planettemp 
FROM stars, planets 
LEFT OUTER JOIN (SELECT ((stars.class + 7) * stars.intensity) * 1000000 AS startemp 
                 FROM stars) 
             ON stars.starid < 100 = planets.planetid 
LEFT OUTER JOIN (SELECT (startemp - 50 * planets.orbitdistance) AS planettemp 
                 FROM planets) 
             ON stars.starid < 100

Here is the database schema (sorry, cant post the image file due to low rep):

这是数据库架构（抱歉，由于代表低，无法发布图像文件）：

CREATE TABLE stars (starid INTEGER PRIMARY KEY,
                    name TEXT,
                    x DOUBLE NOT NULL,
                    y DOUBLE NOT NULL,
                    z DOUBLE NOT NULL,
                    class INTEGER NOT NULL,
                    intensity DOUBLE NOT NULL);

CREATE TABLE hilight (starid INTEGER UNIQUE);

CREATE TABLE planets (planetid INTEGER PRIMARY KEY,
                      starid INTEGER NOT NULL,
                      orbitdistance DOUBLE NOT NULL,
                      name TEXT,
                      color INTEGER NOT NULL,
                      radius DOUBLE NOT NULL);

CREATE TABLE moons (moonid INTEGER PRIMARY KEY,
                    planetid INTEGER NOT NULL,
                    orbitdistance DOUBLE NOT NULL,
                    name TEXT,
                    color INTEGER NOT NULL,
                    radius DOUBLE NOT NULL);

CREATE INDEX planets_starid ON planets (starid);
CREATE INDEX moons_planetid ON moons (planetid);

Answer 1

回答by Clockwork-Muse

Lets build this up slowly.

让我们慢慢建立它。

First, lets see about getting just the information about stars:

首先，让我们看看仅获取有关星星的信息：

SELECT name AS starName, (class + 7) * intensity * 1000000 AS starTemp 
FROM Stars
WHERE starId < 100

(this should look might familiar!)
We get a list of all stars whose starIdis less than 100 (the WHEREclause), grabbing the name and calculating temperature. At this point, we don't need a disambiguating reference to source.

（这应该看起来很熟悉！）
我们得到所有starId小于 100 的恒星的列表（WHERE子句），获取名称并计算温度。在这一点上，我们不需要对 source 的消除歧义引用。

Next, we need to add planet information. What about an INNER JOIN(note that the actual keyword INNERis optional)?

接下来，我们需要添加行星信息。怎么样INNER JOIN（注意实际的关键字INNER是可选的）？

SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
       Planets.name as planetName
FROM Stars
INNER JOIN Planets
        ON Planets.starId = Stars.starId
WHERE Stars.starId < 100

The ONclause is using an =(equals) condition to link planets to the star they orbit; otherwise, we'd be saying they were orbiting more than one star, which is very unusual! Each star is listed once for every planet it has, but that's expected.

该ON子句使用=（等于）条件将行星与其轨道的恒星联系起来；否则，我们会说它们绕着不止一颗恒星运行，这是非常不寻常的！每颗恒星都会针对它所拥有的每颗行星列出一次，但这是意料之中的。

...Except now we have a problem: Some of our stars from the first query disappeared! The (INNER) JOINis causing onlystars with at least one planetto be reported. But we still need to report stars without any planets! So what about a LEFT (OUTER) JOIN?

...除了现在我们有一个问题：我们第一次查询中的一些星星消失了！这(INNER) JOIN导致只有至少有一颗行星的恒星被报告。但是我们仍然需要报告没有任何行星的恒星！那么 aLEFT (OUTER) JOIN呢？

SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
       Planets.name as planetName
FROM Stars
LEFT JOIN Planets
       ON Planets.starId = Stars.starId
WHERE Stars.starId < 100

... And we have all the stars back, with planetNamebeing null(and only appearing once) if there are no planets for that star. Good so far!

......如果那颗恒星没有行星，我们就拥有了所有恒星，并且planetName存在null（并且只出现一次）。目前很好！

Now we need to add the planet temperature. Should be simple:

现在我们需要添加行星温度。应该很简单：

SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
       Planets.name as planetName, starTemp - (50 * Planets.orbitDistance) as planetTemp
FROM Stars
LEFT JOIN Planets
       ON Planets.starId = Stars.starId
WHERE Stars.starId < 100

...except that on most RDBMSs, you'll get a syntax error stating the system can't find starTemp. What's going on? The problem is that the new column alias (name) isn't (usually) available until afterthe SELECTpart of the statement runs. Which means we need to put in the calculation again:

...除了在大多数 RDBMS 上，您会收到一个语法错误，指出系统找不到starTemp. 这是怎么回事？问题是新的列别名（名称）（通常）在语句的一部分运行之后才可用SELECT。这意味着我们需要再次进行计算：

SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp,
       Planets.name as planetName, 
       ((Stars.class + 7) * Stars.intensity * 1000000) - (50 * Planets.orbitDistance) as planetTemp
FROM Stars
LEFT JOIN Planets
       ON Planets.starId = Stars.starId
WHERE Stars.starId < 100

(note that the db mayactually be smart enough to perform the starTempcalculation only once per-line, but when writing you have to mention it twice in this context).
Well, that's slightly messy, but it works. Hopefully, you'll remember to change both references if that's necessary...

（注意，该数据库可以实际上进行足够聪明，starTemp每行只有一次计算，但是写的时候，你必须在这方面两次提到它）。
嗯，这有点乱，但它有效。希望您会记得在必要时更改两个引用...

Thankfully, we can move the Starsportion of this into a subquery. We'll only have to list the calculation for starTemponce!

值得庆幸的是，我们可以Stars将这部分内容移动到子查询中。我们只需列出starTemp一次计算！

SELECT Stars.starName, Stars.starTemp,
       Planets.name as planetName, 
       Stars.starTemp - (50 * Planets.orbitDistance) as planetTemp
FROM (SELECT starId, name AS starName, (class + 7) * intensity * 1000000 AS starTemp 
      FROM Stars
      WHERE starId < 100) Stars
LEFT JOIN Planets
       ON Planets.starId = Stars.starId

Yeah, that looks like how I'd write it. Should work on essentially any RDBMS.

是的，这看起来就像我要写的那样。基本上应该适用于任何 RDBMS。

Note that the parenthesis in Stars.starTemp - (50 * Planets.orbitDistance)is only there for clarity for the reader, the meaning of the math would remain unchanged if they were removed. Regardless of how well you know operator-precedence rules, always put in parenthesis when mixing operations. This becomes especially beneficial when dealing with ORs and ANDs in JOINand WHEREconditions - many people lose track of what's going to be effected.
Also note that the implicit-join syntax (the comma-separated FROMclause) is considered bad practice in general, or outright deprecated on some platforms (queries will still run, but the db may scold you). It also makes certain things - like LEFT JOINs - difficult to do, and increases the possibility of accidently sabotaging yourself. So please, avoid it.

请注意，中的括号Stars.starTemp - (50 * Planets.orbitDistance)只是为了让读者清楚起见，如果删除它们，数学的含义将保持不变。不管您对运算符优先级规则有多了解，在混合运算时总是放在括号中。这在处理和条件中的ORs 和ANDs时变得特别有益- 许多人忘记了将要受到什么影响。另请注意，隐式连接语法（逗号分隔子句）通常被认为是不好的做法，或者在某些平台上完全不推荐使用（查询仍会运行，但数据库可能会骂你）。它也使某些事情 - 比如JOINWHERE
FROMLEFT JOINs - 很难做到，并增加了意外破坏自己的可能性。所以请避免它。

Answer 2

回答by fieven

SELECT * FROM (SELECT [...]) as Alias1
LEFT OUTER JOIN 
    (SELECT [...]) as Alias2
ON Alias1.id = Alias2.id

Answer 3

回答by SystemFun

WITH( 
 SELECT 
   stars.name AS starname, ((star.class+7)*star.intensity)*1000000) AS startemp,
   stars.starid
 FROM
   stars
) AS star_temps
SELECT 
   planets.name AS planetname, (startemp-50*planets.orbitdistance) AS planettemp
   star_temps.starname, star_temps.startemp
FROM 
   star_temps LEFT OUTER JOIN planets USING (star_id)
WHERE
   star_temps.starid < 100;

Alternatively, one can construct a sub-query (I've used a common table expression) to accomplish the same task as demonstrated below:

或者，可以构造一个子查询（我使用了一个公共表表达式）来完成相同的任务，如下所示：

SELECT 
   planets.name AS planetname, (startemp-50*planets.orbitdistance) AS planettemp
   star_temps.starname, star_temps.startemp
FROM 
   (SELECT 
      stars.name AS starname, ((star.class+7)*star.intensity)*1000000) AS startemp,
      stars.starid
    FROM
      stars
 ) AS star_temps
LEFT OUTER JOIN planets USING (star_id)
WHERE
   star_temps.starid < 100;

SQL LEFT OUTER JOIN 与子查询语法

提问by verkter

回答by Clockwork-Muse

回答by fieven

回答by SystemFun

相关推荐

最近更新

标签

SQL LEFT OUTER JOIN 与子查询语法

提问by verkter

回答by Clockwork-Muse

回答by fieven

回答by SystemFun

相关推荐

SQL 如何用NULL替换空格

如何在 sql 2005 或 2008 中使列区分大小写

在 SQL Server 2012 列中查询 JSON

sql 查询连接多个表 - 太慢（8 个表）

相关推荐

最近更新

标签