请问一个多表查询有关问题

请教一个多表查询问题

我有两个表,一个表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语句呢?

忘记排序了。。

SQL code

SELECT title 
FROM B
INNER JOIN (SELECT DISTINCT tid FROM A WHERE A.authorid=41) tmp 
ON B.tid=tmp.tid 
ORDER BY B.lasttime DESC



  
  

   
   SQL code
   
   
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>


或者


    
    

     
     SQL code
     
     

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>