All questions and answers by user, rendered as HTML

0

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフローMeta

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

WITH Posts_CTE AS
(
  SELECT p.*,
    TitleEncoded = REPLACE(REPLACE(REPLACE(p.Title, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'),
    Url = 'https://##SiteDomain?stackoverflow.com##/q/' + CAST(p.Id AS NVARCHAR)
  FROM Posts p
),

Answers_CTE AS
(
  SELECT p.*,
    Render = 
      '<div class="answer">' + CHAR(10) +
        p.Body +
      '</div>' + CHAR(10)
  FROM Posts_CTE p
  WHERE p.PostTypeId = 2 -- Answer
),

Questions_CTE AS
(
  SELECT p.*,
    Render =
      '<div class="question-and-answers">' + CHAR(10) +
        '<h1 class="question-header">' + CHAR(10) +
          '<a class="question-hyperlink" href="' + p.Url + '">' + CHAR(10) +
            p.TitleEncoded + CHAR(10) +
          '</a>' + CHAR(10) +
        '</h1>' + CHAR(10) +
        '<div class="question">' + CHAR(10) +
          p.Body +
        '</div>' + CHAR(10) +
        (
          SELECT STRING_AGG(a.Render, '') WITHIN GROUP (ORDER BY a.Score DESC)
          FROM Answers_CTE a 
          WHERE a.ParentId = p.Id
        ) +
      '</div>' + CHAR(10)
  FROM Posts_CTE p
  WHERE p.PostTypeId = 1 -- Question
)

SELECT 
  '<html>' + CHAR(10) + 
  '<head>' + CHAR(10) + 
  '<style>' + CHAR(10) + 
  '.question-and-answers { border: 1px solid black; padding: 4px; }' + CHAR(10) +
  '.question { border: 1px solid purple; padding: 4px;}' + CHAR(10) + 
  '.answer { border: 1px solid green; padding: 4px; }' + CHAR(10) + 
  '</style>' + CHAR(10) +
  '</head>' + CHAR(10) + 
  '<body>' + CHAR(10) + 
  STRING_AGG(q.Render, '') WITHIN GROUP (ORDER BY q.CreationDate ASC) +
  '</body>' + CHAR(10) + 
  '</html>'
FROM Questions_CTE q
WHERE q.OwnerUserId = ##UserId:int?1149773##

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results