--表结构
declare @t table (id char(1),info nvarchar(20))
insert into @t select '0','00000' union
select '1','aaaaa' union
select '2','bbbbb' union
select '3','ccccc' union
select '4','ddddd'declare @order table (oid int,tid char(1),price int)
insert into @order select '1','1',5 union
select '2','1',88 union
select '3','2',40 union
select '4','3',55 union
select '5','2',22 select a.id,isnull(sum(b.price),0) as [sum(price) ],a.info from @t a left join @order b on a.id=b.tid group by a.id,a.info/*
id sum(price) info
---- ----------- --------------------
0 0 00000
1 93 aaaaa
2 62 bbbbb
3 55 ccccc
4 0 ddddd
*/
declare @t table (id char(1),info nvarchar(20))
insert into @t select '0','00000' union
select '1','aaaaa' union
select '2','bbbbb' union
select '3','ccccc' union
select '4','ddddd'declare @order table (oid int,tid char(1),price int)
insert into @order select '1','1',5 union
select '2','1',88 union
select '3','2',40 union
select '4','3',55 union
select '5','2',22 select a.id,isnull(sum(b.price),0) as [sum(price) ],a.info from @t a left join @order b on a.id=b.tid group by a.id,a.info/*
id sum(price) info
---- ----------- --------------------
0 0 00000
1 93 aaaaa
2 62 bbbbb
3 55 ccccc
4 0 ddddd
*/
from @t a, @order b
where a.id=b.tid
group by a.id,a.info
on a.id=b.tid
where b.tid>2
group by a.id,a.info为什么加个where b.tid>2 id=4 这一行就显示不出来了?
而 where a.id>2 则可以
[sum(price)]=isnull((select sum(price) from @order where tid=a.id),0),
a.info
from @t a
而 where a.id>2 则可以
------------------------------------------------------
因为a.id=4时b.tid是NULL值来的,
a.id,
isnull(sum(b.price),0) as [sum(price) ],
a.info
from
@t a
left join
@order b
on
a.id=b.tid and b.tid>2
group by
a.id,a.info
insert into @t
select '0','00000' union
select '1','aaaaa' union
select '2','bbbbb' union
select '3','ccccc' union
select '4','ddddd'declare @order table (oid int,tid char(1),price int)
insert into @order
select '1','1',5 union
select '2','1',88 union
select '3','2',40 union
select '4','3',55 union
select '5','2',22
select a.id,isnull(sum(b.price),0) as [sum(price)],a.info
from @t a left join @order b on a.id=b.tid
--where b.tid>2
group by a.id, a.info/*
--结果
tid sum(price) info
--------------------------------------------------
0 0 00000
1 93 aaaaa
2 62 bbbbb
3 55 ccccc
4 0 ddddd
*/