因此，对于某些上下文，我有一个数据库，其中包含一个Listings表，该表具有以下属性：id、name、neighborhood_id、price、roomType_id。
我还有一个Neighborhoods表，它只有两个属性：身份，姓名。
最后，我有一个RoomTypes表，它有两个属性：id、roomType。
现在，我想运行一个查询，该查询查询Listings表并返回每个社区（这将是Listings.neighborhood_id的一个group by）中私人房间（aka roomType_id == 1）和整个房屋/公寓（aka roomType_id == 2）之间的价格差异。我的问题是弄清楚如何在一个查询中基本上运行以下查询两次，并获取两列之间的差异以生成第三个新列。理想情况下，我还希望有第四列，列出该差异适用的邻居的名称。
查询的版本1

SELECT n.name as "Neighborhood Name", AVG(l.price) as "Private Room Price"
FROM Listings l
INNER JOIN Neighborhoods n on l.neighborhood_id = n.id
INNER JOIN RoomTypes r on l.roomType_id = r.id
WHERE r.roomType = "Private room"
GROUP BY n.id;

查询的版本2

SELECT n.name as "Neighborhood Name", AVG(l.price) as "Entire home/apt Price"
FROM Listings l
INNER JOIN Neighborhoods n on l.neighborhood_id = n.id
INNER JOIN RoomTypes r on l.roomType_id = r.id
WHERE r.roomType = "Entire home/apt"
GROUP BY n.id;

我也试过这种查询没有用……

SELECT n.name as "Neighborhood Name", AVG(l.price) as "Private Room Price", AVG(l2.price) as "Entire home/apt Price"
FROM Listings l
INNER JOIN Listings l2 on l.id = l2.id
INNER JOIN Neighborhoods n on l.neighborhood_id = n.id
INNER JOIN RoomTypes r on l.roomType_id = r.id
INNER JOIN RoomTypes r2 on l2.roomType_id = r2.id
WHERE r2.roomType = "Entire home/apt" AND r.roomType = "Private room"
GROUP BY n.id;

任何帮助将不胜感激。