让我们使用 CASE 子句进行日常查询，以便向您展示问题，看看当新的认证出现时我们必须如何更改查询：
select count(*) as "certCount",
CASE
when c.id = 1 then 'AWS Cloud Practitioner'
when c.id = 2 then 'AWS Alexa Skill Builder'
when c.id = 3 then 'AWS Solution Architect Associate'
when c.id = 4 then 'AWS Developer Associate'
when c.id = 5 then 'AWS SysOps Associate'
when c.id = 6 then 'AWS Solution Architect Professional'
when c.id = 7 then 'AWS DevOps Professional'
when c.id = 8 then 'AWS Security'
when c.id = 9 then 'AWS Networking'
when c.id = 10 then 'AWS Big Data'
when c.id = 11 then 'AWS Machine Learning'
ELSE 'N/A'
END AS name
from certification c
inner join qualification q on c.id = q.certificationid
group by q.certificationid, c.id
当然，我们可以生成 SQL 并以 Ad-Hoc 方式执行它。这是我过去所做的，尽管我想知道是否有另一种方式，比如某种加入方式可以避免 CASE WHENs？
下面的模式和示例数据，它在 PostGres 中，但我也对 Oracle、SQLServer 等的动态案例语句感兴趣：
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)
;
