求sql，各位大侠帮忙啊，谢谢
表tab_a
------------------------
AID （文章ID） clickCount（点击数）
1 10
2 20
3 0
表tab_b
------------------------
BID AID （文章ID） clickby（点击人）
1 1 张三
2 1 李四
3 2 张三
想要得到的结果集合是
AID clickCount clickby
1 10 张三
1 10 李四
2 20 张三
3 0 nil
请问这个sql怎么写，谢谢各位大侠啊。。。
[解决办法]
你那个是nil，还是null
这不是左匹配就行了吗
select a.aid,a.clickcount,b.clickby
from tab_a a left join tab_b on a.aid=b.bid
[解决办法]
select t1.AID,isnull(sum(clickCount),0) as clickCount,t2.clickby
from tab_a t1 (nolock)
right join tab_b t2 (nolock) on t1.AID=t2.AID
GROUP BY t1.AID,t2.clickby
left join tab_a a on B.AID=A.AID
[解决办法]
select t1.AID,isnull(sum(clickCount),0) as clickCount,isnull(t2.clickby,'nil') clickby
from tab_a t1 (nolock)
right join tab_b t2 (nolock) on t1.AID=t2.AID
GROUP BY t1.AID,t2.clickby
SELECT B.AID,ISNULL(SUM(a.clickCount),0) clickCount,B.clickby FROM tab_b b LEFT JOIN tab_a a
ON b.AID = a.AID
create table tab_a(AID int,clickCount int)
insert into tab_a
select 1,10
union all select 2,20
union all select 3,0
create table tab_b(BID int,AID int,clickby varchar(10))
insert into tab_b
select 1,1,'张三'
union all select 2,1,'李四'
union all select 3,2,'张三'
select A.AID,A.clickCount,B.clickby
from tab_a a
left join tab_b b on B.AID=A.AID
/*
AIDclickCountclickby
-----------------
110张三
110李四
220张三
30NULL
*/
create table tab_a
(AID int, clickCount int)
insert into tab_a
select 1, 10 union all
select 2, 20 union all
select 3, 0
create table tab_b
(BID int, AID int, clickby varchar(10))
insert into tab_b
select 1, 1, '张三' union all
select 2, 1, '李四' union all
select 3, 2, '张三'
select a.AID,a.clickCount,b.clickby
from tab_a a
left join tab_b b on a.AID=b.AID
/*
AID clickCount clickby
----------- ----------- ----------
1 10 张三
1 10 李四
2 20 张三
3 0 null
(4 row(s) affected)
*/
