分组和返回结果,只有当组中所有记录符合条件
问题描述:
的DB结构如下:分组和返回结果,只有当组中所有记录符合条件
地址有许多计划 计划有许多工作 乔布斯有许多UserJobs
我想无论计划如何,将为特定地址完成的所有工作分组。
从那里,我只希望看到还没有做
一个成功的作品成功的作品是时UserJobs.perfomance =准时或UserJobs.perfomance =晚
SELECT
"addresses"."address1",
"user_jobs"."performance"
FROM
"addresses" JOIN "plans" ON "addresses"."id" = "plans"."address_id"
JOIN "jobs" ON "plans"."id" = "jobs"."plan_id"
JOIN "user_jobs" ON "jobs"."id" = "user_jobs"."job_id"
group by
"addresses"."address1",
"user_jobs"."performance"
地址我试图建立上面的查询,但我已经可以看到它的缺陷。它将由地址组,但是如果有地址中的不同表现,将拆分
答
您可以使用有条件的聚集如果要算成功完成工作:
SELECT
"addresses"."address1",
COUNT(CASE
WHEN "user_jobs"."performance" IN ('ontime', 'late') THEN 1
END) AS cnt
FROM
"addresses" JOIN "plans" ON "addresses"."id" = "plans"."address_id"
JOIN "jobs" ON "plans"."id" = "jobs"."plan_id"
JOIN "user_jobs" ON "jobs"."id" = "user_jobs"."job_id"
GROUP BY
"addresses"."address1
答
“的所有记录的组符合条件” - > bool_and聚集函数
select "addresses"."address1" FROM
"addresses" JOIN "plans" ON "addresses"."id" = "plans"."address_id"
JOIN "jobs" ON "plans"."id" = "jobs"."plan_id"
JOIN "user_jobs" ON "jobs"."id" = "user_jobs"."job_id"
group by
"addresses"."address1"
HAVING bool_and("user_jobs"."performance" IN ('ontime', 'late'))
与当前版本的Postgres这也可以写为'COUNT(*)过滤器(其中,在user_jobs.performance( '导通时间', '晚'))' –