在SQL server或Oracle中使用case语句

Question

我修改了我对task#2的要求，我想知道是否有人能帮我。我试图从不在任务#1和任务3中的ITEM列中获取sum。

非常感谢。

我创建了一个简单的表和一些原始数据。我曾尝试做以下工作：

1. a.Sum记录中包含“FIRA”作为FIRA。

b.Sum记录包含“FMUA”作为FMUA。

c.Sum记录中包含“SOTA”作为SOTA。

d.Sum记录包含“PERA”作为PERA。

e.Sum记录包含“SGDA”作为SGDA。

f.Sum记录包含“TGDA”作为TGDA。

h.Sum记录包含“CRMA”作为CRMA。

将不来自任务#1和任务#3的记录合并为MULTIPLE_CLASSIFICATION.

• Sum，其余的记录不是来自于NONE_CLASSIFICATION.

的#1和#2

我能够完成任务#1和#3，但无法完成下面的任务#2是我在SQL Server和Oracle中测试的查询。

CREATE TABLE TEMP
(
  CLASSIFICATION VARCHAR (100),
  ITEM           INTEGER
)

insert into temp values ('CRMA', 66);
insert into temp values ('FIRA', 1288);
insert into temp values ('FIRA/ATEPT/DR', 3);
insert into temp values ('DR/SERA/ATEPT/FIRA', 4);
insert into temp values ('PERA', 1311);
insert into temp values ('STATE/SERA/PERA/ERS', 1);
insert into temp values ('null', 136);
insert into temp values ('PERA/DR/ATEPT', 4);
insert into temp values ('SOTA', 1);
insert into temp values ('FERA/SOTA', 1);
insert into temp values ('SOTA/SATO/DT', 1);
insert into temp values ('FMUA', 5);
insert into temp values ('SERA', 8);
insert into temp values ('SGDA', 3);
insert into temp values ('TGDA', 1);
insert into temp values ('TGDA/ATPET', 1);
insert into temp values ('ATPET', 15);
insert into temp values ('CRMA/PERA', 2);
insert into temp values ('SERA/FIRA/ATEPT/SGDA/SGD', 5);
insert into temp values ('FIRE/FIRA/SERA/DR/SOTA', 4)

-----------------------TASK #1--------------------------

SELECT
SUM (CASE WHEN CLASSIFICATION LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%CRMA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'  
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%' THEN ITEM END) AS FIRA,

SUM (CASE WHEN CLASSIFICATION LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%CRMA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%' THEN ITEM END) AS FMUA,

SUM (CASE WHEN CLASSIFICATION LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%CRMA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%' THEN ITEM END) AS SOTA,

SUM (CASE WHEN CLASSIFICATION LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%CRMA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%' THEN ITEM END) AS PERA,

SUM (CASE WHEN CLASSIFICATION LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%TGDA%'
AND CLASSIFICATION NOT LIKE '%CRMA%' THEN ITEM END) AS SGDA,

SUM (CASE WHEN CLASSIFICATION LIKE '%TGDA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%CRMA%' THEN ITEM END) AS TGDA,

SUM (CASE WHEN CLASSIFICATION LIKE '%CRMA%'
AND CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%' THEN ITEM END) AS CRMA,

-----------------------TASK #2--------------------------
SUM (CASE WHEN ( CLASSIFICATION LIKE '%FIRA%'
OR CLASSIFICATION LIKE '%FMUA%'
OR CLASSIFICATION LIKE '%SOTA%'
OR CLASSIFICATION LIKE '%PERA%'
OR CLASSIFICATION LIKE '%SGDA%'
OR CLASSIFICATION LIKE '%TGDA%'
OR CLASSIFICATION LIKE '%CRMA%') THEN ITEM END) AS MULIPLE_CLASSIFICATIONS,

-----------------------TASK #3--------------------------
SUM (CASE WHEN ( CLASSIFICATION NOT LIKE '%FIRA%'
AND CLASSIFICATION NOT LIKE '%FMUA%'
AND CLASSIFICATION NOT LIKE '%SOTA%'
AND CLASSIFICATION NOT LIKE '%PERA%'
AND CLASSIFICATION NOT LIKE '%SGDA%'
AND CLASSIFICATION NOT LIKE '%TGDA%'
AND CLASSIFICATION NOT LIKE '%CRMA%') THEN ITEM END) AS NONE_CLASSIFICATIONS
FROM TEMP

FIRA    FMUA    SOTA    PERA    SGDA    TGDA    CRMA    MULIPLE_CLASSIFICATIONS NONE_CLASSIFICATIONS

  1295  5     3       1316    3          2       66      2701                      159

Answer 1

您的任务2包括"FIRA"，它的值为1288。由于1288大于您所期望的总和，所以您的sum查询应该修改为只包含要查找的值的类型。见您的插入：

insert into temp values ('FIRA', 1288);

你的问题是：

--for task #2
SUM (CASE WHEN ( CLASSIFICATION LIKE '%FIRA%'
OR CLASSIFICATION LIKE '%FMUA%'
OR CLASSIFICATION LIKE '%SOTA%'
OR CLASSIFICATION LIKE '%PERA%'
OR CLASSIFICATION LIKE '%SGDA%'
OR CLASSIFICATION LIKE '%TGDA%'
OR CLASSIFICATION LIKE '%CRMA%') THEN ITEM END) AS MULIPLE_CLASSIFICATIONS,