abc_list
id title
1 CSDN
2 我是菜鸟
abc_content
rid id content
1 1 CSDN论坛
2 1 CSDN博客
3 1 CSDN主页
4 2 请高手帮忙
5 2 谢谢!我现在用的SQL语句 SELECT A.*,B.* FROM abc_list A LEFT JOIN abc_content B ON A.id=B.id WHERE A.mid='1' AND A.title LIKE '%CSDN%' GROUP BY B.id
这条SQL语句 倒是能用。但是无法 进行 ORDER BY 及 结果总条数 COUNT( * )如果来解决呢?请高手帮忙。
id title
1 CSDN
2 我是菜鸟
abc_content
rid id content
1 1 CSDN论坛
2 1 CSDN博客
3 1 CSDN主页
4 2 请高手帮忙
5 2 谢谢!我现在用的SQL语句 SELECT A.*,B.* FROM abc_list A LEFT JOIN abc_content B ON A.id=B.id WHERE A.mid='1' AND A.title LIKE '%CSDN%' GROUP BY B.id
这条SQL语句 倒是能用。但是无法 进行 ORDER BY 及 结果总条数 COUNT( * )如果来解决呢?请高手帮忙。
id mid title
1 1 CSDN
2 1 我是菜鸟 abc_list 表掉了字段 不好意思。
查询A.* B.content 结果总条数
比如id mid title rid id content
1 CSDN 1 1 CSDN论坛
2 我是菜鸟 1 1 CSDN论坛或者
count(*)
10
1 1 CSDN 1 CSDN论坛
2 1 我是菜鸟 4 CSDN论坛 count(*) = 2像这个样子
2 1 我是菜鸟 4 CSDN论坛 这个结果怎么来的? 取每个ID最后一条记录? 希望你能认真给出基于这些例子的正确结果和说明一下规则。mysql> select * from abc_list;
+----+------+----------+
| id | mid | title |
+----+------+----------+
| 1 | 1 | CSDN |
| 2 | 1 | 我是菜鸟 |
+----+------+----------+
2 rows in set (0.00 sec)mysql> select * from abc_content;
+-----+------+------------+
| rid | id | content |
+-----+------+------------+
| 1 | 1 | CSDN论坛 |
| 2 | 1 | CSDN博客 |
| 3 | 1 | CSDN主页 |
| 4 | 2 | 请高手帮忙 |
| 5 | 2 | 谢谢! |
+-----+------+------------+
5 rows in set (0.00 sec)mysql>
mysql> select a.*,b.*
-> from abc_list a left join abc_content b on a.id=b.id
-> where not exists (select id from abc_content where id=a.id and rid<b.rid)
;
+----+------+----------+------+------+------------+
| id | mid | title | rid | id | content |
+----+------+----------+------+------+------------+
| 1 | 1 | CSDN | 1 | 1 | CSDN论坛 |
| 2 | 1 | 我是菜鸟 | 4 | 2 | 请高手帮忙 |
+----+------+----------+------+------+------------+
2 rows in set (0.00 sec)mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)mysql>
SQL 查询: SELECT a. * , b. *
FROM abc_list a
LEFT JOIN abc_content b ON a.id = b.id
WHERE NOT
EXISTS (
SELECT id
FROM abc_content
WHERE id = a.id
AND rid & lt;b.rid
) LIMIT 0 , 30
MySQL 返回: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists ( select id from abc_content where id = a . id and rid <
我的mysql 版本是MySQL 4.0.25-nt
-> from abc_list a left join abc_content b on a.id=b.id
-> where not exists (select id from abc_content where id=a.id and rid<b.rid)
-> order by a.id
-> limit 0,30;
+----+------+----------+------+------+------------+
| id | mid | title | rid | id | content |
+----+------+----------+------+------+------------+
| 1 | 1 | CSDN | 1 | 1 | CSDN论坛 |
| 2 | 1 | 我是菜鸟 | 4 | 2 | 请高手帮忙 |
+----+------+----------+------+------+------------+
2 rows in set (0.00 sec)mysql>
AND rid & lt; 是哪来的?