A表有ID,NAME,delfalg, B表有 id,aid,delflag,status (aid引用a表id)
现在想查a表中每个name标记为delfag = 1的在b表中的记录数,且b表中delflag =1 and status = 1
这个sql语句应该怎么写才对呢?我写的是这样
select a.* ,count(b.*) as acount
from a left join b on a.id = b.aid
where a.delflag = 1 and b.status = 1 and b.delflag = 1
group a.id,a.name
但是这样写的后果是,a表中记录在b 表中标记为delfalg =0的会显示不出来
现在想查a表中每个name标记为delfag = 1的在b表中的记录数,且b表中delflag =1 and status = 1
这个sql语句应该怎么写才对呢?我写的是这样
select a.* ,count(b.*) as acount
from a left join b on a.id = b.aid
where a.delflag = 1 and b.status = 1 and b.delflag = 1
group a.id,a.name
但是这样写的后果是,a表中记录在b 表中标记为delfalg =0的会显示不出来
select a.id,a.name,a.delflag ,count(b.aid) as acount
from a left join b on a.id = b.aid
where a.delflag = 1 and b.status = 1 and b.delflag = 1
group a.id,a.name,a.delflag
但是这样写的后果是,a表中记录在b 表中标记为delfalg =0的会显示不出来
select count(*) from
(select id,name from A where A.defalg =1 a),
(select aid from B where delflag =1 and status =1 b)
where a.id =b.aid
select count(*) from b where b.delflag =1 and b.status = 1 and
exists(select 1 from a where a.id=b.aid and a.delfag=1)