Início / dba / Perguntas / 279446
Accepted
Jeremy Thompson
Asked: 2020-11-10 21:47:22 +0800 CST

Uma consulta com uma cláusula Dynamic Case ou como evitar Case When

  • 772

Vamos fazer uma consulta diária com uma cláusula CASE para que eu possa mostrar o problema, veja como teríamos que mudar a consulta quando uma nova Certificação sair:

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

Claro que poderíamos gerar o SQL e executá-lo de forma Ad-Hoc. Isso é o que eu fiz no passado, embora eu esteja querendo saber se existe outra maneira, como alguma maneira de ingressar que evite os CASE WHENs?

Esquema e dados de amostra abaixo, estão no PostGres, mas também estou interessado em instruções de caso dinâmico para Oracle, SQLServer, etc:

 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 respostas

  1. Best Answer

    Não tenho certeza de qual pergunta de relatório você está tentando resolver, mas parece que você pode obter o que procura simplesmente referenciando a coluna unida. Aqui estão alguns exemplos:

    /* 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

relate perguntas