再发昨天问题：求高手：mysql union all 两个表后怎么写分页sql语句？

[caicainiaoer]

求高手：mysql union all 两个表后怎么写分页sql语句？

sql ="Select aID as dbID,aName as dbName from ms_a";
sql = sql + " UNION all select BID as dbID,bName as dbName from ms_b";
sql = sql + " order by dbName desc limit " +(pages - 1) * countPerPage + ","+countPerPage+"";

pages是当前页数,countPerPage是每页显示多少条数据
两个表ms_a,ms_b,union all后得到:
+------+---------------------------------+
| dbID | dbName |
+------+---------------------------------+
| 10 | 北京北京楼 |
| 12 | 北京北京楼 |
| 15 | 北京北京楼 |
| 9 | 北京北京楼世界 |
| 17 | 北京北京楼北京北京北京北京楼 |
| 6 | 北京北北京北京楼 |
| 2 | 北京北京楼庄 |
| 12 | 北京北京楼看 |
| 13 | 北京北京楼北京北京楼 |
| 7 | 北京北京楼 |
| 4 | 北京北京楼北京北京楼 |
| 3 | 北京北京楼 |
| 16 | 北京北京楼北京北京楼 |
| 18 | 北京北京楼 |
| 20 | 北京北 |
| 13 | 北京北京楼 |
| 14 | 北京北京楼 |
| 5 | 川菜川 |
| 11 | 川菜川川菜川 |
| 1 | 北京北 |
+------+---------------------------------+
20 rows in set (0.13 sec)

那我想问问,怎么用limit分页呢,我上面的写的,第一页数据还正确,点下一页后就不正确了
为什么会这样?到底怎么分页呢?

谢谢各位高手,我给解决一下吧
昨天分数不多了，没人理我，刚注册一帐号来加分
帮我，解决问题揭帖！！

[caicainiaoer]

to :lsxaa(小李铅笔刀)
试试
sql =" select * from (";
sql =sql + "Select aID as dbID,aName as dbName from ms_a";
sql = sql + " UNION all select BID as dbID,bName as dbName from ms_b";
sql = sql + " ) aaa order by aaa.dbName desc limit " +(pages - 1) * countPerPage + ","+countPerPage+"";

这个少了一个as吧？as aaa,不过就是加上了也是报错,mysql可能不支持吧,我用的4,0的,甲骨文就支持这样的

[lsxaa]

试试
sql =" select * from (";
sql =sql + "Select aID as dbID,aName as dbName from ms_a";
sql = sql + " UNION all select BID as dbID,bName as dbName from ms_b";
sql = sql + " ) aaa order by aaa.dbName desc limit " +(pages - 1) * countPerPage + ","+countPerPage+"";

[lsxaa]

那就每个表分别取,结果再合在一起

[lee3f]

顶！