create table a(id int,name char(5)) create table b(id int,a_id int,money int,status int) insert into a select 1,'Tom' insert into a select 2,'Mary' insert into a select 3,'Tom' insert into a select 4,'Tom' insert into a select 5,'Mary' insert into a select 6,'Jack' insert into a select 7,'Jack' insert into a select 8,'Mary' insert into b select 1, 2, 12, 1 insert into b select 2, 4, 4, 0 insert into b select 3, 5, 3, 0 insert into b select 4, 1, 9, 1 insert into b select 5, 3, 8, 1 insert into b select 6, 7, 3, 1 insert into b select 7, 8, 26, 0 insert into b select 8, 6, 3, 1 select isnull(a.name,b.name)as name,isnull(a.status_0,0)as status_0,b.status_1 from (select name,sum(money)as status_0 from (select name,status,money from a join b on a.id=b.a_id and b.status=0)a group by name)a full join (select name,sum(money)as status_1 from (select name,status,money from a join b on a.id=b.a_id and b.status=1)b group by name)b on a.name=b.name drop table a drop table b ------------结果---------------- name status_0 status_1 Jack 0 6 Mary 29 12 Tom 4 17
select 客户名,(select sum(money) from b b.a_id=a.id and status=0) as status_0 , (select sum(money) from b b.a_id=a.id and status=0) as status_1 from TABLE
怎么各位总是比我快????? 还在测试呢,都已经全都上来了,嘿嘿 select [name],status_0=sum(case statu when'0' then money else 0 end),status_1=sum(case statu when '1' then money else 0 end) from a,b where a.id=b.a_id group by namename status_0 status_1 ---------- ----------- ----------- jack 0 6 mary 29 12 tom 4 17(所影响的行数为 3 行)
to:azsoft(Try my best) 這句應該是(select sum(money) from b b.a_id=a.id and status=1) as status_1吧
不是存贮过程,你直接copy到查询分析起执行: --建立测试环境,创建a,b表及插入数据 create table a(id int,name char(5)) create table b(id int,a_id int,money int,status int) insert into a select 1,'Tom' insert into a select 2,'Mary' insert into a select 3,'Tom' insert into a select 4,'Tom' insert into a select 5,'Mary' insert into a select 6,'Jack' insert into a select 7,'Jack' insert into a select 8,'Mary' insert into b select 1, 2, 12, 1 insert into b select 2, 4, 4, 0 insert into b select 3, 5, 3, 0 insert into b select 4, 1, 9, 1 insert into b select 5, 3, 8, 1 insert into b select 6, 7, 3, 1 insert into b select 7, 8, 26, 0 insert into b select 8, 6, 3, 1 --1句sql语句 select isnull(a.name,b.name)as name,isnull(a.status_0,0)as status_0,b.status_1 from (select name,sum(money)as status_0 from (select name,status,money from a join b on a.id=b.a_id and b.status=0)a group by name)a full join (select name,sum(money)as status_1 from (select name,status,money from a join b on a.id=b.a_id and b.status=1)b group by name)b on a.name=b.name --删除测试表 drop table a drop table b
感谢所有帮助我的朋友,我可以实现这个功能了 特别是midnight2002(午夜)!
对不住 下面经过测试select distinct a.客户名,isnull(o.status_0,0),isnull(p.status_1,0) from a left join (select 客户名,sum(b.money) as status_0 from a inner join b on a.id=b.a_id and b.status=0 group by 客户名) o on o.客户名=a.客户名 left join (select 客户名,sum(b.money) as status_1 from a inner join b on a.id=b.a_id and b.status=1 group by 客户名) p on p.客户名=a.客户名
create table b(id int,a_id int,money int,status int)
insert into a select 1,'Tom'
insert into a select 2,'Mary'
insert into a select 3,'Tom'
insert into a select 4,'Tom'
insert into a select 5,'Mary'
insert into a select 6,'Jack'
insert into a select 7,'Jack'
insert into a select 8,'Mary'
insert into b select 1, 2, 12, 1
insert into b select 2, 4, 4, 0
insert into b select 3, 5, 3, 0
insert into b select 4, 1, 9, 1
insert into b select 5, 3, 8, 1
insert into b select 6, 7, 3, 1
insert into b select 7, 8, 26, 0
insert into b select 8, 6, 3, 1
select isnull(a.name,b.name)as name,isnull(a.status_0,0)as status_0,b.status_1 from
(select name,sum(money)as status_0 from (select name,status,money from a join b on a.id=b.a_id and b.status=0)a group by name)a
full join
(select name,sum(money)as status_1 from (select name,status,money from a join b on a.id=b.a_id and b.status=1)b group by name)b
on a.name=b.name
drop table a
drop table b
------------结果----------------
name status_0 status_1
Jack 0 6
Mary 29 12
Tom 4 17
还在测试呢,都已经全都上来了,嘿嘿
select [name],status_0=sum(case statu when'0' then money else 0 end),status_1=sum(case statu when '1' then money else 0 end)
from a,b where a.id=b.a_id group by namename status_0 status_1
---------- ----------- -----------
jack 0 6
mary 29 12
tom 4 17(所影响的行数为 3 行)
這句應該是(select sum(money) from b b.a_id=a.id and status=1) as status_1吧
--建立测试环境,创建a,b表及插入数据
create table a(id int,name char(5))
create table b(id int,a_id int,money int,status int)
insert into a select 1,'Tom'
insert into a select 2,'Mary'
insert into a select 3,'Tom'
insert into a select 4,'Tom'
insert into a select 5,'Mary'
insert into a select 6,'Jack'
insert into a select 7,'Jack'
insert into a select 8,'Mary'
insert into b select 1, 2, 12, 1
insert into b select 2, 4, 4, 0
insert into b select 3, 5, 3, 0
insert into b select 4, 1, 9, 1
insert into b select 5, 3, 8, 1
insert into b select 6, 7, 3, 1
insert into b select 7, 8, 26, 0
insert into b select 8, 6, 3, 1
--1句sql语句
select isnull(a.name,b.name)as name,isnull(a.status_0,0)as status_0,b.status_1 from
(select name,sum(money)as status_0 from (select name,status,money from a join b on a.id=b.a_id and b.status=0)a group by name)a
full join
(select name,sum(money)as status_1 from (select name,status,money from a join b on a.id=b.a_id and b.status=1)b group by name)b
on a.name=b.name
--删除测试表
drop table a
drop table b
特别是midnight2002(午夜)!
下面经过测试select distinct a.客户名,isnull(o.status_0,0),isnull(p.status_1,0)
from a
left join
(select 客户名,sum(b.money) as status_0 from a inner join b on a.id=b.a_id and b.status=0
group by 客户名) o
on o.客户名=a.客户名 left join
(select 客户名,sum(b.money) as status_1 from a inner join b on a.id=b.a_id and b.status=1
group by 客户名) p
on p.客户名=a.客户名