用户表(users)
字段 类型 关系
Uid(用户ID号) Int 主键(自增长)
Uname(用户名) Varchar(20) 唯一键
Upassword(密码) Varchar(20)
Logintime(登录时间) Datetime 最近一次登录时间
Logincount(登录次数) Int 帐户表(account)
字段 类型 关系
Aid(帐户ID号) Int 主键,自增长
uid(用户ID号) Int Users表外键
Inmoney Money 进帐数
Outmoney Money 出帐数
数据
Users表数据
1 李嘉诚 123456 2009-1-19 50
2 比尔.盖茨 654321 2009-1-19 30 Account表数据
1 1 10000 5000
2 2 1000 200
3 1 1500 0
4 1 2000 500
5 2 4000 3000
6 1 5000 7000
要得到如下结果:(请用三种方法做出下列结果)uname 入账 出账 剩余
李嘉诚 18500.00 12500.00 6000.00
比尔盖茨 5000.00 3200。00 1800.00
字段 类型 关系
Uid(用户ID号) Int 主键(自增长)
Uname(用户名) Varchar(20) 唯一键
Upassword(密码) Varchar(20)
Logintime(登录时间) Datetime 最近一次登录时间
Logincount(登录次数) Int 帐户表(account)
字段 类型 关系
Aid(帐户ID号) Int 主键,自增长
uid(用户ID号) Int Users表外键
Inmoney Money 进帐数
Outmoney Money 出帐数
数据
Users表数据
1 李嘉诚 123456 2009-1-19 50
2 比尔.盖茨 654321 2009-1-19 30 Account表数据
1 1 10000 5000
2 2 1000 200
3 1 1500 0
4 1 2000 500
5 2 4000 3000
6 1 5000 7000
要得到如下结果:(请用三种方法做出下列结果)uname 入账 出账 剩余
李嘉诚 18500.00 12500.00 6000.00
比尔盖茨 5000.00 3200。00 1800.00
from [users] a
join [Account] b on a.uid=b.uid
group by a.uname
from [users] a
,(select uid,sum(inmoney) as inmoney ,sum(outmoney) oumoney
from account
group by uid) b
where a.uid = b.uid
from
(select a.uid,a.uname, b.inmoney ,b.outmoney
from [users] a,account b
where a.uid = b.uid) c
group by c.uid
b.*
from
[users] a,
(
select
uid,sum(b.Inmoney) as 入账,sum(b.Outmoney) as 出账,sum(b.Inmoney-b.Outmoney) as 剩余
from
[Account]
group by
uid)b
where
a.uid=b.uid
Uid int not null,
Uname varchar(20),
Upassword varchar(20),
Logintime datetime,
Logincount int)insert into users
select '1', '李嘉诚', '123456', '2009-1-19', '50' union all
select '2', '比尔.盖茨', '654321', '2009-1-19', '30'if object_id('account') is not null drop table accountcreate table account(
Aid int,
Uid int,
Inmoney money,
OUtmoney money)insert into account
select 1,1,10000,5000 union all
select 2,2,1000,200 union all
select 3,1,1500,0 union all
select 4,1,2000,500 union all
select 5,2,4000,3000 union all
select 6,1,5000,7000select a.Uname, sum(Inmoney) as 入账, sum(Outmoney) as 出帐,
sum(Inmoney) - sum(Outmoney) as 剩余
from users as a inner join
account as b
on a.Uid = b.Uid
group by a.Uname--比尔.盖茨 5000 3200 1800
--李嘉诚 18500 12500 6000select
a.Uname,b.*
from
[users] a,
(
select
uid,sum(Inmoney) as 入账,sum(Outmoney) as 出账,sum(Inmoney-Outmoney) as 剩余
from
[account]
group by
uid)b
where
a.uid=b.uid--李嘉诚 1 18500 12500 6000
--比尔.盖茨 2 5000 3200 1800
insert into users
select '1', '李嘉诚', '123456', '2009-1-19', '50' union all
select '2', '比尔.盖茨', '654321', '2009-1-19', '30'
create table account(Aid int,Uid int,Inmoney money,OUtmoney money)
insert into account
select 1,1,10000,5000 union all
select 2,2,1000,200 union all
select 3,1,1500,0 union all
select 4,1,2000,500 union all
select 5,2,4000,3000 union all
select 6,1,5000,7000
go--方法一
select m.Uname , sum(n.Inmoney) 入账 , sum(n.OUtmoney) 出账 , sum(n.Inmoney - n.OUtmoney) 剩余 from users m , account n where m.Uid = n.Uid group by m.Uname order by m.Uname--方法二
select m.Uname ,
入账 = isnull((select sum(Inmoney) from account n where n.Uid = m.Uid),0),
出账 = isnull((select sum(OUtmoney) from account n where n.Uid = m.Uid),0),
剩余 = isnull((select sum(Inmoney) from account n where n.Uid = m.Uid),0) - isnull((select sum(OUtmoney) from account n where n.Uid = m.Uid),0)
from users m order by m.Uname--方法三
select m.Uname , t.入账 , t.出账 , t.剩余 from users m,
(select n.Uid , sum(n.Inmoney) 入账 , sum(n.OUtmoney) 出账 , sum(n.Inmoney - n.OUtmoney) 剩余 from account n group by n.Uid) t
where m.uid = t.uid
order by m.Unamedrop table users,account/*
Uname 入账 出账 剩余
-------------------- --------------------- --------------------- ---------------------
比尔.盖茨 5000.0000 3200.0000 1800.0000
李嘉诚 18500.0000 12500.0000 6000.0000(所影响的行数为 2 行)Uname 入账 出账 剩余
-------------------- --------------------- --------------------- ---------------------
比尔.盖茨 5000.0000 3200.0000 1800.0000
李嘉诚 18500.0000 12500.0000 6000.0000(所影响的行数为 2 行)Uname 入账 出账 剩余
-------------------- --------------------- --------------------- ---------------------
比尔.盖茨 5000.0000 3200.0000 1800.0000
李嘉诚 18500.0000 12500.0000 6000.0000(所影响的行数为 2 行)*/