学习的过程中遇到的查询问题不会，帮我解答一下

[sqlsuper1]

我把SQL该学的基本功能都学了，可是遇到这几个查询问题脑子还是绕不过来，希望大神们帮我解答这几个问题，多谢了
表格如图所示，有以下4个问题：
1.请计算每一位客人的总购买金额（Amount）,总购买订单数，总购买产品件数（Quantity）,同一个客人同一天的的订单算做一单，并筛选出总购买金额大于等于800的客人，按金额降序排列。

2.请给出每个城市（City）的总店铺数，总购买人数和总购买金额（Amount）,包含无购买记录的城市。

3.请查找购买过CategoryA产品的客人，并计算每一位客人的平均订单金额（Amount）,一个订单编号（OrderID）算作一单。

4请查找每个城市（City）购买金额排名第二的客人，列出其购买城市，姓名和购买金额。

[二月十六]

1、

SELECT
    [ORDER].Name,
    SUM(Amount)   AS Amount,
    SUM(Quantity) AS Quantity,
    COUNT(DISTINCT OrderDate)
FROM
    [ORDER]
    JOIN
        product
            ON [ORDER].product = product.product
    JOIN
        store
            ON [ORDER].store = store.store
GROUP BY
    [ORDER].Name
HAVING
    SUM(Amount) >= 800;

[二月十六]

2、

SELECT
    store.City,
    COUNT(DISTINCT store.store)   AS 总店铺数,
    COUNT(DISTINCT [ORDER].Name) AS 总购买人数,
	SUM(Amount) AS 总购买金额
    
FROM
    store
    LEFT JOIN
        [ORDER]
            ON [ORDER].store = store.store
    JOIN
        product
            ON [ORDER].product = product.product
GROUP BY
    store.City

[二月十六]

3、

SELECT
    Name,
    SUM(Amount) / COUNT(DISTINCT OrderId) AS 平均订单金额
FROM
    [order]
WHERE
    Name IN (
                SELECT
                    [ORDER].Name
                FROM
                    [ORDER]
                    JOIN
                        product
                            ON [ORDER].product = product.product
                WHERE
                    Category = 'CategoryA'
            )
GROUP BY
    Name;

[sqlsuper1]

引用 3 楼 sinat_28984567 的回复:

3、

SELECT
    Name,
    SUM(Amount) / COUNT(DISTINCT OrderId) AS 平均订单金额
FROM
    [order]
WHERE
    Name IN (
                SELECT
                    [ORDER].Name
                FROM
                    [ORDER]
                    JOIN
                        product
                            ON [ORDER].product = product.product
                WHERE
                    Category = 'CategoryA'
            )
GROUP BY
    Name;

大神第4题我也不会呀，是先排名，然后筛选顺序号吗？

[吉普赛的歌]

4 :

SELECT DISTINCT
       s.[City],
       t.[Name],
       t.[sumAmout]
FROM   (
           SELECT [Name],
                  SUM([Amout]) AS sumAmout,
                  ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY SUM([Amout]) DESC) AS rid
           FROM   [Order]
           GROUP BY
                  [Name]
       )                   AS t
       INNER JOIN [Order]  AS o
            ON  t.[Name] = o.[Name]
       INNER JOIN [Store]  AS s
            ON  o.[Store] = s.[Store]
WHERE  t.rid = 2

[二月十六]

4、

SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY
                                   city
                               ORDER BY
                                   Amount DESC
                              ) rn
        FROM
            (
                SELECT
                    [ORDER].Name,
                    store.City,
                    SUM(Amount) AS Amount
                FROM
                    [ORDER]
                    JOIN
                        store
                            ON [ORDER].store = store.store
                GROUP BY
                    [ORDER].Name,
                    store.City
            ) t
    ) t1
WHERE
    rn = 2;

[二月十六]

4我其实是写了的，但是CSDN不允许连续4次回帖，我也真是醉了……另外这些都没测试，你自己试一下吧