编写嵌套子查询的动态查询

Question

让我们使用一个带有嵌套查询列表的日常查询，这样我就可以向您展示问题所在，看看在新的证书发布时我们如何添加子查询：

select DISTINCT e.display_name as "Name",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 1 limit 1),0) "Practitioner",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 2 limit 1),0) "AlexaSkillBuilder",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 3 limit 1),0) "SolutionArchitectAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 4 limit 1),0) "DeveloperAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 5 limit 1),0) "SysopsAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 6 limit 1),0) "SolutionArchitectPro",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 7 limit 1),0) "DevopsPro",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 8 limit 1),0) "Security",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 9 limit 1),0) "Networking",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 10 limit 1),0) "BigData",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 11 limit 1),0) "MachineLearning"
from employee e
inner join qualification q on q.employeeid = e.id
group by e.id, e.display_name;

该数据的输出是一份类似于以下内容的报告：

+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Name | Practitioner | Alexa | SAA | DEV | SOA | DevOps | ArchPro | Sec | Networking | BigData | ML |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Joe  | 1            | 0     | 0   | 0   | 0   | 1      | 0       | 0   | 0          | 0       | 0  |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Jane | 1            | 1     | 0   | 0   | 0   | 0      | 0       | 0   | 0          | 0       | 0  |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+

How我可以写这个查询，这样我就不需要每次出现新的认证时就编辑它了？

下面是Postgres中的模式和示例数据：

CREATE TABLE certification (
    id serial NOT NULL,
    officialcertname text NOT NULL,
    "name" text NOT NULL,
    vendorid int4 NOT NULL DEFAULT 1,
    isdeleted bool NOT NULL DEFAULT false,
    CONSTRAINT certification_pkey PRIMARY KEY (id)
);

CREATE TABLE qualification (
    id serial NOT NULL,
    employeeid int4 NOT NULL,
    certificationid int4 NOT NULL,
    date_attained timestamptz NULL,
    date_expiry timestamptz NULL,
    certurl text NULL,
    verified bool NOT NULL DEFAULT false,
    created_by text NOT NULL,
    created_date timestamptz NOT NULL,
    modified_by text NULL,
    modified_date timestamptz NULL,
    CONSTRAINT qualification_pkey PRIMARY KEY (id)
);


INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES 
('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false)
,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false)
,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false)
,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false)
,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false)
,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false)
,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false)
,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false)
,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false)
,('AWS Certified Big Data - Specialty','AWS Big Data',1,false)

这不是我昨天问的问题的重复：带有动态Case子句的查询或如何在，因为这与CASE WHEN有关，这是关于子查询的。

Answer 1

这篇文章非常有用：https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html和特定用途的演示：https://dbfiddle.uk/?rdbms=postgres_11&fiddle=407a37686238bb3fbcbc4285d1705871

不幸的是，使用数据透视/交叉表比用代码动态生成原始查询要复杂得多：

SELECT employeeid, coalesce(GCPAssociateCloudEngineer ,0) as GCPAssociateCloudEngineer,coalesce(GCPProfessionalCloudArchitect ,0) as GCPProfessionalCloudArchitect,coalesce(GCPProfessionalCloudSecurityEngineer,0) as GCPProfessionalCloudSecurityEngineer,coalesce(GCPProfessionalDataEngineer ,0) as GCPProfessionalDataEngineer,coalesce(GCPProfessionalCloudDeveloper ,0) as GCPProfessionalCloudDeveloper,coalesce(GCPProfessionalNetworkEngineer,0) as GCPProfessionalNetworkEngineer
FROM crosstab('
SELECT employeeid, c.name as "name",1 as complete FROM qualification q
inner JOIN certification AS c ON c.id = q.certificationid where c.vendorid = 3 order by 2
',  $VALUES ('GCPAssociateCloudEngineer'), ('GCPProfessionalCloudArchitect'), ('GCPProfessionalCloudSecurityEngineer'), ('GCPProfessionalDataEngineer'), ('GCPProfessionalCloudDeveloper'), ('GCPProfessionalNetworkEngineer')$
) 
 AS final_result(employeecertregid int4,GCPAssociateCloudEngineer int4,GCPProfessionalCloudArchitect int4,GCPProfessionalCloudSecurityEngineer int4,GCPProfessionalDataEngineer int4,GCPProfessionalCloudDeveloper int4,GCPProfessionalNetworkEngineer int4);