mysql - 如果有 n 个不同的产品，则从每个产品中选择 1 个图像，如果没有，则从每个产品中选择超过 1 个图像，直到达到 n

Question

在项目的设计方案中，一个product可能有多个image。

现在我想从 product 中选择 n 个 image 的情况:

• 如果定义了 n 个 product，则从每个中选择 1 个 image。
• 否则从每个 product 中选择更多 image 限制 n

我们还需要 PHP 辅助操作来实现目标吗？

架构符合预期:

product (id, title, ...)
image (id, product_id, filename, ...)

我什至想不出像这样的查询，这就是为什么我没有尝试任何不幸的原因。

查询应该如下所示:

SELECT * FROM image ..with..those..hard..conditions.. LIMIT n

Answer 1

如果我理解得很好——您需要 n 个图像。如果可能，来自不同的产品。否则，可以接受来自同一产品的多张图片作为后备。

从现在开始，我能想到的唯一解决方案是建立一个临时表，其中包含编号的行，例如每个产品的“图像”位于“顶部”——并与其余产品一起归档图片。

构建该表后，您的查询只是一个SELECT ... LIMIT n。

这将执行得很糟糕——如果您选择灵感的解决方案——您应该离线或按计划合并图像表。

参见 http://sqlfiddle.com/#!2/81274/2

--
-- test values
--
create table P (id int, title char(20));
insert into P values
  (1, "product 1"),
  (2, "product 2"),
  (3, "product 3");

create table I (pid int, filename char(40));
insert into I values
   (1, "image p1-1"),
   (1, "image p1-2"),
   (3, "image p3-1"),
   (3, "image p3-2"),
   (3, "image p3-3");

--
-- "ordered" image table
--
create table T(n int primary key auto_increment not null,
                         filename char(20));


--
-- consolidate images (once in a while)
--
delete from T;
insert into T(filename)
    select filename from I group by pid;
insert into T(filename)
    select filename from I order by rand();

--
-- do the actual query
--
select * from T limit n;

编辑 这是一个完全不同的想法。不使用合并表/ View ——因此这可能被视为更好:

http://sqlfiddle.com/#!2/57ea9/4

select distinct(filename) from
    (select 1 as p, filename from I group by pid
     union (select 2 as p, filename from I order by rand() limit 3)) as T
order by p limit 3

这里的关键点是我不必真的对行进行“编号”。仅跟踪哪些行来自第一个 SELECT。这就是 p 的目的。为简单起见，我将两个 LIMIT 子句设置为相同的值。我不认为你必须“优化”那部分，因为好处会非常小——而且 ORDER BY RAND() 太糟糕了，你不必考虑“性能”这里 ;)

请注意，我尚未完全测试此解决方案。让我知道是否有一些极端情况(好吧..任何情况)不起作用。