mysql - 在多个 LEFT JOIN 查询上使用 ORDER BY

Question

我有一个很长的查询，我终于开始工作了，但我无法ORDER BY date_time_add，该字段存在于除user_accounts之外的所有表中和关系表。我怎样才能让它正常工作？

$sql = "select distinct pb.user_id, pb.Full_name,
tv.why_onsite3, tv.onsite3_id, tv.other_date as onsite3_date, 
tv.user_allowed as tv_user_allowed, np.onsite4_name ,  
np.onsite4_id, np.other_date as onsite4_date, np.user_allowed 
as np_user_allowed, pml.med_name , pml.med_id, pml.other_date 
as pml_date, pml.user_allowed as pml_user_allowed, pl.onsite5_name,
pl.onsite5_test_id, pl.other_date as some_stats_date, pl.user_allowed as   
pl_user_allowed, chlp.problem_name_is , chlp.current_problem_id, 
chlp.other_date as chlp_date, chlp.user_allowed as chlp_user_allowed,
pphl.onsite10_health_prob_id , pphl.onsite10_problem_name_is, 
pphl.other_date as pphl_date, pphl.user_allowed as pphl_user_allowed,
al.onsite_id , al.onsite_name, al.other_date as onsite_date, 
al.user_allowed as al_user_allowed, sl.onsite2_id , sl.onsite2_name, 
sl.other_date as onsite2_date, sl.user_allowed as sl_user_allowed,
hal.onsite6_id , hal.reason_for_admit, hal.other_date as hal_date, 
hal.user_allowed as hal_user_allowed, il.onsite9_id , il.onsite9_name, 
il.other_date as il_date , il.user_allowed as il_user_allowed
from user_accounts pb left join some_stuff tv on pb.user_id = tv.user_id
left join some_onsite4s np on pb.user_id = np.user_id
left join some_med pml on pb.user_id = pml.user_id
left join list_hal hal on pb.user_id = hal.user_id
left join list_for_things il on pb.user_id = il.user_id
left join list_on  sl on pb.user_id = sl.user_id
left join some_all al on pb.user_id = al.user_id
left join some_list  pphl on pb.user_id = pphl.user_id
left join some_stats pl on pb.user_id = pl.user_id
left join some_probs chlp on pb.user_id = chlp.user_id

where (pb.user_id in (select fb.friend_id from relationships fb 
where fb.user_id = '$uid')
or pb.user_id in (select fb1.user_id from relationships fb1 
where fb1.friend_id = '$uid')
)

group by pb.user_id ORDER BY date_time_added DESC LIMIT $startrow, 20";

Answer 1

在 ORDER BY 子句中，您必须指定要排序的确切列。这意味着您必须为用于排序的列添加前缀，因为您有多个在多个表中调用相同的列。

另一个选项是完全重构查询并在多个 SELECT 语句中使用 UNION 运算符。每个 SELECT 语句都会从一个表中选取一组数据，并按该表中的列对该组进行排序。

解决方案取决于您想要输出的数据 - 数据的上下文。