[A] 会员ID表
A.uid 会员id
1
2
3
4[B] 会员充值表: sj:充值时间 je:充值金额
B.id, B.uid, B.sj, B.je
1 1 2012-02-01 200.00
2 2 2012-02-01 100.00
3 1 2012-02-10 50.00
4 3 2012-02-15 100.00[C] 会员消费表: sj:消费时间 je:消费金额
C.id, C.uid, C.sj, C.je
1 1 2012-02-02 200.00
2 2 2012-02-05 100.00
3 1 2012-02-17 50.00
4 3 2012-02-18 100.00
最终我想要得到一个截止到某个日期的会员的消费和充值及余额的汇总表,如下会员ID 总充值金额 总消费金额 总余额
1 xxxx xxx xxxx
2 xxxx xxx xxxx
3 xxxx xxx xxxx
4 xxxx xxx xxxx 其中截止日期是个时间变量,比如我要查截止到2012-3-1位置的上面这个表的数据请教这条查询语句该怎么写?非常感谢。
A.uid 会员id
1
2
3
4[B] 会员充值表: sj:充值时间 je:充值金额
B.id, B.uid, B.sj, B.je
1 1 2012-02-01 200.00
2 2 2012-02-01 100.00
3 1 2012-02-10 50.00
4 3 2012-02-15 100.00[C] 会员消费表: sj:消费时间 je:消费金额
C.id, C.uid, C.sj, C.je
1 1 2012-02-02 200.00
2 2 2012-02-05 100.00
3 1 2012-02-17 50.00
4 3 2012-02-18 100.00
最终我想要得到一个截止到某个日期的会员的消费和充值及余额的汇总表,如下会员ID 总充值金额 总消费金额 总余额
1 xxxx xxx xxxx
2 xxxx xxx xxxx
3 xxxx xxx xxxx
4 xxxx xxx xxxx 其中截止日期是个时间变量,比如我要查截止到2012-3-1位置的上面这个表的数据请教这条查询语句该怎么写?非常感谢。
就是sum,group by
[A] 会员ID表
A.uid 会员id
1
2
3
4[B] 会员充值表: sj:充值时间 je:充值金额
B.id, B.uid, B.sj, B.je
1 1 2012-02-01 200.00
2 2 2012-02-01 100.00
3 1 2012-02-10 50.00
4 3 2012-02-15 100.00[C] 会员消费表: sj:消费时间 je:消费金额
C.id, C.uid, C.sj, C.je
1 1 2012-02-02 200.00
2 2 2012-02-05 100.00
3 1 2012-02-17 50.00
4 3 2012-02-18 100.00
最终我想要得到一个截止到某个日期的会员的消费和
充值及余额的汇总表,如下会员ID 总充值金额 总消费金额 总余额
1 xxxx xxx xxxx
2 xxxx xxx xxxx
3 xxxx xxx xxxx
4 xxxx xxx xxxx 其中截止日期是个时间变量,比如我要查截止到2012-3-1位置的上面这个表的数据请教这条查询语句该怎么写?非常感谢。
*/go
if object_id ('会员') is not null
drop table 会员
go
create table 会员(
UserId int
)
go
insert 会员
select 1 union all
select 2 union all
select 3 union all
select 4go
go
if object_id ('充值') is not null
drop table 充值
go
create table 充值(
Id int,
UserId int,
EndTimes datetime,
cash numeric(8,2)
)
go
insert 充值
select 1,1,'2012-02-01',200.00 union all
select 2,2,'2012-02-01',100.00 union all
select 3,1,'2012-02-10',50.00 union all
select 4,3,'2012-02-15',100.00
go
if OBJECT_ID('消费') is not null
drop table 消费
go
create table 消费(
Id int,
UserId int,
Times datetime,
cash numeric(8,2)
)
go
insert 消费
select 1,1,'2012-02-02',200.00 union all
select 2,2,'2012-02-05',100.00 union all
select 3,1,'2012-02-17',50.00 union all
select 4,3,'2012-02-18',100.00
select 会员.UserId as 会员ID,SUM(isnull(充值.cash,0)) as 总充值金额,
SUM(isnull(消费.cash,0)) as 总消费金额,
(SUM(isnull(充值.cash,0))-SUM(isnull(消费.cash,0))) as 总余额
from 会员 left join 充值
on 会员.UserId=充值.UserId
left join 消费 on 会员.UserId=消费.UserId
group by 会员.UserId
where EndTimes --时间条件
--如果截止日期为变量,可通过传参数实现
[A] 会员ID表
A.uid 会员id
1
2
3
4[B] 会员充值表: sj:充值时间 je:充值金额
B.id, B.uid, B.sj, B.je
1 1 2012-02-01 200.00
2 2 2012-02-01 100.00
3 1 2012-02-10 50.00
4 3 2012-02-15 100.00[C] 会员消费表: sj:消费时间 je:消费金额
C.id, C.uid, C.sj, C.je
1 1 2012-02-02 200.00
2 2 2012-02-05 100.00
3 1 2012-02-17 50.00
4 3 2012-02-18 100.00
最终我想要得到一个截止到某个日期的会员的消费和
充值及余额的汇总表,如下会员ID 总充值金额 总消费金额 总余额
1 xxxx xxx xxxx
2 xxxx xxx xxxx
3 xxxx xxx xxxx
4 xxxx xxx xxxx 其中截止日期是个时间变量,比如我要查截止到2012-3-1位置的上面这个表的数据请教这条查询语句该怎么写?非常感谢。
*/go
if object_id ('会员') is not null
drop table 会员
go
create table 会员(
UserId int
)
go
insert 会员
select 1 union all
select 2 union all
select 3 union all
select 4go
go
if object_id ('充值') is not null
drop table 充值
go
create table 充值(
Id int,
UserId int,
EndTimes datetime,
cash numeric(8,2)
)
go
insert 充值
select 1,1,'2012-02-01',200.00 union all
select 2,2,'2012-02-01',100.00 union all
select 3,1,'2012-02-10',50.00 union all
select 4,3,'2012-02-15',100.00
go
if OBJECT_ID('消费') is not null
drop table 消费
go
create table 消费(
Id int,
UserId int,
Times datetime,
cash numeric(8,2)
)
go
insert 消费
select 1,1,'2012-02-02',200.00 union all
select 2,2,'2012-02-05',100.00 union all
select 3,1,'2012-02-17',50.00 union all
select 4,3,'2012-02-18',100.00
select 会员.UserId as 会员ID,SUM(isnull(充值.cash,0)) as 总充值金额,
SUM(isnull(消费.cash,0)) as 总消费金额,
(SUM(isnull(充值.cash,0))-SUM(isnull(消费.cash,0))) as 总余额
from 会员 left join 充值
on 会员.UserId=充值.UserId
left join 消费 on 会员.UserId=消费.UserId
group by 会员.UserId
where EndTimes --时间条件
--如果截止日期为变量,可通过传参数实现
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp
@endtime datetime--定义传入参数,表示截止日期
as
select 会员.UserId as 会员ID,SUM(isnull(充值.cash,0)) as 总充值金额,
SUM(isnull(消费.cash,0)) as 总消费金额,
(SUM(isnull(充值.cash,0))-SUM(isnull(消费.cash,0))) as 总余额
from 会员 left join 充值
on 会员.UserId=充值.UserId
left join 消费 on 会员.UserId=消费.UserId
where EndTimes<=@endtime--指定截止日期小于传入的截止日期
group by 会员.UserId--调用存储过程:
exec p_sp '2012-02-05'
/*
会员ID 总充值金额 总消费金额 总余额
1 200.00 0.00 200.00
2 100.00 0.00 100.00
*/
use tempdbcreate table member
(
id int
)create table memberapply
(
id int,
uid int,
applytime datetime,
val money
)
create table applydetails
(
id int,
uid int,
consumetime datetime,
consumeval money
)insert into member(id) values(1)
insert into member(id) values(2)
insert into member(id) values(3)
insert into member(id) values(4)insert into memberapply(id,[uid],applytime,val) values(1,1,'2012-02-01',200.00)
insert into memberapply(id,[uid],applytime,val) values(1,2,'2012-02-01',100.00)
insert into memberapply(id,[uid],applytime,val) values(1,1,'2012-02-10',50.00)
insert into memberapply(id,[uid],applytime,val) values(1,3,'2012-02-15',100.00)insert into applydetails (id,[uid],consumetime,consumeval) values(1,1,'2012-02-02',200.00)
insert into applydetails (id,[uid],consumetime,consumeval) values(2,2,'2012-02-05',100.00)
insert into applydetails (id,[uid],consumetime,consumeval) values(3,1,'2012-02-17',50.00)
insert into applydetails (id,[uid],consumetime,consumeval) values(4,3,'2012-02-18',100.00)with mapply as
(
SELECT m.id,sum(a.val) as sum_val FROM
member as m
left join memberapply as a
on m.id=a.uid
where applytime <='20120210'
group by m.id
),
mdetails as
(
SELECT m.id,sum(d.consumeval) as sum_consumeval FROM
member as m
left join applydetails as d
on m.id=d.uid
where consumetime <='20120210'
group by m.id
)SELECT ma.id,sum_val,sum_consumeval,sum_val-sum_consumeval as balne
FROM mapply as ma
left join mdetails as me
on ma.id=me.id
这下应该对了:go
if object_id ('会员') is not null
drop table 会员
go
create table 会员(
UserId int
)
go
insert 会员
select 1 union all
select 2 union all
select 3 union all
select 4go
go
if object_id ('充值') is not null
drop table 充值
go
create table 充值(
Id int,
UserId int,
EndTimes datetime,
cash numeric(8,2)
)
go
insert 充值
select 1,1,'2012-02-01',200.00 union all
select 2,2,'2012-02-01',100.00 union all
select 3,1,'2012-02-10',50.00 union all
select 4,3,'2012-02-15',100.00
go
if OBJECT_ID('消费') is not null
drop table 消费
go
create table 消费(
Id int,
UserId int,
Times datetime,
cash numeric(8,2)
)
go
insert 消费
select 1,1,'2012-02-02',200.00 union all
select 2,2,'2012-02-05',100.00 union all
select 3,1,'2012-02-17',50.00 union all
select 4,3,'2012-02-18',100.00if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp
@endtime datetime--定义传入参数,表示截止日期
as
select a.*,ISNULL(b.消费,0) AS 消费,(ISNULL(a.充值,0)-ISNULL(b.消费,0)) as 余额
from(
select 会员.UserId as 会员ID,sum(isnull(充值.cash,0)) as 充值
from 会员 left join 充值
on 会员.UserId=充值.UserId
group by 会员.UserId
)a left join
(select 消费.UserId,sum(isnull(消费.cash,0)) as 消费
from 消费 where 消费.Times<=@endtime
group by 消费.UserId)b
on a.会员ID=b.UserId--调用存储过程:
exec p_sp '2012-02-05'
/*
会员ID 充值 消费 余额
1 250.00 200.00 50.00
2 100.00 100.00 0.00
3 100.00 0.00 100.00
4 0.00 0.00 0.00
*/
不过各位这么热心帮忙,都好几天了,不结贴也不好意思了。 非常感谢!