MySQL WITH AS用法

1. 首先，确定哪个国家的销售金额最高。
2. 然后，计算出这个国家销售金额的30%和40%的值。
3. 最后，找出其它国家的销售金额介于这两个值之间的记录。

WITH CountrySales AS (
    SELECT S.Country, SUM(P.Price) AS Money
    FROM OrderDetails AS D
    LEFT JOIN Orders AS O ON D.OrderID = O.OrderID
    LEFT JOIN Products AS P ON P.ProductID = D.ProductID
    LEFT JOIN Suppliers AS S ON S.SupplierID = P.SupplierID
    GROUP BY S.Country
)
SELECT Country, Money
FROM CountrySales
WHERE Money > (
    SELECT 0.3 * MAX(Money) 
    FROM CountrySales
) AND Money < (
    SELECT 0.4 * MAX(Money) 
    FROM CountrySales
)
ORDER BY Money DESC;

这个查询使用了一个常用表（Common Table Expression，CTE）来计算各个国家的销售金额，并在主查询中使用了子查询来筛选出满足条件的记录。