时间戳

首页 > 解决方案 > 我正在尝试对多个条件进行计数并将它们作为单独的列返回

sql - 我正在尝试对多个条件进行计数并将它们作为单独的列返回

问题描述

SELECT * FROM
(SELECT COUNT("user_job".id) FROM "user_job" WHERE "user".job_title = 'Welder / Fitter') AS 
"WelderFitters"  
(SELECT COUNT("user_job".id)  AS "Welders"  FROM "user_job" WHERE "user".job_title = 
'Welder')
(SELECT COUNT("user_job".id)  AS "Fitters"  FROM "user_job" WHERE "user".job_title = 
'Fitter')
(SELECT COUNT("user_job".id)  AS "Helpers"  FROM "user_job" WHERE "user".job_title = 
'Helper')
JOIN "user" ON "user".id = "user_job".user_id 
JOIN "job" ON "job".id = "user_job".job_id
WHERE "job".id = 22;

//涉及两个连接。这根本不起作用，但我认为它说明了我正在尝试做的事情。

标签： sqlpostgresqljoincount

解决方案

我认为你想要条件聚合。您的示例查询建议如下：

SELECT COUNT(*) FILTER (WHERE u.job_title = 'Welder / Fitter') AS 
"WelderFitters",
       COUNT(*) FILTER (WHERE u.job_title = 'Welder') AS 
"Welders",
       COUNT(*) FILTER (WHERE u.job_title =  'Fitter') AS "Fitters",
       COUNT(*) FILTER (WHERE u.job_title =  'Helper') AS "Helpers"
FROM "user" u JOIN
     "user_job" uj
     ON u.id = uj.user_id JOIN
     "job" j
     ON j.id = uj.job_id
WHERE j.id = 2;

但是，我不确定是否job_title真的在users表中。它似乎更有可能在jobs表中。

推荐阅读