执行按连接表进行聚合和分组的查询

Question

目前我正面临着SQL的挑战，我想知道我的方法是否正确。让我们考虑以下简化的数据模型：

Table CAT:
----------
ID
COLOR

Table DOMESTIC_CAT:
-------------------
CAT_ID
DOMESTIC_ATTRIBUTE

Table PERSIAN_CAT:
------------------
CAT_ID
PERSIAN_ATTRIBUTE

让我们假设表中有以下数据：

Table CAT:
ID   COLOR
--------------
1    'BLACK'
2    'WHITE'
3    'BLACK'
4    'WHITE'
5    'BLACK'
6    'RED'
7    'WHITE'
8    'WHITE'
9    'RED'
10   'BLACK'

Table DOMESTIC_CAT:
CAT_ID    DOMESTIC_ATTRIBUTE
----------------------------
1         'Domestic1'
2         'Domestic2'
3         'Domestic3'
7         'Domestic4'
8         'Domestic5'

Table PERSIAN_CAT
CAT_ID    PERSIAN_ATTRIBUTE
---------------------------
4         'Persian1'
5         'Persian2'
6         'Persian3'
9         'Persian4'
10        'Persian5'

我想执行一个聚合查询，结果如下：

CAT_TYPE       CAT_COLOR   COUNT
---------------------------------
'DOMESTIC_CAT'    'BLACK'       2
'DOMESTIC_CAT'    'WHITE'       3
'PERSIAN_CAT'     'WHITE'       1
'PERSIAN_CAT'     'BLACK'       2
'PERSIAN_CAT'     'RED'         2

正如您所看到的，我希望通过以下值对'count'-result进行分组：-事实，给定的猫是家猫还是波斯猫-猫的颜色

第一个是困难的事情--实际上我根本不知道是否有可能执行"group by joined“？我快崩溃了，但是找不到任何解决方案:(实际使用的RDBMS是Oracle11。

Answer 1

您可以使用从cat到其他两个表的外部连接，并确定哪个表匹配，并使用它填充cat_type列：

select case when dc.cat_id is not null then 'DOMESTIC_CAT'
    when pc.cat_id is not null then 'PERSIAN_CAT' end as cat_type,
  c.color,
  count(*) as "COUNT"
from cat c
left join domestic_cat dc on dc.cat_id = c.id
left join persian_cat pc on pc.cat_id = c.id
group by case when dc.cat_id is not null then 'DOMESTIC_CAT'
    when pc.cat_id is not null then 'PERSIAN_CAT' end,
  c.color
order by 1,2;

SQL Fiddle。

根据您的实际问题，这可能比内部连接/联合选项执行得更好，但您可能需要同时尝试这两个选项，看看哪一个更好(更快、更高效、更可维护……)。

Answer 2

只管去做

SELECT 'DOMESTIC_CAT', c.color, count(*)
FROM domestic_cat d INNER JOIN cat c ON c.id = d.cat_id
GROUP BY c.color
UNION ALL
SELECT 'PERSIAN_CAT' .... the same for the other table

Answer 3

另一种方法(Oracle 11g及更高版本)：

select cat_type
     , color
     , cat_cnt
  from (select c.color
             , count(dc.domestic_attribute) as domestic_cat
             , count(pc.persian_attribute)  as persian_cat
         from cat c
         left join domestic_cat dc
           on (c.id1 = dc.cat_id)
         left join persian_cat  pc
           on (c.id1 = pc.cat_id)
        group by c.color
        )
unpivot(
  cat_cnt for  cat_type in (domestic_cat, persian_cat)
)
order by cat_type

结果：

CAT_TYPE           COLOR    CAT_CNT
------------------------------------
DOMESTIC_CAT    RED         0
DOMESTIC_CAT    WHITE       3
DOMESTIC_CAT    BLACK       2
PERSIAN_CAT     WHITE       1
PERSIAN_CAT     BLACK       2
PERSIAN_CAT     RED         2