sql - 如何将这两个 PostgreSQL 语句合并为一个用于 Rails 3 应用程序？

Question

我想在我的 RoR 应用程序中合并两个 PostgreSQL 语句。

第一个 SQL 语句返回一个链接，该链接包含两个特定的 tag_ids。

SELECT link_id, count(*) as counter
  FROM "totals"
 WHERE "totals"."tag_id" IN (6, 8)
   AND (score > 0)
 GROUP BY link_id
HAVING count(*)=2

RoR ActiveRecord 版本:

 links = Total.find_all_by_tag_id(@tag_list, :conditions => ["score > 0"], :select => "link_id, count(*) as counter", :having => "count(*)=#{@tag_list.count}", :group => "link_id").collect(&:link_id).uniq.sort.reverse

第二个 SQL 语句返回具有特定 tag_id 最高分的链接。

SELECT s1.link_id
  FROM totals AS s1
     , (SELECT link_id
              , MAX(score) AS maxscore
          FROM totals
         GROUP BY link_id) as s2
 WHERE s2.link_id = s1.link_id
   and s1.score = s2.maxscore
   AND s1.score > 0 AND s1.tag_id = 6

表格是这样构造的:

totals:
  link_id : integer
  tag_id : integer
  score : integer

=============================
| link_id  | tag_id | score |
=============================
|    1     |    6   |   5   |
|    1     |    8   |   2   |
|    1     |    3   |   1   |
|    2     |    6   |   6   |
|    2     |    4   |   2   |
|    2     |    8   |   6   |
|    3     |    6   |   5   |
|    3     |    2   |   4   |
|    4     |    2   |   4   |
|    4     |    6   |   1   |
|    4     |    8   |   2   |
=============================

第一个 SQL 语句将返回 link_ids 1、2 和 4，第二个 SQL 语句将返回 link_ids 1， 2 和 3。

如何将两条SQL语句合二为一，从而得到包含多个选中标签的特定标签的最高分？

组合语句应返回 link_ids 1 和 2。

可以在此处找到 DDL 和 INSERT 命令:http://sqlize.com/480glD5Is4

如果这可以用 RoR ActiveRecord 风格或更优化的 SQL 语句编写，那就太好了。

非常感谢。

Answer 1

The first SQL statement returns a link where the link contains two specific tag_ids.

当且仅当 {link_id, tag_id} 上存在主键约束或唯一约束时，该方法才有效。我添加了该约束(有意义)并且我将包括其他人的 CREATE TABLE 和 INSERT 语句。 (你应该这样做。如果你愿意，你可以编辑你的问题并粘贴这些东西。)

create table totals (
  link_id  integer not null,
  tag_id integer not null,
  score integer not null,
  primary key (link_id, tag_id)
);

insert into totals values
(1, 6, 5   ),
(1, 8, 2   ),
(1, 3, 1   ),
(2, 6, 6   ),
(2, 4, 2   ),
(3, 6, 1   ),
(3, 2, 4   ),
(3, 8, 3   ),
(4, 2, 4   ),
(4, 6, 1   ),
(4, 8, 2   );

根据评论改写问题，您正在寻找具有

• 标签 ID 号 6 和 8，以及
• 标签 id 6 的分数高于标签 id 8 的分数

首先，很容易看出这两个查询将为您提供

• tag_id = 6 的所有行，以及

• tag_id = 8 的所有行

  select *
  from totals
  where tag_id = 6
  
  select *
  from totals
  where tag_id = 8
  

这很简单。

我们可以使用公共(public)表表达式轻松连接两个查询。

with score_for_8 as (
  select *
  from totals
  where tag_id = 8
) 
select totals.* 
from totals 
inner join score_for_8
        on score_for_8.link_id = totals.link_id and
           totals.score > score_for_8.score 
where totals.tag_id = 6;

由于这不需要对结果集进行分组、排序或限制，因此它将正确地报告关系。

我很确定这仍然不是您要找的东西，但我不明白您最后的评论。