主页 / dba / 问题 / 279446
Accepted
Jeremy Thompson
Asked: 2020-11-10 21:47:22 +0800 CST

带有动态 Case 子句的查询或如何避免 Case When

  • 772

让我们使用 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)
;
dynamic-sql case

1 个回答

  1. Best Answer

    不确定您要解决什么报告问题，但看起来您可以通过简单地引用连接列来获得您所追求的。这里有几个例子：

    /* 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;
    • 2

相关问题