如何在MySQL中创建动态限制？

Question

如何在MySQL中创建动态限制？

7

我有一个像这样的表：

// notifications
+----+--------------+------+---------+------------+
| id |      event   | seen | id_user | time_stamp |
+----+--------------+------+---------+------------+
| 1  | vote         | 1    | 123     | 1464174617 |
| 2  | comment      | 1    | 456     | 1464174664 |
| 3  | vote         | 1    | 123     | 1464174725 |
| 4  | answer       | 1    | 123     | 1464174813 |
| 5  | comment      | NULL | 456     | 1464174928 |
| 6  | comment      | 1    | 123     | 1464175114 |
| 7  | vote         | NULL | 456     | 1464175317 |
| 8  | answer       | NULL | 123     | 1464175279 |
| 9  | vote         | NULL | 123     | 1464176618 |
+----+--------------+------+---------+------------+

我试图选择至少15行特定用户的数据。有两个条件：

总是要匹配所有未读行（seen = NULL），即使它们超过了15行。
如果未读行数超过15，则还应选择2个已读行（seen = 1）。

示例：read是notifications表中已读行的数量，unread是未读行的数量。

 read | unread |          output should be           
------|--------|-------------------------------------
 3    | 8      | 11 rows                             
 12   | 5      | 15 rows (5 unread, 10 read)         
 20   | 30     | 32 rows (30 unread, 2 read)         
 10   | 0      | 10 rows (0 unread, 10 read)         
 10   | 1      | 11 rows (1 unread, 10 read)         
 10   | 6      | 15 rows (6 unread, 9 read)          
 100  | 3      | 15 rows (3 unread, 12 read)         
 3    | 100    | 102 rows (100 unread, 2 read)

这是我的当前查询，它不支持第二个条件。

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 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;

- Martin AJ

2

只需选择所有未读的并(与)15个已读的联合。之后，如果未读少于15个，则在客户端截断为15个。 - vp_arth

你的第二个子查询缺少了 seen is not null 条件。 - vp_arth

@vp_arth，你的想法包含了一些有用的观点。谢谢。 - Martin AJ

@vp_arth为什么你不写一个答案呢？我会把它作为被接受的答案。请注意，我在使用PHP作为我的服务器端语言。 - Martin AJ

6个回答

1

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

- msheikh25

哦...那是一个非常复杂的查询。我不知道它是否有效，但我更喜欢不使用它。我感觉有一种更简单的方法。无论如何，还是谢谢你的尝试，并给你一个赞。 - Martin AJ

1

只需选择所有未看到的并且（与）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条未读通知，你可以对其进行截断（客户端）。

我认为最好的做法是在获取循环中进行。
只需计算已读/未读通知数量，并在达到足够行数的时候中断循环。

一些PHP伪代码：

$read = $unread = 0;

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

- vp_arth

1

我找到了一个解决方案。为了添加第二个条件 （如果有超过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 去除重复，但没有应用其他限制。

- Martin AJ

1

现在我知道union和union all之间的区别了 :) 谢谢。 - vp_arth

1

请尝试这个：

表T按时间戳降序返回已读通知和行号。

然后从T中选择行号小于等于未读计数的15减去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

- Tin Tran

0

我可能会简化查询，并在应用程序中使用一些后处理逻辑来处理存在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

- Mike Brant

感谢您的努力，但我相信您没有理解我的观点。LIMIT 17最多只能输出17行。如果有20行未读记录会怎样呢？正如我所说，我希望始终选择所有未读记录。 - Martin AJ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- wchiquito · Accepted Answer

尝试：

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演示