Ollivander's Inventory(连接查询、单表双实例、子查询)

两张表

wands表：（id, code, coins_needed, power)

wands_property表：(code, age, is_evil)

两张表通过code关联

查询要求：

1.对于(power,age)对，是非evil，coins_needed最小,

2.power降序，age降序

思路：

1.wands，wands_property表作内连接生成关系表

2.coins_needs要求是最小值 

sql语句：

select a.id, b.age, a.coins_needed, a.power from 
		wands as a inner join wands_property as b 
						on a.code = b.code where b.is_evil = 0
		and a.coins_needed = (
								select min(wands.coins_needed) 
                                from wands inner join wands_property 
									on wands.code = wands_property.code 
                                    where wands.power = a.power and wands_property.age = b.age
								)
		order by a.power desc, b.age desc;