数据如下：

问：查找所有购买CategoryA的顾客,并计算每位客人的平均订单金额（Amount）,一个订单编号（orderID）算一单。这个是啥意思？咋做呢？

答：1、首先找到购买CategoryA的顾客

SELECT 
        name
FROM
        orderd
LEFT JOIN
        product 
ON 
        orderd.product = product.product
WHERE
        category = 'categorya'
GROUP BY name

2、将订单编号相同订单合并

SELECT 
        SUM(amount * quantity) odamount, name
FROM
        orderd
GROUP BY 
        orderid

3、对符合条件的客户的订单求平均

SELECT 
    name, AVG(odamount) Amount
FROM
    (SELECT 
        SUM(amount * quantity) odamount, name
    FROM
        orderd
    GROUP BY orderid) t
WHERE
    name IN (SELECT 
            name
        FROM
            orderd
                LEFT JOIN
            product ON orderd.product = product.product
        WHERE
            category = 'categorya'
        GROUP BY name)
GROUP BY name;