wTrying在Oracle中编写CASE语句。不确定如何添加此限制

Question

所以这张桌子-

create table inventoryItem (
       itemNum    varchar(10) not null
              references itemType(itemNum) on delete cascade,
       code   varchar(20) not null,
       qtyInstock float       not null,
       itemColor  varchar(15),
       itemSize   float,
       primary key (itemNum, code)
);

这些都是

insert into inventoryItem values ('A0', 'pbk', 30, 'black', 3.0);
insert into inventoryItem values ('B1', 'hbk', 50, 'white', 4.0);
insert into inventoryItem values ('A0', 'hbk', 25, 'green', 4.0);
insert into inventoryItem values ('C2', 'hbk', 20, 'black', 3.0);
insert into inventoryItem values ('C1', 'ebk', 70, 'black', 4.0);
insert into inventoryItem values ('A3', 'pbk', 30, 'black', 2.0);
insert into inventoryItem values ('A2', 'pbk', 50, 'green', 3.0);
insert into inventoryItem values ('A0', 'ebk', 15, 'green', 4.0);
insert into inventoryItem values ('C2', 'pbk', 30, 'black', 3.0);

我试图编写一个子查询，首先选择itemNum、itemColor、itemSize以及每一次发生的股票数量。之后，CASE语句需要返回一个结果。让我们说“快乐”，如果项目编号没有出现超过一次，以相同的颜色和大小。否则“悲伤”。我遇到的问题是如何在Oracle中说明这一点。这是完成的第一部分-

SELECT LISTAGG(itemnum, ', ') WITHIN GROUP (ORDER BY itemnum) itemNum,
itemsize, itemcolor, SUM(qtyinstock) qty_of_combination
FROM inventoryitem
GROUP BY itemsize, itemcolor;

这是我对案件陈述的糟糕尝试-

SELECT LISTAGG(itemNum, ', ') WITHIN GROUP (ORDER BY itemNum) itemNum,
itemSize, itemColor, SUM(qtyinstock) qty_of_combination, (
    SELECT itemNum, itemColor, itemSize, 
        CASE WHEN COUNT(itemNum) <= 1 THEN 'BR-2 Satisifed'
        ELSE 'BR-2 Violated'
        END AS itemNumRules
    FROM inventoryItem
    )
FROM inventoryItem
GROUP BY itemSize, itemColor;

这个查询完成了第一部分，但我不知道我到底需要在这个示例语句中写些什么。任何帮助都是非常感谢的！

Answer 1

请检查一下：

SELECT
    COUNT(T1.itemNum),
    LISTAGG(T1.itemNum, ', ') WITHIN GROUP (
    ORDER BY T1.itemNum) itemNum,
    T1.itemSize,
    T1.itemColor,
    SUM(T1.qtyinstock) qty_of_combination,
    T2.itemNumRules
FROM
    inventoryItem T1
INNER JOIN (
    SELECT
        itemNum,
        CASE
            WHEN COUNT(itemNum) < 2 THEN 'BR-2 Satisifed'
            ELSE 'BR-2 Violated'
        END AS itemNumRules
    FROM
        inventoryItem
    GROUP BY
        itemNum) T2 ON
    T1.itemNum = T2.itemNum
GROUP BY
    T1.itemSize,
    T1.itemColor,
    T2.itemNumRules;

希望它能帮上忙

Answer 2

由于你没有公布你的预期结果，一个简单的计数分析功能是我理解你需要的。-

SELECT LISTAGG(itemnum, ', ') WITHIN GROUP (ORDER BY itemnum) itemNum,
       itemsize, 
       itemcolor, 
       SUM(qtyinstock) qty_of_combination, 
       CASE WHEN cnt <= 1 THEN 'BR-2 Satisifed' ELSE 'BR-2 Violated' END itemNumRules
  FROM (SELECT itemnum, itemsize, itemcolor, qtyinstock, 
               COUNT(*) OVER(partition by itemsize, itemcolor) cnt
          FROM inventoryitem)
 GROUP BY itemsize, 
          itemcolor, 
          cnt;

演示。