多表关联查询如何获取副表最新的一条数据？

Question

多表关联查询，对于副表怎么才能获取到MAX(t_track.t_id)对应的一条数据？

代码如下，查询结果，t_track.t_content,t_track.t_dateline这两个字段得出的数据都对不上MAX(t_track.t_id)的那条数据，应该怎么改呢？只用一条语句解决问题。在线等。。。

SELECT t_sales.t_id,t_sales.t_company, MAX(t_track.t_id) as track_id,t_track.t_content,t_track.t_dateline...

完整的语句:

SELECT 

`t_sales`.`t_id`,
`t_sales`.`t_company`,
`t_sales`.`t_fenlei`, 
`t_sales`.`t_tel`, 
`t_sales`.`province`, 
`t_sales`.`city`, 
MAX(t_track.t_id) as track_id, 
`t_track`.`t_content`, 
`t_track`.`t_dateline`, 
`t_city_1`.`city_name`, 
`t_user`.`t_realname` 

FROM `t_sales` 

right join `t_track` 
ON t_sales.t_id = t_track.t_sale_id 

left join `t_city_1` ON t_sales.t_city = t_city_1.t_id 
left join `t_user` ON t_sales.t_for_sale = t_user.t_id 

WHERE (
    (
        (`t_track`.`t_uid`='214') AND (`t_user`.`t_id`='214')
    ) AND (
        t_sales.t_id IS NOT NULL
    )
) AND (
    t_track.t_sale_id IS NOT NULL
) 

GROUP BY `t_track`.`t_sale_id` 

ORDER BY `t_track`.`t_dateline` DESC

Answer 1

SELECT `t_sales`.`t_id`, `t_sales`.`t_company`, `t_sales`.`t_fenlei`, `t_sales`.`t_tel`, `t_sales`.`province`, `t_sales`.`city`, MAX(t_track.t_id) as track_id, `t_track`.`t_content`, `t_track`.`t_dateline`, `t_city_1`.`city_name`, `t_user`.`t_realname` FROM `t_sales` right join (select *from `t_track` ORDER BY `t_track`.`t_dateline` DESC) `t_track` ON t_sales.t_id = t_track.t_sale_id left join `t_city_1` ON t_sales.t_city = t_city_1.t_id left join `t_user` ON t_sales.t_for_sale = t_user.t_id WHERE (((`t_track`.`t_uid`='214') AND (`t_user`.`t_id`='214')) AND (t_sales.t_id IS NOT NULL)) AND (t_track.t_sale_id IS NOT NULL) GROUP BY `t_track`.`t_sale_id`

Answer 2

MAX(t_track.t_id)写到条件里面去

没有提供更多信息，暂且用t_sales.t_id和t_track.t_id来关联两个表

SELECT t_sales.t_id,t_sales.t_company, t_track.t_id t_track.t_content,t_track.t_dateline from t_sales，t_track where t_sales.t_id=t_track.t_id and t_track.t_id=(select MAX(t_id) from t_track)

Answer 3

1. ORDER BY t_track.t_id DESC

2. 鉴于你这语句在MySQL严格模式下根本无法执行，建议：（类似）

将先筛选出来的结果当JOIN对象
SELECT t_sales.* FROM t_sales INNER JOIN (
    SELECT * FROM t_track WHERE t_id IN (
        SELECT MAX(t_tract.tid) FROM xxxx GROUP BY t_track.t_id)
     )
) AS b ON b.t_id = ...

然后我不想答了，这语句又长又复杂，JOIN了3个表，条件框了N多括号，我感觉优化都有不少空间，就别说取值了

Answer 4

SELECT t_sales.t_id,t_sales.t_company, t_track.t_id as track_id,t_track.t_content,t_track.t_dateline... ORDER BY t_track.t_id DESC;