表中数据如下number money
111 600
111 100
222 800
333 400$query = $db->query("SELECT count(*) AS c FROM test WHERE stat=0 group by number HAVING SUM(money) >500");$total = $this->db->result($query, 0); 应该出现$total=2条数据。为什么我使用只有$total=1?
111 600
111 100
222 800
333 400$query = $db->query("SELECT count(*) AS c FROM test WHERE stat=0 group by number HAVING SUM(money) >500");$total = $this->db->result($query, 0); 应该出现$total=2条数据。为什么我使用只有$total=1?
看下可以不
是出现c
1
1
奇怪。用count(*)统计出来2
应该是你方法调取内容的问题
$query = $db->query("select count(*) from (select number from test where stat=0 group by number having sum(money) >500) t");
insert into test values
(111,600),(111,100),(222,800),(333,400);mysql> select count(*) as c
-> from test
-> group by number
-> having sum(money)>500;
+---+
| c |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.45 sec)
--这条语句的意思是查询出每一个number,money的和大于500的记录条数。
绝对不会快。
你可以试着加一个 (stat,number) 的复合索引。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.htmlCREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option] ...index_col_name:
col_name [(length)] [ASC | DESC]index_type:
USING {BTREE | HASH | RTREE}index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name