如何在MySQL中进行动态限制？

我可能会简化查询，并在应用程序中使用一些后处理逻辑来处理有 14 或 15 行未读的边缘情况。只需选择最多 17 行而不是 15 行，当您在客户端应用程序中循环访问结果集时，除非第 14 行和/或第 15 行未读取，否则无需费心检索第 16 行和第 17 行。

该查询可以像以下几点一样简单：

SELECT id, event, seen, time_stamp 
FROM notifications n
WHERE id_user = :id
ORDER BY seen DESC, time_stamp DESC
LIMIT 17

SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = 123 AND seen IS NULL

UNION

(SELECT id, event, seen, time_stamp 
FROM ( 
 SELECT id, event, seen, n.id_user, time_stamp, un.CNT
    FROM notifications n
    JOIN (
        SELECT COUNT(1) CNT, id_user
        FROM notifications
        WHERE id_user = 123 and seen is NULL
        group by id_user
        ) un
    ON n.id_user = un.id_user
    WHERE CNT > 15
) t1
WHERE t1.SEEN is not NULL
LIMIT 2)

UNION

SELECT id, event, seen, time_stamp 
FROM ( 
 SELECT id, event, seen, n.id_user, time_stamp, un.CNT
    FROM notifications n
    JOIN (
        SELECT COUNT(1) CNT, id_user
        FROM notifications
        WHERE id_user = 123 and seen is NULL
        group by id_user
        ) un
    ON n.id_user = un.id_user
    WHERE CNT < 15
) t1
WHERE t1.SEEN is not NULL

只需选择所有看不见的和（与）15 看到的。

SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id AND seen IS NULL
UNION ALL
(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id AND seen IS NOT NULL
 LIMIT 15)

因此，您现在拥有所有未读通知和最多 15 个已读通知。

之后，如果看不见的少于 15 个，则可以将（客户端）截断为 15。

我认为，最好的地方是获取循环。
只需计算可见/看不见的次数，并在达到足够多的行时打破循环。

一些伪代码php：

$read = $unread = 0;

while($row = $db->fetch()) {
  if ($row['seen']) $read++;
  if (!$row['seen']) $unread++;
  // ...
  if ($weHaveEnoughRows) break;
}

我找到了解决方案。要添加第二个条件（如果未读行超过 15 个，则选择两个读取行），我必须再使用一个。像这样的东西：UNION

(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id AND seen IS NULL
)UNION
(SELECT id, event, seen, time_stamp
 FROM notification n
 WHERE id_user = :id AND seen IS NOT NULL
 LIMIT 2
)UNION 
(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id 
 ORDER BY (seen IS NULL) desc, time_stamp desc
 LIMIT 15
)
ORDER BY (seen IS NULL) desc, time_stamp desc;

第一个子查询获取所有看不见的行。第二个有两行。第三个有十五行。删除重复项，但不应用其他限制。UNION

请试试这个，

表 T 返回按time_stamp描述按行号顺序排列的已读通知。

然后，从 T 中进行选择，其中第 < 行 = GREATEST（15-Count（） of unread，2）。

然后将所有与未读合并

SELECT id,event,seen,time_stamp 
FROM 
  (SELECT id, event, seen, time_stamp,@row:=@row+1 as row 
   FROM notifications n,(SELECT @row := 0)r
   WHERE id_user = :id AND seen IS NOT NULL
   ORDER BY time_stamp desc
   )T
WHERE T.row <= GREATEST(15-
                   (SELECT COUNT(*) FROM notifications n
                    WHERE id_user = :id AND seen IS NULL),2)
UNION ALL
(SELECT id, event, seen, time_stamp 
 FROM notifications n
 WHERE id_user = :id
 AND seen is NULL
)
ORDER BY (seen IS NULL) desc,time_stamp desc

尝试：

SET @`id_user` := 123;

SELECT `id`, `event`, `seen`, `time_stamp`
FROM (SELECT `id`, `event`, `seen`, `time_stamp`, @`unread` := @`unread` + 1
      FROM `notifications`, (SELECT @`unread` := 0) `unr`
      WHERE `id_user` = @`id_user` AND `seen` IS NULL
      UNION ALL
      SELECT `id`, `event`, `seen`, `time_stamp`, @`read` := @`read` + 1
      FROM `notifications`, (SELECT @`read` := 0) `r`
      WHERE `id_user` = @`id_user` AND `seen` IS NOT NULL
            AND (
                 @`read` < (15 - @`unread`) OR
                 ((15 - @`unread`) < 0 AND @`read` < 2)
            )
) `source`;

SQL Fiddle 演示