MySQL的一个输出中有两个不同的表 - Two different tables in one output in MySQL

Two different tables in one output in MySQL

本文关键字：两个输出一个 MySQL | 更新日期: 2023-09-27

SELECT t.dateline AS date,t.tid, t.subject, u.avatar,t.views, t.username, t.replies, u.profilepic, t.uid, p.thumbsup, t.firstpost, f.name, f.fid, p.message, a.updatetime, a.md5hash, a.uploadtime, a.aid, a.attachname, a.filename, a.thumbs, td.vidid, td.cat, td.portada
FROM ". TABLE_PREFIX ."threads t 
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid)
LEFT JOIN ".TABLE_PREFIX."xtattachments a ON (a.tid=t.tid)
LEFT JOIN ".TABLE_PREFIX."threadfields_data td ON (td.tid=t.tid)
LEFT JOIN ".TABLE_PREFIX."posts p ON (p.pid=t.firstpost)
WHERE t.fid IN ($f4id) AND t.uid IN ($show_post_list)  
UNION ALL
SELECT th.dateline AS date, th.thumbsup, th.uid
FROM ". TABLE_PREFIX ."thumbspostrating th 

ORDER BY date DESC 
LIMIT ".(($page-1)*$perpage).", ".$perpage);

的例子:

线程号日期:今天
Like 1 dateline:昨天下午1:10
线程号2日期线:昨天1:09pm
Like 2 dateline:昨天下午1:08
Like 3 dateline:昨天下午1:07
线程号3日期线:昨天1:05pm

我不知道为什么这不起作用?在ORDER BY <—我需要t.dateline和l.dateline在一个做这个例子。

1222 -使用的SELECT语句有不同的列数

您可以尝试将add as dateline添加到select .dateline as dateline, l.name, l.avatar

这对我有用。如果这不起作用你可以在select语句中输入select语句然后对结果进行排序它会读成这样

SELECT dateline, name, avatar FROM ( 
  SELECT t.dateline, t.name, u.avatar
  FROM ". TABLE_PREFIX ."threads t 
  LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
  UNION ALL
  SELECT l.dateline, l.name, l.avatar
  FROM ". TABLE_PREFIX ."likes l 
  LEFT JOIN ".TABLE_PREFIX."treads t ON (t.uid=l.uid)
) as x
ORDER BY x.dateline DESC 
LIMIT 10;

ORDER BY仅应用于第二个查询:

Select * From (
SELECT t.dateline, t.name, u.avatar
FROM ". TABLE_PREFIX ."threads t 
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
UNION ALL
SELECT l.dateline, l.name, l.avatar
FROM ". TABLE_PREFIX ."likes l 
LEFT JOIN ".TABLE_PREFIX."treads t ON (t.uid=l.uid) ) allData
ORDER BY allData.dateline DESC 
LIMIT 10;