SQL - UNION，UNION ALL，INTERSECT，EXCEPT

I am learning SQL with GalaXQL and can't figure out the following question:

我正在使用GalaXQL学习SQL,无法弄清楚以下问题:

Build a query which returns starids from planets. The starids should be selected so that for each starid (x) in the list: - there should exist a planet with a starid that's three times x but - there should not exist a planet with starid two times x. Only use starid:s from the planets table.

构建一个从行星返回starid的查询。应该选择星号,以便对于列表中的每个星号(x): - 应该存在一个星球,它是x的三倍但是 - 不应该存在星球两倍x的行星。仅使用行星表中的starid:s。

What would be a query that would accomplish this using UNION, UNION ALL, INTERSECT, EXCEPT? Please digest this in your answer as I am a beginner. Thank you in advance!

什么是使用UNION,UNION ALL,INTERSECT,EXCEPT完成此任务的查询?因为我是初学者,请在你的答案中消化这个。先谢谢你!

Here is the database schema:

这是数据库模式:

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);

Here is my query:

这是我的查询:

SELECT planets.starid
FROM planets
UNION
SELECT starid*3 FROM planets
EXCEPT
SELECT starid*2 FROM planets

2 个解决方案

#1

Easiest is to start with exists/not exists:

最简单的是从存在/不存在开始:

SELECT starid
FROM planets p1
WHERE EXISTS (
    SELECT 1 
    FROM planets p2
    WHERE p2.starid = 3*p1.starid
)
AND NOT EXISTS (
    SELECT 1 
    FROM planets p3
    WHERE p3.starid = 2*p1.starid
)

If you want to express this in terms of union/intersect, let A be all starids from planet, and let B be the starids * 3. Since both of these must exists we are talking intesection

如果你想用联合/交叉来表达这一点,那么让A成为来自星球的所有星辰,让B成为starids * 3.由于这两者都必须存在,我们正在谈论剖析

SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets

From that set we want to exclude starids * 2. That is all elements but starids * 2. This is known as EXCEPT or MINUS:

从那个集合我们想要排除starids * 2.这是除了starids之外的所有元素* 2.这被称为EXCEPT或MINUS:

SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets

Since the result should belong to both of these sets we once again apply intersection:

由于结果应属于这两个集合,我们再次应用交集:

(SELECT starid
FROM planets
INTERSECT
SELECT 3*starid
FROM planets)
INTERSECT
(SELECT starid
FROM planets
EXCEPT
SELECT 2*starid
FROM planets)

Did that help?

这有帮助吗?

#2

SQL - UNION，UNION ALL，INTERSECT，EXCEPT for more details check link this help me

有关详细信息,请查看链接,这有助于我

#1