按 DISTINCT 列限制/偏移

发布于 2024-11-05 08:18:03 字数 638 浏览 0 评论 0原文

我需要选择所有带有所有相关标签的帖子：

SELECT p.*, pt.name AS tag_name, pt.id AS tag_id FROM posts p
LEFT JOIN posts_tags pt ON pt.post_id = p.id

所以我得到这样的结果：

 p.id | p.name | p.content | tag_name | tag_id
  1   | Yahoo  | ...       | first    | 1
  1   | Yahoo  | ...       | second   | 2
  2   | Google | ...       | second   | 2
  2   | Google | ...       | third    | 3

我知道当以这种方式选择记录时，可以通过 COUNT(p.id) 获取记录数，但我没有发现如何设置 OFFSET （从头开始跳过多少条记录）和 LIMIT （总共返回多少条记录）根据唯一帖子 ID。

现在它显然正在以这种方式工作，它正在跳过/限制记录的数量，但不是真正的帖子的数量......

原文

I need to select all posts with all their related tags:

SELECT p.*, pt.name AS tag_name, pt.id AS tag_id FROM posts p
LEFT JOIN posts_tags pt ON pt.post_id = p.id

So i get something like this:

 p.id | p.name | p.content | tag_name | tag_id
  1   | Yahoo  | ...       | first    | 1
  1   | Yahoo  | ...       | second   | 2
  2   | Google | ...       | second   | 2
  2   | Google | ...       | third    | 3

I know when selecting records this way it is possible to get number of records through COUNT(p.id) for example, but i didn't discover how to set OFFSET (how many records skip from the beginning) and LIMIT (how many records return at all) according to unique post ID.

Now it's obviously working in the way, that it's skipping/limiting the number of records, but not the number of real posts...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

飘落散花 2024-11-12 08:18:03

如果我理解正确，您需要从偏移量 O 开始的 N 个帖子的所有“标签”行：

   SELECT p.*, pt.name AS tag_name, pt.id AS tag_id
     FROM (SELECT * FROM posts ORDER BY id ASC LIMIT {N} OFFSET {O}) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id

附录

这是一种使用以下方法来实现的方法： DENSE_RANK 仅受帖子本身限制：

   SELECT p.id, p.name, p.content, pt.name as tag_name, pt.id AS tag_id
     FROM (SELECT DENSE_RANK() OVER (ORDER BY id) AS dr, posts.*
             FROM posts) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id
    WHERE dr BETWEEN {N} AND {N + O}

If I understand you correctly, you want all "tag" rows for N posts starting at offset O:

   SELECT p.*, pt.name AS tag_name, pt.id AS tag_id
     FROM (SELECT * FROM posts ORDER BY id ASC LIMIT {N} OFFSET {O}) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id

Addendum

Here's one way to do it using DENSE_RANK to limit just by the posts themselves:

   SELECT p.id, p.name, p.content, pt.name as tag_name, pt.id AS tag_id
     FROM (SELECT DENSE_RANK() OVER (ORDER BY id) AS dr, posts.*
             FROM posts) p
LEFT JOIN posts_tags pt
          ON pt.post_id = p.id
    WHERE dr BETWEEN {N} AND {N + O}

回复收藏 0 原文

送君千里 2024-11-12 08:18:03

我对 PostgreSQL 不太熟悉，但是（如果我正确理解你的问题）我认为 ROW_NUMBER() 是正确的起点。像这样的事情：

SELECT
  Tag_Name, Tag_ID
FROM
  (
  SELECT P.ID, P.Name AS Tag_Name, P.Content, ROW_NUMBER() OVER (ORDER BY P.ID, PT.Tag_ID) AS RowNum
  FROM Posts AS P LEFT JOIN Posts_Tags AS PT ON P.Post_ID = P.ID
  ) AS X
WHERE
  RowNum BETWEEN 101 AND 200

这应该可以在 MS SQL 中解决问题，您可能需要调整语法。

I'm not very familiar with PostgreSQL, but (if I understand your question right) I think ROW_NUMBER() is the right place to start. Something like this:

SELECT
  Tag_Name, Tag_ID
FROM
  (
  SELECT P.ID, P.Name AS Tag_Name, P.Content, ROW_NUMBER() OVER (ORDER BY P.ID, PT.Tag_ID) AS RowNum
  FROM Posts AS P LEFT JOIN Posts_Tags AS PT ON P.Post_ID = P.ID
  ) AS X
WHERE
  RowNum BETWEEN 101 AND 200

This should do the trick in MS SQL, you may need to adjust the syntax.

回复收藏 0 原文

~没有更多了~