gpt4 book ai didi

mysql - 如何在 SQL 中显示 NULL 记录

转载 作者:行者123 更新时间:2023-11-28 23:07:18 25 4
gpt4 key购买 nike

我得到了包含 3 个表的简单数据库:

Galaxy(GalaxyID INTEGER PRIMARY KEY, Name TEXT);

Planet(PlanetID INTEGER PRIMARY KEY, GalaxyID INTEGER, Name TEXT, Population INTEGER);

Continent(ContinentID INTEGER PRIMARY KEY, PlanetID INTEGER,Name TEXT);

INSERT INTO Galaxy VALUES
(1, "MILKY WAY"),
(2, "IC 1101"),
(3, "GN-z11");

INSERT INTO Planet VALUES
(1,1, "Earth", 100000),
(2,1, "Moon", 0),
(3,1, "Saturn", 0),
(4,2, "IC-123", 0),
(5,2, "IC-124", 1),
(6,2, "IC-126", 4),
(7,3, "GN-24", 1),
(8,3, "GN-26", 1),
(9,3, "GN-28", 4);

INSERT INTO Continent VALUES
(1,1, "Europe"),
(2,4, "UnnamedContinent1"),
(3,4, "UnnamedContinent2");

我想选择根本没有大陆的星系:

SELECT Galaxy.Name, Planet.name
FROM Galaxy
LEFT OUTER JOIN Planet, Continent
ON Continent.PlanetID = Planet.PlanetID AND Galaxy.GalaxyID = Planet.GalaxyID
GROUP BY Galaxy.Name
HAVING COUNT(ContinentID) IS NULL

此查询将不起作用,但如果我给出 HAVING COUNT(ContinentID) IS NOT NULL,它将显示所有带大陆的星系(IC 1101 和 MILKY WAY)如何显示 NULL 记录,如:GN-z11 NULL

最佳答案

不需要COUNT,也不需要having,也不需要group by,只需添加额外的continent左连接表:

SELECT DISTINCT g.Name GalaxyName, p.name PlanetName
FROM Galaxy AS g
LEFT JOIN Planet AS p ON g.GalaxyID = p.GalaxyID
LEFT JOIN Continent AS c ON c.PlanetID = p.PlanetID
WHERE c.PlanetID IS NULL;

Demo

关于mysql - 如何在 SQL 中显示 NULL 记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46813377/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com