加入两个单独的查询在PostgreSQL的... ...查询（可以或不可以）

问题描述

我得到了一些很大的帮助过的Returning在PostgreSQL的查询第一个X记录有与我有，并试图像我一样可以优化现在（虽然我通常不是一个大风扇的一个问题，一个独特的领域前端装载优化，这是一种独特的情况）。

I got some great help over at Returning the first X records in a postgresql query with a unique field with an issue that I'm having and trying to optimize as much as I can for now (though I'm usually not a big fan of front-loading the optimization, this is kind of a unique situation).

在一个应用程序想象一下三个实体：

Imagine three entities in an app:

User
Post
Instance # An instance is just a reference to a post

字段是这个样子：

The fields look something like this:

User
  id
Post
  id
  user_id
  name
Instance
  id
  user_id
  post_id
  helped_by_user_id

要求

返回10个实例，其中：

Return 10 instances where:

1. USER_ID不等于3


2. POST_ID是独一无二
   
3. 有没有其他的实例与POST_ID和3
的helped_by_user_id

1. user_id does not equal 3
2. post_id is unique
3. there is no other instance with that post_id and the helped_by_user_id of 3

编辑：

我已经创建了一个这样的SQLFiddle在。

I've created an SQLFiddle for this at http://sqlfiddle.com/#!10/7a324/1/0.

有关记录，我使用Ruby 1.9.3，Rails的3.2.13和PostgreSQL（Heroku的）

For the record, I'm using Ruby 1.9.3, Rails 3.2.13, and Postgresql (Heroku)

推荐答案

只需所以它更容易阅读，与信用证去@CraigRinger，为我伟大的工作最终的解决方案是100％：

Just so it's easier to read, with 100% of the credit going to @CraigRinger, the final solution that worked great for me was:

SELECT DISTINCT ON (post_id) *
FROM instances i
WHERE i.user_id <> 3
AND i.helped_by_user_id IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM instances ii
  WHERE ii.post_id = i.post_id
  AND ii.helped_by_user_id = 3
)
ORDER BY i.post_id
LIMIT 10;

这篇关于加入两个单独的查询在PostgreSQL的... ...查询（可以或不可以）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！