帶有動態 Case 子句的查詢或如何避免 Case When

讓我們使用 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)
;

不確定您要解決什麼報告問題，但看起來您可以通過簡單地引用連接列來獲得您所追求的。這裡有幾個例子：

/* Count of certifications held by all employees */
SELECT  c.name
       ,COUNT(1) AS certCount
FROM    qualification AS q
       INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
       c.name;

/* Count of certifications held, grouped by employee */
SELECT  q.employeeid
       ,c.name
       ,COUNT(1) AS certCount
FROM    qualification q
       INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
       q.employee_id
       ,c.name;

引用自：https://dba.stackexchange.com/questions/279446