sql，怎么选出只购买过某个礼包和购买过某几个礼包的人数？

表类似于下面这个样子：

现在想选出购买这些礼包的人数，如下图：

sql语句应该咋写呢？

select userid,count(*) from 表名 where 礼包名称 in ('礼包名称1'，'礼包名称2'，'礼包名称n')

相同的用户ID和礼包名称只会出现一次吗？

因为比较多，所以就一个一个说，如果有错误你就指出来，可能不是所有的都是正确的。先从简单的开始。

1. 三卡都开过

group by userid having count(礼包名称) = 3

2. 三卡都没开

要 left join 用户表，然后 group by userid having count(礼包名称) = 0

3. 只卖过月卡

你需要先查出开通了只一种卡(不管什么卡)的用户

SELECT
	count(*) 
FROM
	(
	SELECT
		t.userid 
	FROM
		t 
	WHERE
		t.userid IN (只开一张卡的用户) 
		AND t.礼包名称 IN (礼包名称) 
	GROUP BY
		t.userid 
	HAVING
	count( t.礼包名称 ) = 礼包名称数量
	)

剩下的就和 3 是类似的了。

您好，我是有问必答小助手，你的问题已经有小伙伴为您解答了问题，您看下是否解决了您的问题，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

select 
case when a.userid is not null and b.userid is null and c.userid is null then '只买过月卡'
     when a.userid is null and b.userid is not null and c.userid is null then '只买过季卡'
     when a.userid is null and b.userid is null and c.userid is not null then '只买过永久卡'
     when a.userid is not null and b.userid is not null and c.userid is null then '月卡&季卡'
     when a.userid is not null and b.userid is null and c.userid is not null then '月卡&永久卡'
     when a.userid is null and b.userid is not null and c.userid is not null then '季卡&永久卡'
     when a.userid is not null and b.userid is not null and c.userid is not null then '三卡都开过'
     when a.userid is null and b.userid is null and c.userid is null then '三卡都未开过'
end 礼包,
count(1) 人数
from
table a
full join
table b on a.userid = b.userid and b.礼包名称 = '季卡'
full join
table c on a.userid = b.userid and b.userid = c.userid and c.礼包名称 = '永久卡'
full join
table d on a.userid = d.userid and b.userid = d.userid and c.userid = c.userid and d.礼包名称 not in ('月卡','季卡','永久卡')
where a.礼包名称 = '月卡'
group by 
case when a.userid is not null and b.userid is null and c.userid is null then ''
     when a.userid is null and b.userid is not null and c.userid is null then ''
     when a.userid is null and b.userid is null and c.userid is not null then ''
     when a.userid is not null and b.userid is not null and c.userid is null then ''
     when a.userid is not null and b.userid is null and c.userid is not null then ''
     when a.userid is null and b.userid is not null and c.userid is not null then ''
     when a.userid is not null and b.userid is not null and c.userid is not null then ''
     when a.userid is null and b.userid is null and c.userid is null then ''
end
;

有疑惑请随时沟通，烦请采纳！

非常感谢您使用有问必答服务，为了后续更快速的帮您解决问题，现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中，希望能得到您的支持与协助！

速戳参与调研>>>https://t.csdnimg.cn/Kf0y