我有一张数据表item_table
,它长这样:
item age
--------------
1 1
1 6
2 2
我有另一张名为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
中。您应该如何编写选择查询语句?