无法使用 RANK() 函数编写 sql 查询

Question

在我的应用程序中，我需要使用以下规则从数据库中检索最佳文件：

• 文件，具有最多的upvotes -Priority 1
• 文件，最多的注释 - 优先级2

如果只有没有投票和注释的文件，则只拿起随机的文件。

我的桌子：

  CREATE TABLE "FILES" 
   (    "ID" NUMBER, 
    "OBJ_ID" NUMBER, 
    "NAME" VARCHAR2(30 BYTE)
   ) ;
  CREATE TABLE "UPVOTES" 
   (    "ID" NUMBER, 
    "TO_ID" NUMBER, 
    "TO_TYPE" NUMBER
   )  ;
   COMMENT ON COLUMN "UPVOTES"."TO_TYPE" IS '0 obj, 1 file, 2 comment';
  CREATE TABLE "COMMENTS" 
   (    "ID" NUMBER, 
    "OBJ_ID" NUMBER, 
    "CONTENT" VARCHAR2(20 BYTE), 
    "TO_TYPE" NUMBER, 
    "TO_ID" NUMBER
   ) ;
   COMMENT ON COLUMN "COMMENTS"."TO_TYPE" IS '0 object, 1 file';
Insert into FILES (ID,OBJ_ID,NAME) values ('1','1','best file for obj id = 1');
Insert into FILES (ID,OBJ_ID,NAME) values ('2','1','file obj1');
Insert into FILES (ID,OBJ_ID,NAME) values ('3','1','file obj1');
Insert into FILES (ID,OBJ_ID,NAME) values ('4','2','best file for obj id = 2');
Insert into FILES (ID,OBJ_ID,NAME) values ('5','2','file obj2');
Insert into FILES (ID,OBJ_ID,NAME) values ('6','3','only one file obj 3');
Insert into FILES (ID,OBJ_ID,NAME) values ('7','4','probilem file obj 4');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('1','1','1');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('2','1','1');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('3','7','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('4','2','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('5','2','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('6','2','0');
Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('1','1','comment 1','1','2');
Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('2','1','comment 2','1','2');
Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('3','2','comment 3','1','4');

我的SQL查询：

SELECT obj_id, name FROM (
SELECT obj_id, name, rank, ROW_NUMBER() OVER (PARTITION BY obj_id ORDER BY rank) rownumb FROM (
SELECT f.obj_id, f.name, RANK() OVER (PARTITION BY f.obj_id ORDER BY COUNT(v.id) DESC, COUNT(DISTINCT com.id) DESC) rank
FROM files f
LEFT OUTER JOIN upvotes v
ON f.id = v.to_id
LEFT OUTER JOIN comments com
ON f.id = com.to_id
WHERE (v.to_type = 1 OR v.to_type IS NULL)
AND (com.to_type = 1 OR com.to_type IS NULL)
GROUP BY f.obj_id, f.name
)
)
WHERE rownumb = 1;

预期结果：

obj_id    file name
1         best file for obj id = 1
2         best file for obj id = 2
3         only one file obj 3
4         probilem file obj 4

问题是：

(v.to_type = 1 OR v.to_type IS NULL)

它失败了，因为具有与文件ID相同的对象（to_type = 0）的upvotes（to_type = 0），但我仍然需要计算文件的upvotes（to_type = 1）。

。

有人可以帮我弄清楚吗？

我使用Oracle数据库11G XE R2。

Answer 1

替换

FROM files f
LEFT OUTER JOIN upvotes v
ON f.id = v.to_id
LEFT OUTER JOIN comments com
ON f.id = com.to_id
WHERE (v.to_type = 1 OR v.to_type IS NULL)
AND (com.to_type = 1 OR com.to_type IS NULL)

FROM files f
    LEFT OUTER JOIN upvotes v ON f.id = v.to_id AND v.to_type = 1
    LEFT OUTER JOIN comments com ON f.id = com.to_id AND com.to_type = 1