在 mysql 中执行 UNION 时，如何对结果执行 where 操作

Question

您好，我正在对几张桌子进行联合。虽然有点长，但是很有效！

(SELECT user_id,added_date,group_id,'joined',0,0,'' FROM group_members WHERE status = 1)
UNION
(SELECT user_id,added_date,object_id,'made a comment',0,0,'' FROM comments WHERE object_type = 11 AND status = 1)
UNION
(SELECT user_id,added_date,group_id,'made the event',1,group_calendar_id,title FROM group_calendars WHERE status = 1)
UNION
(SELECT comments.user_id,comments.added_date,group_calendars.group_id,'made a comment on the event',1,group_calendar_id,'' FROM group_calendars 
        INNER JOIN comments ON group_calendars.group_calendar_id = comments.object_id WHERE group_calendars.status = 1 AND comments.status = 1 AND object_type = 10 
)
UNION
(SELECT user_id,pd.added_date,pd.object_id,'uploaded a photo',2,pd.photo_data_id,
    (SELECT varchar_val FROM photo_data WHERE data_id = 1 AND photo_data.photo_id = photos.photos_id AND object_type = 3 AND object_id = pd.object_id) 
    FROM photo_data pd 
        INNER JOIN photos ON photos.photos_id = pd.photo_id 
    WHERE photos.photo_status = 1 AND pd.status = 1 AND pd.data_id = 0 AND pd.object_type = 3
 )

UNION
(SELECT cp.user_id,cp.added_date,cp.object_id,'made a comment on the photo',2,pd.photo_data_id,
(SELECT varchar_val FROM photo_data WHERE data_id = 1 AND photo_data.photo_id = photos.photos_id AND object_type = 3 AND object_id = pd.object_id) 
 FROM comments cp 
    INNER JOIN photo_data pd ON pd.photo_data_id = cp.object_id
    INNER JOIN photos ON photos.photos_id = pd.photo_id 
 WHERE cp.object_type = 8 AND cp.status = 1 AND pd.status = 1 AND pd.data_id = 0 AND photos.photo_status = 1 AND pd.object_type = 3 
)
UNION
(SELECT user_id,added_date,group_id,'made a topic',3,forum_topic_id,title FROM forum_topics WHERE forum_categories_id = ".GROUP_FORUM_CATEGORY." AND group_id > 0 AND status = 1)
UNION
(SELECT forum_comments.user_id,forum_comments.added_date,group_id,'made a comment on the topic',3,forum_comments.forum_topic_id,title FROM forum_comments 
INNER JOIN forum_topics ON forum_comments.forum_topic_id = forum_topics.forum_topic_id 
WHERE forum_topics.forum_categories_id = 16 AND forum_topics.group_id > 0 AND forum_topics.status = 1 AND forum_comments.status = 1
)

这会获取一组组中的所有活动。我的问题是最后我想确保该小组是活跃的。

所以最后想做一些类似 WHERE (SELECT COUNT(1) FROM groups g WHERE g.group_id = group_id AND status = 1) = 1

有什么方法可以做到这一点吗？

Answer 1

我建议将其存储到视图或临时表中，然后查询视图。我知道你会有两个调用，但实际上在 mysql 中这样会更快。