php - UNION ALL - JOIN 如何限制为 1

Question

尝试限制每行...在我的查询中，我得到结果和结果图像，但是如果有，例如:成员(member)有 2 张照片，它将显示两张照片，如果 ttimages 有 30 张照片，它将导致显示 30 张照片等

我想要达到的是每个结果的限制......用一张图片显示表中的一个结果，而不是 x 数量，从而得出图像表中有多少张图片。

这里是sql...

$sql = "
SELECT DISTINCT members.lastlogin as timeline_date, members.memusername as 
timeline_title, members.town as timeline_body, memberimages.images as 
timeline_img FROM members JOIN memberimages ON members.memid = 
memberimages.memid 
UNION ALL
SELECT DISTINCT ttages.dateadded as timeline_date, ttages.title as 
timeline_title, ttages.body as timeline_body, ttages_images.image as 
timeline_img FROM ttages JOIN ttages_images ON ttages.agesid = 
ttages_images.ageid
UNION ALL
SELECT DISTINCT sellit_listings.dateadded as timeline_date, 
sellit_listings.sittitlename as timeline_title, sellit_listings.sitcontent as 
timeline_body, sellit_images.images as timeline_img FROM sellit_listings JOIN 
sellit_images ON sellit_listings.listid = sellit_images.listid
UNION ALL
SELECT DISTINCT property_listings.dateadded as timeline_date, 
property_listings.paddress as timeline_title, property_listings.plistdiscript 
as timeline_body, property_images.image as timeline_img FROM 
property_listings JOIN property_images ON property_listings.propid = 
property_images.propid
UNION ALL
SELECT DISTINCT event_listings.dateadded as timeline_date, 
event_listings.eventtitle as timeline_title, event_listings.eventdescript as 
timeline_body, event_images.image as timeline_img FROM event_listings JOIN 
event_images ON event_listings.eventid = event_images.eventid
ORDER BY timeline_date DESC  LIMIT 70";

Answer 1

如果您想要每个成员、对象等一个结果行，则按它们分组:

SELECT
  m.lastlogin as timeline_date,
  m.memusername as timeline_title,
  m.town as timeline_body,
  MIN(mi.images) as timeline_img 
FROM members m
JOIN memberimages mi ON m.memid = mi.memid
GROUP BY m.memid
UNION ALL
...

这是标准 SQL。某些 DBMS 可能需要在所有列上使用伪聚合函数(即 MIN(m.laSTLogin) 而不是 m.laSTLogin 等)或将它们放在 GROUP BY 子句中不过。