27,579
社区成员
发帖
与我相关
我的任务
分享
select ROW_NUMBER() over(order by getdate()) as [序号],
username as [用户名],
userid as [用户ID],
(select COUNT(1) from @usprecord where userid=t.userid) as [抽奖次数],
(select COUNT(1) from @usprecord where userid=t.userid and prizeid>0) as [中奖次数],
(select COUNT(1) from @usprecord where userid=t.userid and prizeid<=0) as [没中奖次数],
vmoney as [账户余额(爱心币)],
(select COUNT(1) from @chargerecord where userid=t.userid and payresult='支付成功') as [充值记录/条]
from @userinfo t
where userid in(select distinct userid from @usprecord)
加最后一行判断declare @userinfo table(userid int, username varchar(20), vmoney int)
declare @usprecord table(id int, prizeid int, userid int)
declare @chargerecord table(id int, userid int, amt int, cardname varchar(100), payresult varchar(100))
insert @userinfo
select 10001 ,'张三', 20
union all select 10002 ,'李四', 100
union all select 10003 ,'王五', 80
insert @usprecord
select 1, -1, 10001
union all select 2, 2, 10003
union all select 3, 3, 10001
union all select 4, -1, 10002
union all select 5, -1, 10002
insert @chargerecord
select 1, 10001, 50 ,'神州行', '支付成功'
union all select 2, 10002, 100 ,'联通卡', '支付成功'
union all select 3, 10003, 30 ,'神州行', '支付失败'
select ROW_NUMBER() over(order by getdate()) as [序号],
username as [用户名],
userid as [用户ID],
(select COUNT(1) from @usprecord where userid=t.userid) as [抽奖次数],
(select COUNT(1) from @usprecord where userid=t.userid and prizeid>0) as [中奖次数],
(select COUNT(1) from @usprecord where userid=t.userid and prizeid<=0) as [没中奖次数],
vmoney as [账户余额(爱心币)],
(select COUNT(1) from @chargerecord where userid=t.userid and payresult='支付成功') as [充值记录/条]
from @userinfo t
--结果:
序号 用户名 用户ID 抽奖次数 中奖次数 没中奖次数 账户余额(爱心币) 充值记录/条
1 张三 10001 2 1 1 20 1
2 李四 10002 2 0 2 100 1
3 王五 10003 1 1 0 80 0