sql - 连接 ON 子句的聚合

Question

我有一张 table item_table像这样:

item   age
--------------    
1      1 
1      6 
2      2    

我有另一张 table price_table像这样:

item    pricetype    price
--------------------------    
1       O             5
1       P             6
1       V             7
2       O             8
2       P             9
2       V             10

所以，我想在两个表上方进行内部连接。

select *
from item_table i
inner join price_table p
on ...

on有一些条件:

如果 平均年龄项目大于 3 ，然后我做:inner join price_table on pricetype = 'O' or pricetype = 'P'

如果没有，那么我做:inner join price_table on pricetype = 'O' or pricetype = 'P' or pricetype = 'V'

所以 on是有条件的状况。

然后我写这样的查询:

select i.item, i.type, p.pricetype, p.price
from item_table i
inner join price_table p on i.item = p.item 
    and (avg(i.age) >= 3 and p.pricetype in ('O', 'P'))
        or (avg(i.age) < 3 and p.pricetype in ('O', 'P', 'V'))

给出错误: An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
我动不了 avg至 Having因为其他条件取决于 avg .

如何编写选择查询？

Answer 1

select *
from (
    select item, avg(age) as AvgAge
    from item_table
    group by item
) ia
inner join price_table p on ia.item = p.item 
    and ((ia.AvgAge >= 3 and p.pricetype in ('O', 'P'))
        or (ia.AvgAge < 3 and p.pricetype in ('O', 'P', 'V')))

SQL Fiddle Example 1

这可以简化为:

select *
from (
    select item, avg(age) as AvgAge
    from item_table
    group by item
) ia
inner join price_table p on ia.item = p.item 
    and (p.pricetype in ('O', 'P')
        or (ia.AvgAge < 3 and p.pricetype = 'V'))

SQL Fiddle Example 2