select sum(`count`) as totalcount from mytest group by id where totalcount > 1000
count是保留字你加一个`试试
count是保留字你加一个`试试
解决方案 »
- mysql+php环境正常运行一段时间后,php连接mysql失败,需重启电脑才行
- exec('mysqldump -uroot -proot dbName xxxxx > myFile'); 之后文件如何释放?
- delete data from tb的问题!
- mysql的两表数据关联问题
- liunx下mysql安装问题
- 求教mysql下的数据加密问题
- 本版将要强行结帖,请各位注意自己的帖子!
- 关于mysql 在大并发写入时存储过程中修改表名
- 一master二slave问题
- 请问jdbc for mysql的最新版本在哪里可以下载?
- 请问MySql怎么查询前几行啊??
- 在MYSQL SERVER2000中如何存取图片
如果是postgresql 数据库的话是:
select id,regdate,count from tb where id in (select id from tb group by id having sum(count)>1000) mysql 数据库的话
select id,regdate,count from mytest group by id having sum(count) > 1000
这里只列出其中一条记录!
+-------+-------+-------+
| cid | ctype | cflag |
+-------+-------+-------+
| 00001 | 1 | 0 |
| 00002 | 1 | 1 |
| 00003 | 1 | 1 |
| 00004 | 1 | 0 |
| 00005 | 1 | 1 |
| 00006 | 2 | 1 |
| 00007 | 2 | 0 |
| 00008 | 3 | 1 |
+-------+-------+-------+
8 rows in set (0.00 sec)mysql> SELECT CTYPE , COUNT(*) AS TJ FROM T_USER
-> GROUP BY CTYPE;
+-------+----+
| CTYPE | TJ |
+-------+----+
| 1 | 5 |
| 2 | 2 |
| 3 | 1 |
+-------+----+
3 rows in set (0.01 sec)mysql> SELECT CTYPE , COUNT(*) AS TJ FROM T_USER
-> GROUP BY CTYPE
-> HAVING TJ>1;
+-------+----+
| CTYPE | TJ |
+-------+----+
| 1 | 5 |
| 2 | 2 |
+-------+----+
2 rows in set (0.00 sec)以上是测试结果
where id in(select id from mytest group by id having sum('count')>1000)
table : mytest
字段/记录:
id regdate regcount
1 ####### 1
1 ####### 2
1 ####### 3
2 ####### 5
2 ####### 5
2 ####### 5
..........
我想按id对分组,计算regcount的和
上面的例子可以通过:
select id,sum(regcount) as totalcount from mytest group by id的到如下: id totalcount
1 6
2 15
现在我的问题是我只要totalcount > 10的记录
也就是出来的应该是
id totalcount
2 15
谢谢各位再指教一二!
select id , sum(count) as totalcount from xmail group by id having totalcount>10
感谢 各位帮助,
尤其感谢 : sunthing(藏身网上)