比如:表News
NewsID Title ClassID
1 A 1
2 B 2
3 C 2
4 D 3
5 E 4
6 F 2
7 G 2
执行以下查询
Select * from News where ClassID=2那么结果肯定是:
NewsID Title ClassID
1 2 B 2
2 3 C 2
3 6 F 2
4 7 G 2
也就是说我想获取NewsID=3的记录在上面结果集中的顺序,很明显上述结果集中是2。
请达人指教,谢谢!
NewsID Title ClassID
1 A 1
2 B 2
3 C 2
4 D 3
5 E 4
6 F 2
7 G 2
执行以下查询
Select * from News where ClassID=2那么结果肯定是:
NewsID Title ClassID
1 2 B 2
2 3 C 2
3 6 F 2
4 7 G 2
也就是说我想获取NewsID=3的记录在上面结果集中的顺序,很明显上述结果集中是2。
请达人指教,谢谢!
select id
from (
Select @n:=@n+1 as id,* from News where ClassID=2) k
where NewsID=3;
mysql> select * from news;
+--------+-------+---------+
| newsid | title | classid |
+--------+-------+---------+
| 1 | A | 1 |
| 2 | B | 2 |
| 3 | C | 2 |
| 4 | D | 3 |
| 5 | E | 4 |
| 6 | F | 2 |
| 7 | G | 2 |
+--------+-------+---------+
7 rows in set (0.00 sec)mysql> select a.*,(select count(*) from news where newsid<=a.newsid and
-> news.classid=2) ct
-> from
-> (select * from news where classid=2) a;
+--------+-------+---------+------+
| newsid | title | classid | ct |
+--------+-------+---------+------+
| 2 | B | 2 | 1 |
| 3 | C | 2 | 2 |
| 6 | F | 2 | 3 |
| 7 | G | 2 | 4 |
+--------+-------+---------+------+
4 rows in set (0.00 sec)