Question

mysql两张表的数据查询

我有两张表，两张表table1和table2靠id关联，其中table1有一个字段a 和更新时间b，tablse2有一个字段c，d（d值为1，2，3中一个），当d=1时，两张表中的a和c都相等的话就取更新时间最近的数据，我写了一个sql是对的，
SELECT id FROM (SELECT * FROM table1 ORDER BY b DESC )c
GROUP BY a,(SELECT c FROM table2
AS d WHERE d=1 AND d.id=c.id)可是程序不支持这种sql语句，希望有知道的朋友帮下忙，谢啦，解决的我会加分的

Answer 1

你的语句是错的，试试这个：

select id
from table1 T
where b= (select max(b) from table1 T1, table2, T2 where T2=1 and T1.a=T2.c and T.a=T1.a group by T1.a)

Answer 2

SELECT t1.id FROM table1 as t1, table2 as t2 WHERE t2.d=1 AND t1.a = t2.c ORDER BY t1.d DESC
我没有相应的表可以测试 你可以试一下

追问

。。。你给的是两张表的关联，我还要去除重复得数据。。。

追答

可以在SELECT 后面的 t1.id 加上DISTINCT
SELECT DISTINCT t1.id FROM table1 as t1, table2 as t2 WHERE t2.d=1 AND t1.a = t2.c ORDER BY t1.d DESC

Answer 3

select oo.a,oo.b,pp.* from table1 oo,table2 pp where oo.a = pp.c and pp.d = 1 order by oo.b desc;

Answer 4

select * from table1 inner join table2 where table2.d=1 and table2.c=table1.a
按你说的要求，这样肯定行