count where group by 查询问题求助

[sosqrn975]

各位好，有个问题请教大家：

drop table ta;
drop table tb;

create table ta(
        id int(4) primary key not null auto_increment,
        user_id int (4),
        name varchar(10)
);

create table tb(
        id int(4) primary key not null auto_increment,
        fid int(4) comment 'ref ta.id',
        status int(1)
);

insert into ta(user_id,name) values(1,'lua');
insert into ta(user_id,name) values(1,'aix');
insert into ta(user_id,name) values(2,'java');
insert into ta(user_id,name) values(2,'cpp');
insert into ta(user_id,name) values(2,'csharp');
insert into ta(user_id,name) values(3,'linux');
insert into ta(user_id,name) values(9,'id');

insert into tb(fid,status) values(1,1);
insert into tb(fid,status) values(3,1);
insert into tb(fid,status) values(4,1);
insert into tb(fid,status) values(5,0);
insert into tb(fid,status) values(7,0);

select * from ta;
select * from tb;
select ta.id,ta.user_id,ta.name,tb.id as tbId,tb.fid,tb.status,count(tb.fid) countFid from ta left join tb on ta.id = tb.fid 
-- where tb.status = 1 
group by ta.id;

我想要的结果是没有注释where前的类似结果，即保证左连左表所有记录都在，没有记录的count以0显示，谢谢~

[rick-he]

你应该将想要的数据贴出来吧，要不就描述下

[ACMAIN_CHM]

select ta.id,ta.user_id,ta.name,tb.id as tbId,tb.fid,tb.status,count(tb.fid) countFid from ta left join tb on ta.id = tb.fid and tb.status = 1 group by ta.id;

[sosqrn975]

引用 2 楼 ACMAIN_CHM 的回复:

select ta.id,ta.user_id,ta.name,tb.id as tbId,tb.fid,tb.status,count(tb.fid) countFid from ta left join tb on ta.id = tb.fid and tb.status = 1 group by ta.id;

谢谢版主，问题解决了~ 俺想知道原理，比如：group by后还想having 还是会过滤 是执行顺序问题?没懂and