有一表 tms
字段
id auto_increment
TeamID int(11) UNSIGNED
Set1 tinyint(4) UNSIGNED
OT1 tinyint(4) UNSIGNED
Select count(*) as mCount,if(OT1!=0,sum(1),false) as cOT1 From `tms` group by TeamIDcount(*) as mCount =>所有符合条件的记录数量if(OT1!=0,sum(1),false) as cOT1 => OT1 不等于0的记录的数量.为何得出来的数量(if(OT1!=0,sum(1),false) as cOT1) 不正确的,怎样改?
字段
id auto_increment
TeamID int(11) UNSIGNED
Set1 tinyint(4) UNSIGNED
OT1 tinyint(4) UNSIGNED
Select count(*) as mCount,if(OT1!=0,sum(1),false) as cOT1 From `tms` group by TeamIDcount(*) as mCount =>所有符合条件的记录数量if(OT1!=0,sum(1),false) as cOT1 => OT1 不等于0的记录的数量.为何得出来的数量(if(OT1!=0,sum(1),false) as cOT1) 不正确的,怎样改?
解决方案 »
- 这个SQL有没优化的余地呢
- 请教一个sql的写法,谢谢大家!
- mysqldump Couldn't execute 'show create table.....的错误!
- MySql PROCEDURE 内部调用过程
- 8G内存,myisam存储引擎的系统参数配置,大家给予点意见!
- 关于innodb类型表的count准确性
- MySQL PHP left join错了吗?select read.*,user.uname,category.cname from read left join
- 在C++里怎样锁定MySql表里的记录?
- mysql中的ENUM是什么数据类型
- Mysql字符乱码问题
- 配2台机器的双向同步(主--主)失败
- mysqld [options]怎么使用
Select count(*) as mCount,sum(if(OT1!=0,1,0)) as cOT1 From `tms` group by TeamID
TeamID int(11) UNSIGNED
Set1 tinyint(4) UNSIGNED
OT1 tinyint(4) UNSIGNED
错误:
Select count(*) as mCount,if(OT1!=0,sum(1),false) as cOT1 From `tms` group by TeamID
首先你的if是条件句,只能走一次。
要循环,但是可以子查询
正确:
Select count(*) as mCount,
(select count(×)from `tms` where OT1!=0) as cOT1 from 'tms' group by TeamID