sql查询-既买了产品A又买了产品B的用户,该怎么解决_Sql Server

sql查询-既买了产品A又买了产品B的用户
表结构
----------------------
id username product_id
1 张三 363
2 李四 364
3 张三 365
4 王五 363

需求：
找出既买了产品363又买了产品365的用户（即张三），sql怎么写？
数据量比较大，有没有不用表联结的写法？

------解决方案--------------------
表达不是问题，具体看你数据怎么选择和操作。

SQL code

with t1 as(select Username, product_idfrom tablenamewhere product_id = 363group by Username, product_id),t2 as(select Username, product_idfrom tablenamewhere product_id = 365group by Username, product_id)select Username from t1where Username in(select Username from t2)
------解决方案--------------------
SQL codeSELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE username=T.username  AND product_id=363) AND  EXISTS(SELECT 1 FROM TB WHERE username=T.username  AND product_id=365)
------解决方案--------------------
SQL codeselect * from  tb where username in(select username from tb group by username having count(1)=2) and product_id in('363','365')
------解决方案--------------------
select * from tb a,
(SELECT username  FROM TB WHERE product_id=363) b
(SELECT username  FROM TB WHERE product_id=365) c
where b.username=c.username and a.username=c.username
------解决方案--------------------
SELECT * FROM TB T  
WHERE EXISTS(SELECT 1 FROM TB WHERE username=T.username  AND product_id=363) AND  
  EXISTS(SELECT 1 FROM TB WHERE username=T.username  AND product_id=365)
AND T.product_id IN(365,363)

--把product_id 单独一个索引
username 单独一个
先试试看。索引有没有效有作用还要看你的数据量。数据结构这些
语句按上面的。

------解决方案--------------------
探讨
引用:

SELECT * FROM TB T
WHERE EXISTS(SELECT 1 FROM TB WHERE username=T.username AND product_id=363) AND
EXISTS(SELECT 1 FROM TB WHERE username=T.username AND product_id=365)
AND T.produc……

------解决方案--------------------
你查询363,365多少数据量?
username include(product_id)再试试

+1
------解决方案--------------------
SELECT distinct username FROM TB  
试试这个语句有多少数据