忘了加Group by了:select a.IDa,count(*) from a,aa where substr(aa.IDaa,1,1)=a.IDa and aa.status='1' group by a.IDa
使用下面語句即可: select a.IDa,count(status) as all_ID from a LEFT JOIN aa ON a.IDa=substr(b.IDaa,1,1) where aa.status='1' group by a.IDa; 對於你給的數據,用上面語句查詢的結果應是: IDA all_ID ------ ------ 1 2 0 1另外樓主的表述有問題,因為在你給的數據中,aa.status='1'時,IDaa隻有兩種值0和1,你的結果中怎麼出現了2 ?是不是你的表述沒有表達清楚?
all 是计算table aa中出现1的个数
要是這樣的話,那用下面語句即可: select a.IDa,count(status) as all_ID from a LEFT JOIN aa ON a.IDa=substr(b.IDaa,1,1) and aa.status='1' group by a.IDa;--下面為查詢結果 IDA ALL_ID ---------- ---------- 0 1 1 2 2 0
from a,aa
where substr(aa.IDaa,1,1)=a.IDa and aa.status='1'
group by a.IDa
select a.IDa,count(status) as all_ID
from a LEFT JOIN aa ON a.IDa=substr(b.IDaa,1,1)
where aa.status='1'
group by a.IDa;
對於你給的數據,用上面語句查詢的結果應是:
IDA all_ID
------ ------
1 2
0 1另外樓主的表述有問題,因為在你給的數據中,aa.status='1'時,IDaa隻有兩種值0和1,你的結果中怎麼出現了2 ?是不是你的表述沒有表達清楚?
select a.IDa,count(status) as all_ID
from a LEFT JOIN aa ON a.IDa=substr(b.IDaa,1,1) and aa.status='1'
group by a.IDa;--下面為查詢結果
IDA ALL_ID
---------- ----------
0 1
1 2
2 0