sql - PostgreSQL-(Somewhat)复杂的SQL查询-需要跨多个表并进行批注

给定以下架构：

CREATE TABLE "organization_building" (
       "id" serial NOT NULL PRIMARY KEY,
       "name" varchar(255) NOT NULL);
CREATE TABLE "organization_calendar" (
       "id" serial NOT NULL PRIMARY KEY,
       "name" varchar(255) NOT NULL);
CREATE TABLE "organization_event" (
       "id" serial NOT NULL PRIMARY KEY,
       "start" timestamp with time zone NOT NULL,
       "end" timestamp with time zone NOT NULL, "calendar_id" integer NOT NULL);
CREATE TABLE "organization_floor" (
       "id" serial NOT NULL PRIMARY KEY,
       "name" varchar(255) NOT NULL,
       "building_id" integer NOT NULL, "calendar_id" integer NULL);
ALTER TABLE "organization_event" ADD CONSTRAINT "organization_event_calendar_id_433ca4c752778f76_fk_organization_calendar_id" FOREIGN KEY ("calendar_id") REFERENCES "organization_calendar" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "organization_event_9c13da14" ON "organization_event" ("calendar_id");
ALTER TABLE "organization_floor" ADD CONSTRAINT "organization_floor_building_id_6152d8c8b88bbaea_fk_organization_building_id" FOREIGN KEY ("building_id") REFERENCES "organization_building" ("id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "organization_floor" ADD CONSTRAINT "organization_floor_calendar_id_66357b835616383c_fk_organization_calendar_id" FOREIGN KEY ("calendar_id") REFERENCES "organization_calendar" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "organization_floor_4c63c6ae" ON "organization_floor" ("building_id");
CREATE INDEX "organization_floor_df2e10dc" ON "organization_floor" ("calendar_id");

给定日期时间范围（开始和结束），如何以以下格式获取结果：

| Building_Id | Building_Name   | Floors_Total | Floors_Available |
|-------------+-----------------+--------------+------------------|
|           1 | First Building  |           10 |                5 |
|           2 | Second Building |           20 |               20 |

“可用”楼层描述如下：
它必须设置日历id。
它不能有任何在时间范围内发生的“事件”。（没有重叠，事件不能以任何方式与日期时间范围聚合）。
我想跳过所有没有楼层的建筑物。
SQLFiddle在这里处理数据：http://sqlfiddle.com/#!15/a6370

最佳答案

我明白了。

select organization_building.id as "id",
       organization_building.name as "building_name",
       COUNT(organization_floor.id) AS "Floor Available",
       tmp.f AS "Floors_Total"
FROM organization_floor INNER JOIN organization_building
                  ON organization_floor.building_id = organization_building.id
     INNER JOIN (select building_id, COUNT(*) AS "f" from organization_floor GROUP BY building_id) AS tmp
                  ON organization_building.id=tmp.building_id
WHERE calendar_id in (select calendar_id from organization_event
                                         where "start" >='2015-11-03' OR "end" < '2015-11-04')
      and tmp.f > 0 GROUP BY organization_building.id, tmp.f;

这不是解决问题的最好办法，但我试过了：）

关于sql - PostgreSQL-(Somewhat)复杂的SQL查询-需要跨多个表并进行批注，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33504783/