SQLserver 两表联合查询消费者消费周期记录

Question

SqlServer2018数据库，在线急需一个两表查询语句
表一记录会员数据 table1
UserName(会员名)，regDate(注册日期)，ShopName(店名),UserTel(会员手机)
表二是消费记录table2
ShopDate(消费日期),UserTel(会员手机),ShopAmount(消费金额)

需求：根据会员注册日期和店名，按照门店和注册日期统计，筛选日期之后的每个月的消费人数
统计展示格式如下：如果筛选日期是2023-05~2023-06
筛选日期内的消费人数 2023-07 2023-08 2023-09 2023-10 2023-11 2023-12
店1
店2
店3

Answer 1

要完成这个需求，首先需要明确“筛选日期”是什么。假设你想从table1筛选出注册日期在2023-05~2023-12之间的会员。

以下是一个可能的SQL查询语句，用于统计满足条件的会员在每个月的消费人数：

sql
WITH CTE AS (
    SELECT
        t2.ShopDate,
        t1.ShopName,
        COUNT(DISTINCT t2.UserTel) AS ConsumptionCount
    FROM
        table1 t1
    JOIN
        table2 t2 ON t1.UserTel = t2.UserTel
    WHERE
        t1.regDate BETWEEN '2023-05-01' AND '2023-12-31'
    GROUP BY
        t2.ShopDate, t1.ShopName
)

SELECT
    ShopName,
    SUM(CASE WHEN MONTH(ShopDate) = 7 THEN ConsumptionCount ELSE 0 END) AS '2023-07',
    SUM(CASE WHEN MONTH(ShopDate) = 8 THEN ConsumptionCount ELSE 0 END) AS '2023-08',
    SUM(CASE WHEN MONTH(ShopDate) = 9 THEN ConsumptionCount ELSE 0 END) AS '2023-09',
    SUM(CASE WHEN MONTH(ShopDate) = 10 THEN ConsumptionCount ELSE 0 END) AS '2023-10',
    SUM(CASE WHEN MONTH(ShopDate) = 11 THEN ConsumptionCount ELSE 0 END) AS '2023-11',
    SUM(CASE WHEN MONTH(ShopDate) = 12 THEN ConsumptionCount ELSE 0 END) AS '2023-12'
FROM
    CTE
GROUP BY
    ShopName;

这个查询首先使用一个CTE（公用表表达式）来获取每个月每个店的消费人数。然后，在主查询中，使用CASE语句按月份对消费人数进行分类汇总。