MySQL Query 请教

[hyrongg]

Example code

create 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 如何得到？

谢谢

[ACMAIN_CHM]

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>

[WWWWA]

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

[WWWWA]

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

[ACMAIN_CHM]

或者这个效率会略有差异。

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>

[hyrongg]

50多万，这样整效率如何？

我先测试一下，谢

[阿_布]

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

[hyrongg]

效率还可以，揭帖，

自己应该想到的，晕了