SQL按某属性求和sum

[luolingzhichen]

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

[wtujedp]

select userid from table where report_date between 开始时间 and 结束时间 group by userid having sum(sell+buy)>=10 试试这个行不行

[misterliwei]

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)

[luolingzhichen]

引用 1 楼 wtujedp 的回复:

select userid from table where report_date between 开始时间 and 结束时间 group by userid having sum(sell+buy)>=10 试试这个行不行

[code=SELECT report_date,user_id,sum(buy),sum(sell) FROM table GROUP BY report_date,user_id,buy,sell having sum(buy+sell)>=10][/code] 这样出来的是第一种结果，我换了下user_id，和report_date的顺序还是一样的结果，而且按你那样子，信息肯定不全

[luolingzhichen]

SELECT  
report_date,user_id,sum(buy),sum(sell)
FROM table
GROUP BY report_date,user_id,buy,sell
having sum(buy+sell)>=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])

[zbdzjx]

第一个：

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

[luolingzhichen]

引用 6 楼 lovelj2012 的回复:

第二个

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])

忘了可以用in语句，我回去试试，