Question

sql 聚合函数的使用

数据库：ACCESS
sql="select a.ID,a.title,count(b.ID) as total from table1 a left join table2 b on a.ID=b.AID where title like '%kkk%' group by a.ID,a.title order by a.ID desc"
这句是对的，可是在where后面再加上一个聚合函数的值的使用就不行了：
sql="select a.ID,a.title,count(b.ID) as total from table1 a left join table2 b on a.ID=b.AID where title like '%kkk%' and total>5 group by a.ID,a.title order by a.ID desc"
请教这是怎么回事呢？谢谢各位达人！！
补充：无论是total>5还是a.total>5还是b.total>5还是count(b.ID)>5这些都不行
谢谢你 popeye627！
我还想知道having后面可以不可以用between呢？
比如：having count(b.ID) between 5 and 10

Answer 1

sql="select a.ID,a.title,count(b.ID) as total from table1 a left join table2 b on a.ID=b.AID where title like '%kkk%' group by a.ID,a.title having count(b.ID)>5 order by a.ID desc"

应该可以吧，你试试

Answer 2

应该这样

sql="select a.ID,a.title,count(b.ID) as total from table1 a left join table2 b on a.ID=b.AID where title like '%kkk%' group by a.ID,a.title
having count(b.ID)>5
order by a.ID desc"