20
3.相關的SQL語法 (10/10)
*Group運算式 (1)
*找出至少購買五次以上的顧客其平均消費金額
SQL:
SELECT avg(p_Price) AS avgprice
FROM order  
GROUP BY pId  
HAVING count(*) >= 5  
XML :
  FOR $op IN distinct(document("order.xml")//pId)
  LET $o := document("customer1")//order_record[pId = $op]
  WHERE count($o) >=5
  RETURN
      <customer1>
        $op,        <avgprice> avg($o/price) </avgprice>
     </ customer1>