SQL 查询选择语句 [英] SQL query select statemenet

问题描述

请看小提琴:

http://sqlfiddle.com/#!2/de208/1

到目前为止，我有以下 SQL 语句，我认为它可以正常工作，但现在我发现了一个问题案例.

 SELECT Count(id) FROM data WHERE user = 2 AND seen = 0与日期 >(SELECT coalesce(MAX(date),0) FROM data WHERE user = 2 AND seen <> 0);

A 部分)在上面的小提琴中，这很有效，它产生的计数为 2 - 最不可见的帖子.问题是最近未看到的帖子在被看到之前已被删除，因此它也将保持不可见.所以下一次有数据，并且执行这个查询时，它会再次计数.应该只计算一次的地方.

B 部分)因此，当执行下一个小提琴(添加了几行新行)时，结果是 3 个看不见的帖子，而实际上它应该是 2 个.http://sqlfiddle.com/#!2/1e750b/1

我认为只有在最近未看到的帖子(如 A 部分小提琴中的记录 677)被删除后才会出现问题.因此，最好仅将已删除的帖子包含在计数中，如果它们是最近未看到的帖子，而不是出现在未看到的未删除帖子之前(如 B 部分小提琴中的记录 677).

我希望这是有道理的.

解决方案

重新计数问题可能发生在查询再次运行(无论是否已删除)时仍未看到的任何帖子中，因此我认为您需要保留跟踪上次运行查询的时间，只计算新条目

SELECT Count(id) FROM data WHERE user = 2 AND seen = 0与日期 >(SELECT coalesce(MAX(date),0) FROM data WHERE user = 2 AND seen <> 0)与日期 >last_count_date

<块引用>

我认为只有在最近未看到的帖子时才会出现问题(如 A 部分小提琴中的记录 677)被删除的帖子.所以它会最好只在计数中包含已删除的帖子，如果它们是最多的最近看不见的帖子，而不是出现在一个看不见的未删除的帖子之前(如 B 部分小提琴中的记录 677).

我不确定这是不是真的.假设在运行计数查询之间添加了以下 2 行:deleted unseen 其次是 unseen.在这种情况下，已删除的未见帖子不是最新的帖子，但仍应包含在计数中.

Please see fiddle:

http://sqlfiddle.com/#!2/de208/1

So far I have the following SQL statement which I thought works fine, except now I have found one problem case.

 SELECT Count(id) FROM data WHERE user = 2 AND seen = 0
 AND date > (SELECT coalesce(MAX(date),0) FROM data WHERE user = 2 AND seen <> 0);

Part A) In the above fiddle this works well, it produces a count of 2 - the most unseen posts. The problem is that the most recent unseen post has been deleted before it had been seen, so it will also remain unseen. So the next time there is data, and this query is executed it will be counted again. Where it should only be counted once.

Part B) So by the time the next fiddle (with a couple of new rows added) is executed the result is a count of 3 unseen posts, when in fact it should be 2. http://sqlfiddle.com/#!2/1e750b/1

I think the problem would only occur if the most recent unseen posts (as in record 677 in Part A fiddle) are deleted post. So it would be good to only included deleted posts in the count, if they are the most recent unseen post(s), and not come before an unseen non-deleted post (as in record 677 in Part B fiddle).

I hope that makes some sense.

解决方案

The recount problem could happen with any post that remains unseen by the time the query runs again (whether it's deleted or not), so I think you need to keep track of the last time the query was run and only count new entries

SELECT Count(id) FROM data WHERE user = 2 AND seen = 0
AND date > (SELECT coalesce(MAX(date),0) FROM data WHERE user = 2 AND seen <> 0)
AND date > last_count_date

I think the problem would only occur if the most recent unseen posts (as in record 677 in Part A fiddle) are deleted post. So it would be good to only included deleted posts in the count, if they are the most recent unseen post(s), and not come before an unseen non-deleted post (as in record 677 in Part B fiddle).

I'm not sure that's true. Suppose the following 2 rows are added between running count queries: deleted unseen followed by unseen. In this case the deleted unseen post is not the most recent one but should still be included in the count.

这篇关于SQL 查询选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！