34,571
社区成员
发帖
与我相关
我的任务
分享
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)
*/
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
/*
AID clickCount clickby
-----------------
1 10 张三
1 10 李四
2 20 张三
3 0 NULL
*/
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
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 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