请教一个多表查询问题
我有两个表,一个表A,一个表B
表A里有两个字段:tid(int)和authorid(int)
表B里有三个字段:tid(int)、title(char)和lasttime(int)
现在我要先根据authorid=41这个条件,查出A表里所有符合条件的tid(剔除重复项):
select distinct tid from A where authorid=41
然后,再从B表里查出title字段的值,条件是tid等于从A表里查出的tid,并且按照lasttime字段进行降序排列
select title from B where B.tid=A.tid order by lasttime desc
[我写的两个SQL语句只是为了把问题描述清楚]
请问这个思路应该怎么构造SQL语句呢?
[解决办法]
忘记排序了。。
SELECT title FROM BINNER JOIN (SELECT DISTINCT tid FROM A WHERE A.authorid=41) tmp ON B.tid=tmp.tid ORDER BY B.lasttime DESC
[解决办法]
mysql> select * from a;+------+----------+| tid | authorid |+------+----------+| 1 | 41 || 2 | 41 || 3 | 41 || 3 | 41 || 4 | 55 |+------+----------+5 rows in set (0.00 sec)mysql> select * from b;+------+---------+----------+| tid | subject | lastpost |+------+---------+----------+| 1 | aa | 11 || 2 | bb | 22 || 3 | cc | 33 || 4 | dd | 44 || 5 | ee | 55 |+------+---------+----------+5 rows in set (0.00 sec)mysql> select * from b -> where exists (select 1 from a where tid=b.tid and authorid=41) -> order by lastpost desc -> ;+------+---------+----------+| tid | subject | lastpost |+------+---------+----------+| 3 | cc | 33 || 2 | bb | 22 || 1 | aa | 11 |+------+---------+----------+3 rows in set (0.00 sec)mysql>
[解决办法]
或者
mysql> select * from b -> where tid in (select tid from a where authorid=41) -> order by lastpost desc;+------+---------+----------+| tid | subject | lastpost |+------+---------+----------+| 3 | cc | 33 || 2 | bb | 22 || 1 | aa | 11 |+------+---------+----------+3 rows in set (0.00 sec)mysql>
