根据填充的列数返回记录的 SQL 查询

Question

我有一个项目表。项目 ID 存储在 ITEM 列中。所有项目都有不同的变体，分别命名为 "80"、"85"、"90"、"95"、"100"、"105"、"110"、"115"(也是现有列).如果某项商品具有特殊类型的变体，则该列(以变体类型命名)会填充一个数字。

我只想选择那些至少有 4 个变体的项目(4 个变体列用数字填充)。

ITEM    80    85    90    95    100    105    110    115
 A      1                        1                    3    <-- 3 variants
 B      2                        1                    3    <-- 3 variants
 C                        1                           3    <-- 2 variants
 D            1                                       3    <-- 2 variants
 E      1                 1                     1     1    <-- 4 variants

在此示例中，商品 E 将是唯一被选中的商品，因为它有 4 个变体。

create table itemtest (
    item varchar2(30 char),
    "80" integer,
    "85" integer,
    "90" integer,
    "95" integer,
    "100" integer,
    "105" integer,
    "110" integer,
    "115" integer
);

insert into itemtest values ('A', 1, null, null, null, 1, null, null, 3);

insert into itemtest values ('B', 2, null, null, null, 1, null, null, 3);

insert into itemtest values ('C', null, null, null, 1, null, null, null, 3);

insert into itemtest values ('D', null, 1, null, null, null, null, null, 3);

insert into itemtest values ('E', 1, null, null, 1, null, null, 1, 1);

commit;

Answer 1

避免输入繁琐的 CASE 表达式。使用 Oracle 被低估的 NVL2()为此功能!

select itemtest.*
from itemtest
where NVL2("80" , 1, 0) + NVL2("85" , 1, 0) + 
      NVL2("90" , 1, 0) + NVL2("95" , 1, 0) + 
      NVL2("100", 1, 0) + NVL2("105", 1, 0) + 
      NVL2("110", 1, 0) + NVL2("115", 1, 0) >= 4;

来自文档:

NVL2(expr1, expr2, expr3)

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

奖金。用漂亮的 UNPIVOT 子句打动(或惹恼)你的同事

我不知道为什么我没有早点想到它。事实上，您实际上是在逆透视您的列以便对它们进行计数。方法如下:

SELECT item, COUNT(*)
FROM itemtest
UNPIVOT (
  variants FOR code IN ("80", "85", "90", "95", "100", "105", "110", "115")
)
GROUP BY item
HAVING COUNT(*) >= 4
ORDER BY item

这会产生

ITEM  COUNT(*)
--------------
E            4

它是如何工作的？

这是一个简单的 UNPIVOT 语句，没有分组和聚合:

SELECT *
FROM itemtest
UNPIVOT (
  variants FOR code IN ("80", "85", "90", "95", "100", "105", "110", "115")
)

哪个产量...

ITEM  CODE  VARIANTS
--------------------
A     80    1       
A     100   1       
A     115   3       
B     80    2       
B     100   1       
B     115   3       
C     95    1       
C     115   3       
D     85    1       
D     115   3       
E     80    1       
E     95    1       
E     110   1       
E     115   1       

其余的(GROUP BY 和HAVING)只是普通的 SQL。