将两个sql查询合并为一个查询，从SEDE中获取最热门的问题和答案

Question

我有一个查询，通过以下方式从SEDE获取最受欢迎的问题

SELECT TOP 10
    'https://stackoverflow.com/questions/' + CONVERT(VARCHAR, Posts.Id) as url,
    (ViewCount / (SELECT - DATEDIFF(DAY, GETDATE(), CreationDate))) AS ave_views_per_day,
    Body as QUESTION,
    Posts.Id,
    Title
FROM
    Posts
LEFT JOIN 
    PostTags ON Posts.Id = PostTags.PostId
LEFT JOIN 
    Tags ON PostTags.TagId = Tags.Id
WHERE 
    TagName IN ('##tag##')
    AND ViewCount > 10000
ORDER BY 
    ave_views_per_day DESC

我有一个额外的查询，可以在给定问题id的情况下获得得分最高的答案：

SELECT 
    'https://stackoverflow.com/questions/' + CONVERT(VARCHAR, Id) as url, HighestScoringAnswer
FROM
    (SELECT TOP 1 
         Id, Body AS HighestScoringAnswer, 
         DENSE_RANK() OVER (ORDER BY Score DESC) Rank 
     FROM 
         (SELECT p.Id, p.ParentId, p.Body, p.Score
          FROM Posts p 
          WHERE p.ParentId = ##QuestionId##) x
     ORDER BY 
         Rank ASC) x

如何组合这些查询，使第二个查询的##QuestionId##源自第一个查询的Posts.Id，并将生成的HighestScoringAnswer列添加到第一个查询的结果中？我尝试使用Union操作，但不知道如何将Id从一个查询“传递”到另一个查询。

Answer 1

您必须在两个查询(问题和答案)之间创建JOIN；而不是UNION ( UNION返回第一个查询的行和第二个查询的行，不包括重复的行；但是您希望同一行中的每个问题都有前十个问题和更多得分的答案)。

尝试此查询，我认为这就是您要查找的查询：

SELECT DISTINCT TOP 10
    'https://stackoverflow.com/questions/' + CONVERT(VARCHAR, q.Id) as url,
    (q.ViewCount / -DATEDIFF(DAY, GETDATE(), q.CreationDate)) AS ave_views_per_day,
    q.Body as QUESTION,
    q.Id,
    q.Title,
    FIRST_VALUE(a.Body) OVER (PARTITION BY q.id ORDER BY a.Score DESC) AS HighestScoringAnswer
    
FROM Posts q
LEFT JOIN Posts a ON q.id = a.ParentId

WHERE q.PostTypeId = 1 
      AND q.Tags LIKE '%postgresql%'
      AND q.ViewCount > 10000
      
ORDER BY ave_views_per_day DESC;