我的目的是得到各个subject前三名的记录。两种写法:
(1)select *
from report t
where (select count(1)+1 from report where subject=t.subject and >t.)<=3
(2)select *
from report as t1,report as t2
where (select count(1)+1 from t1 where t1.subject=t2.subject and t1.>t2.)<=3
第一种成功了,第二种错误提示。高手们,能解释一下为什么吗?
report表:
+----+---------+-------+
| ID | subject | |
+----+---------+-------+
| 1 | 地理 | 71.00 |
| 2 | 地理 | 59.00 |
| 3 | 地理 | 42.00 |
| 4 | 地理 | 48.00 |
| 5 | 地理 | 66.00 |
| 6 | 地理 | 55.00 |
| 1 | 中文 | 65.00 |
| 2 | 中文 | 48.00 |
| 3 | 中文 | 84.00 |
| 4 | 中文 | 58.00 |
| 5 | 中文 | 74.00 |
| 6 | 中文 | 98.00 |
| 1 | 历史 | 45.00 |
| 2 | 历史 | 74.00 |
| 3 | 历史 | 86.00 |
| 4 | 历史 | 62.00 |
| 5 | 历史 | 52.00 |
| 6 | 历史 | 96.00 |
+----+---------+-------+
(1)select *
from report t
where (select count(1)+1 from report where subject=t.subject and >t.)<=3
(2)select *
from report as t1,report as t2
where (select count(1)+1 from t1 where t1.subject=t2.subject and t1.>t2.)<=3
第一种成功了,第二种错误提示。高手们,能解释一下为什么吗?
report表:
+----+---------+-------+
| ID | subject | |
+----+---------+-------+
| 1 | 地理 | 71.00 |
| 2 | 地理 | 59.00 |
| 3 | 地理 | 42.00 |
| 4 | 地理 | 48.00 |
| 5 | 地理 | 66.00 |
| 6 | 地理 | 55.00 |
| 1 | 中文 | 65.00 |
| 2 | 中文 | 48.00 |
| 3 | 中文 | 84.00 |
| 4 | 中文 | 58.00 |
| 5 | 中文 | 74.00 |
| 6 | 中文 | 98.00 |
| 1 | 历史 | 45.00 |
| 2 | 历史 | 74.00 |
| 3 | 历史 | 86.00 |
| 4 | 历史 | 62.00 |
| 5 | 历史 | 52.00 |
| 6 | 历史 | 96.00 |
+----+---------+-------+
'截至2011-08-21 11:54:14 用户结帖率100.00% 总发帖:2 正常结帖:0
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖
ERROR 1146 (42S02): Table 'vbadb.t1' doesn't exist
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
........以下是关于table_references 的定义:
table_references:
table_reference [, table_reference] ...table_reference:
table_factor
| join_tabletable_factor:
tbl_name [[AS] alias] [index_hint]
| table_subquery [AS] alias
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }从上面可以看出,from 后面接的 可能是【表】 或者一个 【子查询 as 别名】,但肯定没有接别名的。