主页 / dba / 问题 / 72006
Accepted
Philip Tenn
Asked: 2014-07-22 13:14:37 +0800 CST

Oracle 11g 中的聚合函数/查询

  • 772

我在 Oracle 11g 工作。

我整天都在绞尽脑汁想弄清楚如何编写特定的 SQL 语句（我是 Java/.NET 开发人员，不会认为自己是 SQL 专家）

我已经编写了一个内联视图（包含大量LEFT OUTER JOINS和CASE语句）来设置以下场景：

ID (non-unique)  |  Received 
----------------------------
        1               Y
        1               N
        2               N
        2               N
        2               Y
        3               N
        3               Y

我需要查询内联视图：

如果每个 ID 至少有 1 行带有“Y”，则返回“Y”。

否则返回'N'。

因此，对于上述情况，我会返回“Y”。**

我认为聚合函数会起作用，并且我一直在阅读 GROUP BY、ANY/ALL 和 Count。然而，我没有看到

不要求任何人为我编写 SQL ... 如果有人甚至可以建议我可以阅读/运行的功能或技术或文章。我想我对复杂的 SQL 了解不够，不知道要搜索什么。

如果这不可能写，我想我可以考虑把它作为一个 PL/SQL 函数来做。我知道我可以使用游标并使用变量跟踪事物。但是，我认为将其编写为纯 SQL 会更有效率（似乎记得 Oracle 优化器无法查看函数/过程）。

非常感谢您，

菲利普

oracle aggregate

2 个回答

  1. Best Answer

    简单但“脏”：

    with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 3 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
Y

    更改输入（例如，在最后一行将 id 更改为 4）：

    with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 4 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
N
    • 2

  2. 如果我正确理解你的逻辑，两级聚合应该可以满足你的需要。您不需要 PL/SQL 或客户端处理（即：.NET）。

    SELECT MIN(Received) Received FROM (
  SELECT ID, MAX(Received) Received FROM your_view GROUP BY ID
);

    http://sqlfiddle.com/#!4/b2f85/3/0

    或者@Balazs 风格更简洁，没有子查询的两级聚合：

    SELECT MIN(MAX(Received)) Received FROM your_view GROUP BY ID;
    • 1

相关问题