有表 Aadvertid advertname desc
1 广告1 广告
2 广告2 广告
3 广告3 广告表B
logid advertid num
1 1 20
2 1 25
3 1 10
4 2 10求某个广告的详细信息 和 总点击量。 比如 对于广告1, 我想得到查询结果如下:
1 广告1 广告 55各位高手帮帮忙
1 广告1 广告
2 广告2 广告
3 广告3 广告表B
logid advertid num
1 1 20
2 1 25
3 1 10
4 2 10求某个广告的详细信息 和 总点击量。 比如 对于广告1, 我想得到查询结果如下:
1 广告1 广告 55各位高手帮帮忙
from tb1 a
left join
tb2 b
on a.advertid=b.advertid
group by a.advertid,a.advertname,a.[desc]
go
insert into A
select 1,'广告1','广告'
union all select 2,'广告2','广告'
union all select 3,'广告3','广告'
go
create table B(logid int,advertid int,num int)
go
insert into B
select 1,1,20
union all select 2,1,25
union all select 3,1,10
union all select 4,2,10
go
select * from a
select * from B
select advertid,advertname,[desc],(select sum(num) from b where advertid=a.advertid) as nums from a
go
drop table a,B
go(3 行受影响)(4 行受影响)
advertid advertname desc
----------- -------------------- --------------------
1 广告1 广告
2 广告2 广告
3 广告3 广告(3 行受影响)logid advertid num
----------- ----------- -----------
1 1 20
2 1 25
3 1 10
4 2 10(4 行受影响)advertid advertname desc nums
----------- -------------------- -------------------- -----------
1 广告1 广告 55
2 广告2 广告 10
3 广告3 广告 NULL(3 行受影响)
on a.advertid=b.advertid where a.advertid='1'
group by a.advertid, a.advertname, a.desc
from tb1 a
left join
(select advertid,sum(num) total from tb2 group by advertid) b
on a.advertid=b.advertid
FROM ta a
LEFT JOIN
(SELECT advertid,SUM(num) num FROM tb GROUP BY advertid) b
ON a.advertid=b.advertid
WHERE a.advertName='广告1' --替换你想查的
from a left outer join
(select advertid,sum(num) as num from b group by advertid) b
on a.advertid=b.advertid
insert into @a select 1,'广告1','广告'
insert into @a select 2,'广告2','广告'
insert into @a select 3,'广告3','广告'
declare @b table (logid int, advertid int, num int)
insert into @b select 1,1,20
insert into @b select 2,1,25
insert into @b select 3,1,10
insert into @b select 4,2,10select a.advertid, a.advertname, a.[desc],sum(b.num) as '总和'
from @a a inner join @b b
on a.advertid=b.advertid where a.advertid='1'
group by a.advertid, a.advertname, a.[desc]
advertid advertname desc 总和
1 广告1 广告 55
from a full join
(select advertid,sum(num) as num from b group by advertid) b
on a.advertid=b.advertid
---------
看来需要加where 条件
(select isnull(sum(num),0) from b where advertid=a.advertid) as nums
from a