MySQL Query 请教

Question

Example codecreate table t(idx int,cname varchar(30));
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');select * from t的结果这样mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | c     |
+------+-------+怎么才能得到这种结果？+------+-------+-------+
| idx  | cname |countnum| 
+------+-------+-------+
|    1 | a     |3      |
|    2 | b     |3      |
|    3 | c     |3      |
+------+-------+-------+
countnum 是这张表的总记录数SQL Server和oracle都可以用Row_number来得到，
MySQL 如何得到？谢谢

Answer 1

mysql> select * from t;
+------+-------+
| idx  | cname |
+------+-------+
|    1 | a     |
|    2 | b     |
|    3 | c     |
+------+-------+
3 rows in set (0.00 sec)mysql> select *,(select count(*) from t) as countnum from t;
+------+-------+----------+
| idx  | cname | countnum |
+------+-------+----------+
|    1 | a     |        3 |
|    2 | b     |        3 |
|    3 | c     |        3 |
+------+-------+----------+
3 rows in set (0.02 sec)mysql>

Answer 2

select *,(select count(*) from t) as countnum from t;

Answer 3

mysql没有Row_number之类的函数，只有通过间接方法得到

Answer 4

或者这个效率会略有差异。mysql> select t.*,a.countnum
    -> from t,(select count(*) as countnum from t) a;
+------+-------+----------+
| idx  | cname | countnum |
+------+-------+----------+
|    1 | a     |        3 |
|    2 | b     |        3 |
|    3 | c     |        3 |
+------+-------+----------+
3 rows in set (0.00 sec)mysql>

Answer 5

select t.idx,t.cname,count(*) from t;