对于Rails3应用程序，如何将这两个PostgreSQL语句组合成一个语句？

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 and 4，第二个SQL语句将返回link_ids 1, 2 and 3。

如何将这两条SQL语句合并为一条语句，以便获得包含多个选定标记的特定标记的最高分？

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

可以在以下位置找到DDL和插入命令：http://sqlize.com/480glD5Is4

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

非常感谢。

Answer 1

第一条SQL语句返回一个链接，该链接包含两个特定的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号具有

• 都是标签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

这很简单。

我们可以使用一个公用表表达式轻松地连接这两个查询。

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;

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

我很确定这仍然不是你想要的，但我不明白你最后的评论。