针对主详细查询结果的SQL Server条件查询

Question

我有这个问题。让我们看看查询：

SELECT 
    D.DocumentName, 
    D.Title, 
    D.Description, 
    V.Version, 
    CASE 
     WHEN V.State = 'PUB' THEN 'PUBLISHED' 
     WHEN V.State = 'UNP' THEN 'UNPUBLISHED' 
     WHEN V.State IS NULL THEN 'NOT PUBLISHED' 
     ELSE '' 
    END AS State 
FROM 
    Document D 
LEFT JOIN 
    DocumentVersion V ON D.IdDocument = V.IdDocumentVersion 

这是一个典型的主 - 细节表（Document-DocumentVersion）。 A Document可以有一个或多个DocumentVersions或没有。 A DocumentVersion状态可以是PUBLISHED或UNPUBLISHED。 PUBLISHED一个DocumentVersion只能存在一次Document（这是由应用程序控制），但可以存在很多UNPUBLISHED，或者所有DocumentVersions都是UNPUBLISHED。 NOT PUBLISHED不存在，当Document没有DocumentVersion记录时，这只是查询结果中的一个单词，这就是LEFT JOIN的原因。

所以我的问题是：我如何使查询完成下一个规则？

• 如果DocumentVersion状态PUB，忽略了别人，我的意思是不显示UNP那些在查询结果用于本Document

• 如果DocumentVersion状态UNP（这里可许多UNP），那么只显示最高日期为Unpublish（DocumentVersion表格有UnPublishDate列），此规则适用于没有PUBDocumentVersion的文档

我将不胜感激任何帮助，你可以给我，谢谢你们。

Answer 1

这是一个优先级查询。您可以使用row_number()或rank()做到这一点：

select d.*, dv.* 
from d left join 
    (select dv.*, 
      row_number() over (partition by dv.IdDocument 
           order by (case when dv.state = 'PUB' then 1 
               when dv.state = 'UNP' then 2 
               else 3 
              end) 
           ) as seqnum 
     from documentversion dv 
    ) dv 
    on dv.IdDocument = d.IdDocument and 
     seqnum = 1; 

注：我不认为加盟条件是你的问题是正确的。

Answer 2

select * 
from Document D 
left outer join 
    (select *, 
    row_number()over(partition by IdDocumentVersion order by state asc, UnPublishDate desc) as row 
    from DocumentVersion) V 
on D.IdDocument = V.IdDocumentVersion 
and V.row = 1 

State asc在刚才碰巧在这种情况下工作的方式有些破绽。你可能想更明确一个'什么时候'（见戈登的答案）。