某商家有网店和实体店铺两种销售渠道，其销售数据分别存储在 SALES_ONLINE 和 SALES_STORE 两个表中。 CUSTOMERID：客户ID ORDERDATE：订单日期 PRODUCTID：产品ID SELLERID：销售人员ID AMOUNT：销售额 数据库表1网店：SALES_ONLINE： ID CUSTOMERID ORDERDATE PRODUCTID AMOUNT 1 HANAR 2023/01/01 17 3868.6 2 ERNSH 2023/01/03 42 1005.9 3 LINOD 2023/01/06 64 400.0 4 OTTIK 2023/01/07 10 1194.0 5 OCEAN 2023/01/09 41 319.2 … … … … … 数据库表2实体店：SALES_STORE： ID CUSTOMERID ORDERDATE SELLERID PRODUCTID AMOUNT 1 EASTC 2023/01/01 1 27 3063.0 2 ERNSH 2023/01/02 8 70 2713.5 3 MAGAA 2023/01/03 2 74 1675.0 4 SAVEA 2023/01/07 7 45 2018.2 5 FOLIG 2023/01/08 8 30 1622.4 … … … … … … 3.查询 2023 年每种产品的总销售额？(SQL) 4.查询 2023年在网店和实体店都有消费的客户有哪些？(SQL) 5.统计 2023 年网店每个月销售额都排前 10 名的客户名称。(SQL) 6.查询 2023 年哪些产品在网店累计销售额超过 10000，或者在实体店销售次数大于 5 次。(SQL) 7.查询在实体店消费总金额超过 1000，在网店没有消费过的客户有哪些？(SQL) 8.查询客户 RATTC，在 2023年是否排进过实体店单月销售额的前三名。(SQL)

3. 查询 2023 年每种产品的总销售额？(SQL)

SELECT PRODUCTID, SUM(AMOUNT) AS TOTAL_SALES
FROM (
SELECT PRODUCTID, AMOUNT
FROM SALES_ONLINE
WHERE YEAR(ORDERDATE) = 2023
UNION ALL
SELECT PRODUCTID, AMOUNT
FROM SALES_STORE
WHERE YEAR(ORDERDATE) = 2023
) AS SALES
GROUP BY PRODUCTID;

4. 查询 2023 年在网店和实体店都有消费的客户有哪些？(SQL)

SELECT CUSTOMERID
FROM (
SELECT CUSTOMERID, COUNT(DISTINCT SELLERID) AS SELLER_COUNT
FROM (
SELECT CUSTOMERID, SELLERID
FROM SALES_ONLINE
WHERE YEAR(ORDERDATE) = 2023
UNION ALL
SELECT CUSTOMERID, SELLERID
FROM SALES_STORE
WHERE YEAR(ORDERDATE) = 2023
) AS SALES
GROUP BY CUSTOMERID
) AS CUSTOMER_SELLER_COUNT
WHERE SELLER_COUNT = 2;

5. 统计 2023 年网店每个月销售额都排前 10 名的客户名称。(SQL)

SELECT MONTH(ORDERDATE) AS MONTH, CUSTOMERID, SUM(AMOUNT) AS TOTAL_SALES
FROM SALES_ONLINE
WHERE YEAR(ORDERDATE) = 2023
GROUP BY MONTH(ORDERDATE), CUSTOMERID
HAVING TOTAL_SALES IN (
SELECT TOP 10 SUM(AMOUNT)
FROM SALES_ONLINE
WHERE YEAR(ORDERDATE) = 2023
GROUP BY MONTH(ORDERDATE)
)
ORDER BY MONTH(ORDERDATE), TOTAL_SALES DESC;

6. 查询 2023 年哪些产品在网店累计销售额超过 10000，或者在实体店销售次数大于 5 次。(SQL)

SELECT PRODUCTID
FROM (
SELECT PRODUCTID, SUM(AMOUNT) AS TOTAL_SALES_ONLINE
FROM SALES_ONLINE
WHERE YEAR(ORDERDATE) = 2023
GROUP BY PRODUCTID
HAVING TOTAL_SALES_ONLINE > 10000
UNION ALL
SELECT PRODUCTID, COUNT(*) AS TOTAL_SALES_STORE
FROM SALES_STORE
WHERE YEAR(ORDERDATE) = 2023
GROUP BY PRODUCTID
HAVING TOTAL_SALES_STORE > 5
) AS SALES
GROUP BY PRODUCTID;

7. 查询在实体店消费总金额超过 100，在网店没有消费过的客户有哪些？(SQL)

SELECT DISTINCT CUSTOMERID
FROM SALES_STORE
WHERE CUSTOMERID NOT IN (
SELECT CUSTOMERID
FROM SALES_ONLINE
)
GROUP BY CUSTOMERID
HAVING SUM(AMOUNT) > 100;

8. 查询客户 RATTC，在 2023 年是否排进过实体店单月销售额的前三名。(SQL)

SELECT *
FROM (
SELECT CUSTOMERID, MONTH(ORDERDATE) AS MONTH, SUM(AMOUNT) AS TOTAL_SALES,
ROW_NUMBER() OVER (PARTITION BY MONTH(ORDERDATE) ORDER BY SUM(AMOUNT) DESC) AS RANK
FROM SALES_STORE
WHERE YEAR(ORDERDATE) = 2023 AND CUSTOMERID = 'RATTC'
GROUP BY CUSTOMERID, MONTH(ORDERDATE)
) AS RATTC_SALES
WHERE RANK <= 3;