UC知道这段SQL文怎么写?
来源：百度知道 编辑：UC知道 时间：2024/06/14 11:57:28
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)
谢谢啦.
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;