如何在所有聚合行中检查某个值?

假设我有三张桌子: user , group 和 xref ,一个给他们提供多对多RI的表。

我可能想看看每个用户所属的组:

select
    user.user_id,
    user.user_name,
    count(*) as group_count
from
    user
        inner join xref on user.user_id = xref.user_id
        inner join group on group.group_id = xref.group_id
group by user.user_id, user.user_name

目前一切正常。但是,如果我需要一些额外的信息呢?我在报告,我想知道每个用户是开发人员还是内容经理。现在,反模式出现了:

select
    user.user_id,
    user.user_name,
    count(*) as group_count,
    max( case group.group_name when 'Developers' then 'Y' else null end )
        as is_dev
    max( case group.group_name when 'Content Management' then 'Y' else null end )
        as is_cm
from
    user
        inner join xref on user.user_id = xref.user_id
        inner join group on group.group_id = xref.group_id
group by user.user_id, user.user_name

这是可行的,并产生了预期的结果,但感觉非常错误。我做了什么 希望 问甲骨文:

我在说什么 事实上

“对于每个用户,显示他们所在的组数。另外,对于每个用户的所有组名,请向我显示该组生成的最大值 case “表达式。”

这是一个反模式的原因是我基本上依赖于 Y 在上面“冒泡” null 当使用 max()

基本上,我希望我能写的问题是:

select
    user.user_id,
    user.user_name,
    count(*) as group_count,
    any(group.group_name, 'Developers', 'Y', null) as is_dev,
    any(group.group_name, 'Content Management', 'Y', null) as is_cm
from
    user
        inner join xref on user.user_id = xref.user_id
        inner join group on group.group_id = xref.group_id
group by user.user_id, user.user_name

我一直在筛选选择,似乎有一些潜力:

• first_value 可以,但我不知道如何限制相应的 partition 窗口到右边的行。
• 解析函数 over 条款可能有用,但我 我想折叠我分组的列,这样看起来就不太合适了。
• 令人气愤的是,似乎有一个 any here ,但它只存在于一种神秘的方言中,叫做Oracle OLAP DML,我不认为我只能在10g上使用SQL来访问它 确切地

我只有这些了。有什么想法吗?

我知道有两个非常简单的想法,“在代码中实现”或“在PL/SQL中实现”,但那是欺骗。:-)