首先两张表...
t1{id,num}
t2{id,mainid,value}
t2.mainid外键关联t1.idselect t1.id, t2,id from t1 left join t2 on t1.id = t2.mainid order by t1.id;得到结果:
1 1
1 2
1 3
2 null
3 1
3 2
4 1
5 1.....但是我想用聚合函数
让显示结果如下:
1 3
2 0
3 2
4 1
5 1...
可是我这么写就报错...-0 -
select t1.id, t2.count(id) from t1 left join t2 on t1.id = t2.mainid order by t1.id;
小弟不知道错在哪里- -请各位高人大侠们多多赐教..
t1{id,num}
t2{id,mainid,value}
t2.mainid外键关联t1.idselect t1.id, t2,id from t1 left join t2 on t1.id = t2.mainid order by t1.id;得到结果:
1 1
1 2
1 3
2 null
3 1
3 2
4 1
5 1.....但是我想用聚合函数
让显示结果如下:
1 3
2 0
3 2
4 1
5 1...
可是我这么写就报错...-0 -
select t1.id, t2.count(id) from t1 left join t2 on t1.id = t2.mainid order by t1.id;
小弟不知道错在哪里- -请各位高人大侠们多多赐教..
1 count 放在 t2.id 之外
2 聚合,超过两个字段,请用 group by如下为语法上的更正:select t1.id, count(t2.id) from t1 left join t2 on t1.id = t2.mainid group by t1.id order by t1.id;
from t1 left join t2 on t1.id = t2.mainid
group by t1.id;