SELECT
a.id,a.name,sum(b.num)
FROM A INNER JOIN B ON A.ID = B.ID AND date_format(accessdate,'%Y/%m/%d') between '2006-01-01' and '2006-01-02'
GROUP BY A.ID, A.NAME
a.id,a.name,sum(b.num)
FROM A INNER JOIN B ON A.ID = B.ID AND date_format(accessdate,'%Y/%m/%d') between '2006-01-01' and '2006-01-02'
GROUP BY A.ID, A.NAME
a.logid=b.logid and a.logid in(1,2) and date_format(b.accessdate,'%Y/%m/%d') between '2006/06/01' and '2006/06/02' group by a.logid order by a.logid这种方法将我不需要的其他记录也显示出来了啊.我要的结果是:logid name sumnum
1 A1 400
2 A2 0但是显示的结果却是:logid name sumnum
1 A1 400
2 A2 0
3 A3 0
4 A4 0............注意条件:in(1,2)
a.logid ,a.name,sum(b.num)
FROM A LEFT OUTER JOIN B ON A.logid = B.logid AND date_format(accessdate,'%Y/%m/%d') between '2006-01-01' and '2006-01-02'
WHERE
a.logid in(1,2)
GROUP BY A.logid , A.NAME
(
logid int,
name varchar(10)
);
create table B
(
id int,
logid int,
name varchar(10),
num int,
accessdate date
);
insert into A select 1 , 'A1'
union all 2, 'A2';
insert into B select 1 , 1 , 'A1' , 100 , '2006-01-01'
union all select 2, 1, 'A1', 300 , '2006-01-02';select a.logid,a.name,if(sum(b.num)>0,sum(b.num),0) as sumset from a left join b on a.logid = b.logid AND b.accessdate between '2006-01-01' and '2006-01-02' group by a.logid,a.name
logid name sumset
1 A1 400
2 A2 0
*/