基于列如何分配记录

Question

我有一个存储竞赛参赛作品的表格。参赛者必须输入文字，可以选择上传照片。

当我在页面上显示作品时，每次分页显示9个作品。

如何尽可能地确保每个页面至少包含一张带有照片的作品（假设有足够的带有照片的作品供每个页面使用）？平均分配带有照片的作品可能已经足够了。

- Mark

---

你并不真的在意展示顺序，只是希望照片能够均匀分布，是吗？ - Strawberry

正确 - 最好按提交顺序，但并不是非常重要 - Mark

可能将以下示例从Oracle SQL转换可能会指向一个解决方案，但我不确定在mysql中是否可能 - http://hoopercharles.wordpress.com/2009/12/09/sql-–-row_number-analytical-function-mod-even-distribution/ （需要复制并粘贴链接，因为SO无法处理奇怪的URL） - Mark

我有一个想法：只需选择没有图片的X=8条记录，并与返回带有图片的Y=1条记录的选择进行UNION。您还可以使这些X和Y数字动态化，并通过某个计算总图片和非图片行数的函数来计算它们，以获得更好的分布。 - Kamil

Answer 1

我建议您随机排序行：

order by rand()

这并不保证每一页都有照片，但它会有所帮助。

另一种选择是像这样做：

select *, @seqnum:=@seqnum+1
from t
where nophoto
select *, @seqnum:=@seqnum+8
from t
where photo

然后按seqnum排序。让这变得麻烦的是处理每页少于一张照片和多于一张照片的情况。随机方法可能已经足够了。

- Gordon Linoff

---

我不确定这会有帮助，因为无论如何它都可能是随机分布——就像照片记录没有被分组一样。 - Mark

Answer 2

对于每一页，执行以下操作（例如对于第3页，页面大小为10）：

select ...
from ...
where has_photo
order by created
limit 3, 1
union
select ...
from ...
where not has_photo
order by created
limit 27, 9

这个查询将两种类型的行分成两个单独的查询，然后通过 union 重新组合。

- Bohemian

---

我曾考虑过这个问题，但前提是我必须知道照片与非照片的比例才能实现。 - Mark

先执行一个查询以了解情况。 - Bohemian

Answer 3

这是我最近看到的比较具有挑战性的问题之一 - 谢谢！我无法使用单个SQL语句使其工作，但我似乎可以像这样使其工作。基本上，它尝试确定将返回多少结果，然后其中有多少带有照片，并使用页面数的照片百分比除以（使用CEILING确保前几页至少有一页）。

无论如何，让我们开始吧：

SET @page = 1;
SET @resultsPerPage = 9;

SELECT @recCount:= COUNT(Id) as RecCount
FROM Entries;

SELECT @photoCount:= COUNT(Photo) as PhotoCount
FROM Entries
WHERE Photo IS NOT NULL;

SET @pageCount = CEILING(@recCount/@resultsPerPage);
SET @photosPerPage = CEILING(@photoCount/@pageCount);
SET @nonPhotosPerPage = @resultsPerPage - CEILING(@photosPerPage);

SELECT *
FROM (
      SELECT *, 
        @rownum := @rownum + 1 row_number
      FROM Entries JOIN    (SELECT @rownum := 0) r
      WHERE Photo IS NOT NULL
   ) a 
WHERE a.row_number > (@photosPerPage*(@page-1))
   and a.row_number <= (@photosPerPage*(@page))
UNION 
SELECT *
FROM (
      SELECT *, 
        @rownum2 := @rownum2 + 1 row_number
      FROM Entries JOIN    (SELECT @rownum2 := 0) r
      WHERE Photo IS NULL
   ) b
WHERE b.row_number > (@nonPhotosPerPage*(@page-1))
   and b.row_number <= (@nonPhotosPerPage*(@page))

还有SQL Fiddle。

祝你好运！

- sgeddes