SQL按某属性求和sum

luolingzhichen 2015-07-08 04:26:45
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
...全文
372 8 点赞 打赏 收藏 举报
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
luolingzhichen 2015-07-08
引用 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语句,我回去试试,
  • 打赏
  • 举报
回复
zbdzjx 2015-07-08
第一个:
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
  • 打赏
  • 举报
回复
江南小鱼 2015-07-08
第二个
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])
  • 打赏
  • 举报
回复
江南小鱼 2015-07-08
第一个

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])
  • 打赏
  • 举报
回复
luolingzhichen 2015-07-08
SELECT  
report_date,user_id,sum(buy),sum(sell)
FROM table
GROUP BY report_date,user_id,buy,sell
having sum(buy+sell)>=10
  • 打赏
  • 举报
回复
luolingzhichen 2015-07-08
引用 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的顺序还是一样的结果,而且按你那样子,信息肯定不全
  • 打赏
  • 举报
回复
misterliwei 2015-07-08

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)
  • 打赏
  • 举报
回复
wtujedp 2015-07-08
select userid from table where report_date between 开始时间 and 结束时间 group by userid having sum(sell+buy)>=10 试试这个行不行
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-08 04:26
社区公告
暂无公告