求sql语句 查询三张表的写法？

[魔拉宝]

有三张表，如下：
表一：user (会员资料表) 字段如下：
uid usort username money
111 01 张三 200
112 01 李四 550
113 02 王五 168
114 01 陈七 860

表二：inmoney (会员充值表) 字段如下：
id uid inmoney indate
1 111 100 2010-01-10
2 112 200 2010-01-12
3 113 100 2010-01-15
4 111 200 2010-02-18
6 114 500 2010-03-12
7 113 400 2010-03-25
8 112 800 2010-04-10
9 114 600 2010-05-20


表三：outmoney (会员消费表) 字段如下：
id uid outmoney outdate
1 111 100 2010-01-26
2 113 60 2010-02-11
3 112 250 2010-02-22
4 112 200 2010-03-12
5 114 100 2010-03-20
6 113 172 2010-04-16
7 114 140 2010-05-28

根据这三张表，我想sql查询得出的结果如下：

查询条件：日期段：2010-01-01 至 2010-06-20 ，查询此时间段时的所有会员的充值金额和消费金额总计是多少？

会员类别(usort) 会员编号(uid) 会员名称(username) 累计充值金额(inmoney) 累计消费金额(outmoney) 卡余额(money)
01 111 张三 300 100 200
01 112 李四 1000 450 550
02 113 王五 500 232 168
01 114 陈七 1100 240 860


请问写SQL="select *****" 怎么写？谢谢了！！！

在线等！

[yi314650291]

xuexi

[nightmaple]

select T1.usort,T1.uid,T1.username,a.inmoney,b.outmoney,T1.money from T1 
left join (
select uid,SUM(inmoney) as inmoney from T2 
where indate between '2010-01-01' and '2010-06-20'
group by uid) a on T1.uid=a.uid
left join (
select uid,SUM(outmoney) as outmoney from T3 
where outdate between '2010-01-01' and '2010-06-20'
group by uid) b on T1.uid=b.uid

[gongjie416]

学习了。

[魔拉宝]

三张表唯一关联的字段是：uid （会员编号）

[水族杰纶]

if not object_id('T1') is null
	drop table T1
Go
Create table T1([uid] int,[usort] nvarchar(2),[username] nvarchar(2),[money] int)
Insert T1
select 111,N'01',N'张三',200 union all
select 112,N'01',N'李四',550 union all
select 113,N'02',N'王五',168 union all
select 114,N'01',N'陈七',860
Go
if not object_id('T2') is null
	drop table T2
Go
Create table T2([id] int,[uid] int,[inmoney] int,[indate] Datetime)
Insert T2
select 1,111,100,'2010-01-10' union all
select 2,112,200,'2010-01-12' union all
select 3,113,100,'2010-01-15' union all
select 4,111,200,'2010-02-18' union all
select 6,114,500,'2010-03-12' union all
select 7,113,400,'2010-03-25' union all
select 8,112,800,'2010-04-10' union all
select 9,114,600,'2010-05-20'
if not object_id('T3') is null
	drop table T3
Go
Create table T3([id] int,[uid] int,[outmoney] int,[outdate] Datetime)
Insert T3
select 1,111,100,'2010-01-26' union all
select 2,113,60,'2010-02-11' union all
select 3,112,250,'2010-02-22' union all
select 4,112,200,'2010-03-12' union all
select 5,114,100,'2010-03-20' union all
select 6,113,172,'2010-04-16' union all
select 7,114,140,'2010-05-28'
Go
select a.[usort],
       a.[uid],
       a.[username],
       b.[inmoney],
       c.[outmoney],
       b.[inmoney]-c.[outmoney][money]
from T1 a 
left join
(select [uid],
       sum([inmoney])[inmoney]
from T2
group by [Uid]
)b on a.[uid]=b.[uid]
left join
(
select [uid],
       sum([outmoney])[outmoney]
from T3
group by [Uid])c
on C.[uid]=b.[uid]
/*
usort uid         username inmoney     outmoney    money
----- ----------- -------- ----------- ----------- -----------
01    111         张三       300         100         200
01    112         李四       1000        450         550
02    113         王五       500         232         268
01    114         陈七       1100        240         860

(4 個資料列受到影響)
*/

[albert_sky]

select T.uid,T.username,A.inmonet,B.outmoney from user T
left join 
(
select uid,sum(inmoney) as inmonet from inmoney where indate between '2010-01-01' and  '2010-06-20'
goup by uid 
) A on T.uid=A.uid
left join 
(
select uid,sum(outmoney) as outmoney from outmoney where outdate between '2010-01-01' and  '2010-06-20'
goup by uid 
) B on T.uid=B.uid

[水族杰纶]

[Quote=引用 2 楼 wufeng4552 的回复:]
SQL code
if not object_id('T1') is null
drop table T1
Go
Create table T1([uid] int,[usort] nvarchar(2),[username] nvarchar(2),[money] int)
Insert T1
select 111,N'01',N'张三',200 union all
sel……
[/Quote]
忘記還有一個條件 日期
自己加進去吧
應該不難
在

[albert_sky]

select T.uid,T.username,T.money,A.inmonet,B.outmoney from user T
left join 
(
select uid,sum(inmoney) as inmonet from inmoney where indate between '2010-01-01' and  '2010-06-20'
goup by uid 
) A on T.uid=A.uid
left join 
(
select uid,sum(outmoney) as outmoney from outmoney where outdate between '2010-01-01' and  '2010-06-20'
goup by uid 
) B on T.uid=B.uid

[small_agile]

select t1.usort,t1.username,(select sum(inmoney) from t2 where t2.uid = t1.uid group by uid ),(select sum(outmoney) from t3 where t3.uid = t1.uid group by uid),money from t1