select
max(product.productId) as maxProductId,
count(product.productId) as countProductId,
company.userGrade as userGrade,
company.name as companyName
from product left join company on company.companyId=product.companyId
where product.status=3
and match(product.name,product.keywords) against('+light' in boolean mode)
and product.address='zhejiang'
group by product.companyId
order by (company.userGrade ) desc
,(match(product.name,product.keywords) against('+light' in boolean mode) ) desc
limit 0,20
这样的一段sql语句,那我count明显不能用了,但我还是要统计一下数量用于分页,怎么办
max(product.productId) as maxProductId,
count(product.productId) as countProductId,
company.userGrade as userGrade,
company.name as companyName
from product left join company on company.companyId=product.companyId
where product.status=3
and match(product.name,product.keywords) against('+light' in boolean mode)
and product.address='zhejiang'
group by product.companyId
order by (company.userGrade ) desc
,(match(product.name,product.keywords) against('+light' in boolean mode) ) desc
limit 0,20
这样的一段sql语句,那我count明显不能用了,但我还是要统计一下数量用于分页,怎么办
解决方案 »
- 查询结果,不显示0值,怎么写?
- 这个子查询为什么不成功?
- 多表联合查询问题?
- mysql_real_escape_string 的问题
- 使用mysqlmanager管理mysql实例时,mysqlmanager自身怎样退出,只能kill进程吗?
- mysql存储过程参数处理问题
- 在MYSQL备份数据中需要备份InnoDB目录中的数据吗?
- mysql建好表后不能插入数据
- 怎样从由mysqldump --xml生成的xml文件中将数据还原到数据库的某个表中?
- 求一条SQL语句
- 关于update的问题
- 我的mysql数据库现在只有.frm文件了,我直接复制到新的mysql/data目录下面,确总是报表不存在错误
select
max(product.productId) as maxProductId,
count(product.productId) as countProductId,
company.userGrade as userGrade,
company.name as companyName
from product left join company on company.companyId=product.companyId
where product.status=3
and match(product.name,product.keywords) against('+light' in boolean mode)
and product.address='zhejiang'
group by product.companyId
order by (company.userGrade ) desc
,(match(product.name,product.keywords) against('+light' in boolean mode) ) desc
limit 0,20
) as ttt
但我想要整个
select
max(product.productId) as maxProductId,
count(*) as countcompanyId,
company.userGrade as userGrade,
company.name as companyName
from product left join company on company.companyId=product.companyId
where product.status=3
and match(product.name,product.keywords) against('+light' in boolean mode)
and product.address='zhejiang'
group by product.companyId
order by (company.userGrade ) desc
,(match(product.name,product.keywords) against('+light' in boolean mode) ) desc是想这样子吗?
按照 companyId 分组,并把所有相同的 companyId 数量统计出来。
但limit 0,20又不想不带,想一次性全部取出来
不过看起来是没办法,总归要统计一次,哎,先这样办法,我一会来结贴
select mycol1,count(*) as col1Count from myTable where 1 group by mycol1;
显示的就是每个mycol1 数据,并统计出每个mycol1数据的重复数目。如果后面有Limit X,Y 则显示第X到Y条mycol1 数据,并统计出这些mycol1数据的重复数目。
如果select mycol1,count(*) as col1Count ,NewCol2 ......
则对 NewCol2 ,之输出第一条符合条件的 NewCol2 数据。
下面的条件用你的。这样就能统计出来了。