table表
user_id report_date sell buy
1 0101 2 3
1 0102 2 2
1 0103 5 5
1 0104 0 1
2 0101 2 0
2 0102 5 7
3 0105 0 1
4 0106 1 1
5 0103 7 3
1.查询该段时间内sell+buy 大于等于10的user_id所有信息，结果为
user_id report_date sell buy
1 0101 2 3
1 0102 2 2
1 0103 5 5
1 0104 0 1
2 0101 2 0
2 0102 5 7
5 0103 7 3
2.查询该段时间内sell+buy 大于等于10的report_date所有信息，结果为
user_id report_date sell buy
1 0102 2 2
1 0103 5 5
2 0102 5 7
5 0103 7 3
------解决思路----------------------
select * from table表 where USER_ID in (select USER_ID from table表 where sell+suy >= 10)
select * from table表 where report_date in (select report_date from table表 where sell+suy >= 10)
------解决思路----------------------
第一个
with t([user_id],report_date,sell,buy)
as(select 1,'0101',2,3
union all
select 1,'0102',2,2
union all
select 1,'0103',5,5
union all
select 1,'0104',0,1
union all
select 2,'0101',2,0
union all
select 2,'0102',5,7
union all
select 3,'0105',0,1
union all
select 4,'0106',1,1
union all
select 5,'0103',7,3)
select * from t where [user_id] in(select [user_id] from t where sell+buy>=10 group by [user_id])
------解决思路----------------------
第二个
with t([user_id],report_date,sell,buy)
as(select 1,'0101',2,3
union all
select 1,'0102',2,2
union all
select 1,'0103',5,5
union all
select 1,'0104',0,1
union all
select 2,'0101',2,0
union all
select 2,'0102',5,7
union all
select 3,'0105',0,1
union all
select 4,'0106',1,1
union all
select 5,'0103',7,3)
select * from t where report_date in('0102','0103') and [user_id] in(select [user_id] from t where report_date in('0102','0103') and sell+buy>=10 group by [user_id])
------解决思路----------------------
第一个：
with table1 as
(
select 1 user_id, '0101' report_date, 2 sell, 3 buy union all
select 1 user_id, '0102' report_date, 2 sell, 2 buy union all
select 1 user_id, '0103' report_date, 5 sell, 5 buy union all
select 1 user_id, '0104' report_date, 0 sell, 1 buy union all
select 2 user_id, '0101' report_date, 2 sell, 0 buy union all
select 2 user_id, '0102' report_date, 5 sell, 7 buy union all
select 3 user_id, '0105' report_date, 0 sell, 1 buy union all
select 4 user_id, '0106' report_date, 1 sell, 1 buy union all
select 5 user_id, '0103' report_date, 7 sell, 3 buy
)
select * from table1 where user_id in (select user_id from table1 where sell+buy>=10)
第二个：你给的结果中的第一条不对吧。
with table1 as
(
select 1 user_id, '0101' report_date, 2 sell, 3 buy union all
select 1 user_id, '0102' report_date, 2 sell, 2 buy union all
select 1 user_id, '0103' report_date, 5 sell, 5 buy union all
select 1 user_id, '0104' report_date, 0 sell, 1 buy union all
select 2 user_id, '0101' report_date, 2 sell, 0 buy union all
select 2 user_id, '0102' report_date, 5 sell, 7 buy union all
select 3 user_id, '0105' report_date, 0 sell, 1 buy union all
select 4 user_id, '0106' report_date, 1 sell, 1 buy union all
select 5 user_id, '0103' report_date, 7 sell, 3 buy
)
select * from table1 where sell+buy>=10