SQL Server计算商品购买记录

SQL Server计算商品购买记录

数据如图

实现需求如图所示

实现结果的SQL语句如下

WITH a AS (
	SELECT '1号' AS 卖家, '1号商品' AS 商品, '已付款' AS 状态, '2024-08-12 12:01:02' AS 下单时间 UNION ALL
	SELECT '1号' AS 卖家, '1号商品' AS 商品, '已付款' AS 状态, '2024-08-12 12:01:07' AS 下单时间 UNION ALL
	SELECT '2号' AS 卖家, '1号商品' AS 商品, '已付款' AS 状态, '2024-08-12 12:01:02' AS 下单时间 UNION ALL
	SELECT '3号' AS 卖家, '1号商品' AS 商品, '已付款' AS 状态, '2024-08-12 12:01:02' AS 下单时间 UNION ALL
	SELECT '3号' AS 卖家, '2号商品' AS 商品, '已付款' AS 状态, '2024-08-12 12:01:02' AS 下单时间 UNION ALL
	SELECT '1号' AS 卖家, '2号商品' AS 商品, '待付款' AS 状态, '2024-08-12 12:01:02' AS 下单时间
)

SELECT 商品,
       状态,
		 CASE WHEN 状态 = '已付款' THEN COUNT(DISTINCT 卖家) ELSE NULL END AS 付款人数,
       CASE WHEN 状态 = '已付款' THEN
		 	SUM(CASE WHEN DATEPART(hour,下单时间) = 12 THEN 1 ELSE NULL END)
		 ELSE NULL END AS [12点下单数],
       SUM(CASE WHEN 状态 = '待付款' THEN 1 ELSE NULL END) AS 未下单次数,
       CASE WHEN 状态 = '已付款' THEN
		 STUFF((
           SELECT DISTINCT ',' +卖家 FROM a AS subquery
           WHERE subquery.状态 = '已付款' AND subquery.商品 = a.商品
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ELSE NULL END AS 付款卖家,
       
       CASE WHEN 状态 = '待付款' THEN 
		 STUFF((
           SELECT DISTINCT ',' + 卖家 FROM a AS subquery
           WHERE subquery.状态 = '待付款' AND subquery.商品 = a.商品
           FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ELSE NULL END AS 待付款卖家,
       
       CASE WHEN 状态 = '待付款' THEN NULL
            ELSE STUFF((
                SELECT DISTINCT ',' + 卖家 + '卖家' + '(' + CAST(下单次数 AS VARCHAR(10)) + ')'+商品
                FROM (
					SELECT 商品,状态,卖家,COUNT(*) AS 下单次数 FROM a GROUP BY 商品,状态,卖家
					-- SELECT *,CONCAT( COUNT(*) OVER (PARTITION BY 商品, 状态, 卖家),'次购买') AS 下单次数 FROM a
                ) AS 订单计数
                WHERE 订单计数.状态 = '已付款' AND 订单计数.商品 = a.商品
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
       END AS 付款卖家及下单次数--,GETDATE() AS 统计时间段
FROM a
GROUP BY 商品,状态
ORDER BY 商品,状态 DESC;

SQL语句执行结果展示