select a.bh,a.xm,isnull(sum(b.gm),0) as sl from a left join b on a.bh=b.bh group by a.bh,a.xm
libin_ftsafe(子陌红尘) 依然得到的是 张三 001 150 李四 002 20
A表和你的SELECT语句左连就可以了 你的MSSQL为什么不支持左连?
我刚少说了B中的一个字段pm(品名) B 张三 001 50 a 李四 002 20 b 张三 001 50 c 张三 001 50 b我需要查的是除商品品名为b的A中所有人员的购买汇总表 张三 001 150 李四 002 20 王五 003 0请问这个语句怎么写呀? select a.bh,a.xm,sum(b.gm) as sl,b.pm from a left join b on a.bh=b.bh where b.pm in('a','c') group by a.bh,a.xm,b.pm 得到的结果却是: 张三 001 150 李四 002 20
select a.xm,a.bh,isnull(b.tgm,0) from a, ( select bh,sum(gm) as tgm from b group by bh ) d where a.bh = d.bh
create table a( xm nvarchar(6), bh varchar(6) ) drop table b create table b( xm nvarchar(6), bh varchar(6), gm smallint, pm char(3) default 'a' )insert into a select N'张三','001' union all select N'李四','002' union all select N'王五','003'insert into b select N'张三','001',50,'a' union all select N'李四','002',20,'b' union all select N'张三','001',50,'c' union all select N'张三','001',50,'b'select * from a select * from bselect a.xm,a.bh,isnull(d.tgm,0) from a left join ( select bh,sum(gm) as tgm from b group by bh ) d on a.bh = d.bh结果: xm bh ------ ------ ----------- 张三 001 150 李四 002 20 王五 003 0你的数据好像有问题 “我需要查的是除商品品名为b的A中所有人员的购买汇总表”没用到啊
你要加这个条件 就直接在select bh,sum(gm) as tgm from b group by bh 后面加where
select a.bh,a.xm,sum(c.gm) as sl from a left join (select * from b where pm in('a','c')) c on a.bh=c.bh group by a.bh,a.xm
libin_ftsafe(子陌红尘 你的结果虽然每个人出来了,但sl却都是null
select a.bh,a.xm,isnull(sum(b.gm),0) as gm from a left join b on a.bh=b.bh group by a.bh,a.xm
libin_ftsafe(子陌红尘 好像可以了,我再试试! 先谢谢大家!
drop table A drop table B create table A (xm varchar(20),bh varchar(10)) insert into A select'张三','001' insert into A select'李四','002' insert into A select'王五','003' create table B(xm varchar(20),bh varchar(10),gm int) insert into B select'张三','001',50 insert into B select'李四','002',20 insert into B select'张三','001',50 insert into B select'张三','001',50select A.xm,A.bh,isnull(b.c,0) from A left join(select xm,bh,sum(gm) c from B group by xm,bh)b on A.xm=b.xm
a.bh,a.xm,isnull(sum(b.gm),0) as sl
from
a
left join
b
on
a.bh=b.bh
group by
a.bh,a.xm
张三 001 150
李四 002 20
你的MSSQL为什么不支持左连?
B
张三 001 50 a
李四 002 20 b
张三 001 50 c
张三 001 50 b我需要查的是除商品品名为b的A中所有人员的购买汇总表
张三 001 150
李四 002 20
王五 003 0请问这个语句怎么写呀?
select a.bh,a.xm,sum(b.gm) as sl,b.pm from a left join b on a.bh=b.bh where b.pm in('a','c') group by a.bh,a.xm,b.pm
得到的结果却是:
张三 001 150
李四 002 20
(
select bh,sum(gm) as tgm from b group by bh
) d
where a.bh = d.bh
xm nvarchar(6),
bh varchar(6)
)
drop table b
create table b(
xm nvarchar(6),
bh varchar(6),
gm smallint,
pm char(3) default 'a'
)insert into a
select N'张三','001' union all
select N'李四','002' union all
select N'王五','003'insert into b
select N'张三','001',50,'a' union all
select N'李四','002',20,'b' union all
select N'张三','001',50,'c' union all
select N'张三','001',50,'b'select * from a
select * from bselect a.xm,a.bh,isnull(d.tgm,0) from a left join
(
select bh,sum(gm) as tgm from b group by bh
) d
on a.bh = d.bh结果:
xm bh
------ ------ -----------
张三 001 150
李四 002 20
王五 003 0你的数据好像有问题 “我需要查的是除商品品名为b的A中所有人员的购买汇总表”没用到啊
a.bh,a.xm,sum(c.gm) as sl
from
a
left join
(select * from b where pm in('a','c')) c
on
a.bh=c.bh
group by
a.bh,a.xm
你的结果虽然每个人出来了,但sl却都是null
a.bh,a.xm,isnull(sum(b.gm),0) as gm
from
a
left join
b
on
a.bh=b.bh
group by
a.bh,a.xm
好像可以了,我再试试!
先谢谢大家!
drop table B
create table A (xm varchar(20),bh varchar(10))
insert into A select'张三','001'
insert into A select'李四','002'
insert into A select'王五','003'
create table B(xm varchar(20),bh varchar(10),gm int)
insert into B select'张三','001',50
insert into B select'李四','002',20
insert into B select'张三','001',50
insert into B select'张三','001',50select A.xm,A.bh,isnull(b.c,0)
from A left join(select xm,bh,sum(gm) c
from B
group by xm,bh)b
on A.xm=b.xm