这段SQL文怎么写?

来源:百度知道 编辑:互助问答吧 时间:2020/08/13 07:40:10
a表:id,people
id people
1 john
2 tom
3 peter
...
b表:id,present,people
id present people
1 a tom
2 a tom
3 a tom
4 b peter
....
我要抽出a表的第30到第40条的数据.并且算出a表中每个人的礼物数.
就是通过b表进行count计算后添加到a表.怎么做?
最后抽出的数据希望是
a.id,a.people,count(b.present)

谢谢啦.

select a.id,a.people,count(b.present)
from a left join b on a.people=b.people
where a.id>=30 and a.id<=40
group by a.id,a.people

SELECT id,people FROM a
WHERE EXISTS(SELECT present FROM b
GROUP BY present HAVING COUNT(b.present) )

看看行不。不行发你那个数据库我,帮你试出个方法!

SELECT a.id, a.people,count(b.present) AS Expr1
FROM a INNER JOIN b ON a.people = b.people where a.id between 30 and 40 group by a.people;