php - 如何合并两个具有相同列名称的画廊

Question

我有 2 个具有相同结构的表:

    //gallery
id | user_id | image | type
    //gallery_default
id | user_id | image | type | folder

在“gallery”表中，我保存了用户上传的图像，在“gallery_default”中，我保存了来自服务器的预定义图像(图像名称和文件夹类型)。

我想连接两个具有相同列名和相同类型的表。

示例

(SELECT `image`, '1' AS `t` 
 FROM `gallery` 
 WHERE `user_id`='".$u->get('id')."' 
 AND `type` LIKE 'type1' 
 ORDER BY RAND() LIMIT 2)

UNION

(SELECT `image`, '2' AS `t` 
 FROM `gallery` 
 WHERE `user_id`= '" . $u->get('id') . "' 
 AND `type` LIKE 'type2' 
 ORDER BY RAND() LIMIT 2)

UNION

(SELECT `image`, '3' AS `t` 
 FROM `gallery` 
 WHERE `user_id` = '" . $u->get('id') . "' 
 AND `type` LIKE 'type3' 
 ORDER BY RAND() LIMIT 2)

UNION

(SELECT `image`, '4' AS `t` 
 FROM `gallery` 
 WHERE `user_id` = '" . $u->get('id') . "' 
 AND `type` LIKE 'type4' 
 ORDER BY RAND() LIMIT 2)

ORDER BY `t` ASC 
LIMIT 8

-这适用于一个“gallery”表，但如何添加第二个“gallery_default”作为第一个表的一部分。

Answer 1

嗯，

这个查询应该适合你:

SELECT * FROM (
  SELECT image, type, 1 AS t FROM gallery
  UNION
  SELECT image, type, 2 AS t FROM gallery_default ORDER BY RAND()
) AS all_galleries
GROUP BY type, t

想法是将两个结果合并到一个派生表中，然后按类型GROUP 结果，然后 t.这将确保每种类型获得 2 张图片，一张来自 galleries，一张来自 default_galleries。

希望对您有所帮助。