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

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

DECLARE @UserId INT = ##UserId:int?1149773##;
DECLARE @SiteDomain NVARCHAR(50) = '##SiteDomain?stackoverflow.com##';

SELECT q.Id
INTO #PostIds
FROM Posts q
WHERE q.PostTypeId = 1 -- Question
AND q.OwnerUserId = @UserId;
--OR EXISTS (SELECT * FROM Posts a WHERE a.ParentId = q.Id AND a.OwnerUserId = @UserId));


WITH Tags_CTE AS
(
  SELECT t.*,
    TagRender =
      '<span class="post-tag">' + REPLACE(REPLACE(REPLACE(t.TagName, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') + '</span>'
  FROM Tags t
),

Users_CTE AS
(
  SELECT u.*,
    DisplayNameEncoded = REPLACE(REPLACE(REPLACE(u.DisplayName, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'),
    UserRender =
      '<div class="user-gravatar32">' + CHAR(10) +
        '<a href="https://' + @SiteDomain + '/users/' + CAST(u.Id AS NVARCHAR) + '">' + CHAR(10) +
          CASE WHEN u.ProfileImageUrl IS NOT NULL
            THEN '<img src="' + REPLACE(REPLACE(u.ProfileImageUrl, '&', '&amp;'), '"', '&quot;') + '" width="32" height="32" />'
            ELSE '<div style="width: 32px; height: 32px; background-color: var(--black-025)"></div>'
          END + CHAR(10) +
        '</a>' + CHAR(10) +
      '</div>' + CHAR(10) +
      '<div class="user-details">' + CHAR(10) +
        '<a href="https://' + @SiteDomain + '/users/' + CAST(u.Id AS NVARCHAR) + '">' +
          REPLACE(REPLACE(REPLACE(u.DisplayName, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') +
        '</a>' + CHAR(10) +
        '<div class="-flair">' + CHAR(10) +
          '<span class="reputation-score">' + FORMAT(u.Reputation, 'N0') + '</span>' + CHAR(10) +
        '</div>' + CHAR(10) +
      '</div>'
  FROM Users u
),

Comments_CTE AS
(
  SELECT c.*,
    CommentRender = c.Text
  FROM Comments c
),

Posts_CTE AS
(
  SELECT p.*,
    TitleEncoded = REPLACE(REPLACE(REPLACE(p.Title, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'),
    Url = 'https://' + @SiteDomain + '/q/' + CAST(p.Id AS NVARCHAR),
    PostRender = 
      '<div class="post-layout">' + CHAR(10) +
        '<div class="votecell post-layout--left fc-black-200">' + CHAR(10) +
          '<svg class="svg-icon iconArrowUpLg" width="36" height="36"><path d="M2 26h32L18 10 2 26z"></path></svg>' + CHAR(10) +
          '<div class="vote-count fs-title fc-black-500" title="Score">' +
            CAST(p.Score AS NVARCHAR) +
          '</div>' + CHAR(10) +
          '<svg class="svg-icon iconArrowDownLg" width="36" height="36"><path d="M2 10h32L18 26 2 10z"></path></svg>' + CHAR(10) +
          CASE 
            WHEN p.PostTypeId = 1 -- Question
            AND p.FavoriteCount > 0 THEN
              '<svg class="svg-icon iconStar" width="18" height="18">' +
                '<path d="M9 12.65l-5.29 3.63 1.82-6.15L.44 6.22l6.42-.17L9 0l2.14 6.05 6.42.17-5.1 3.9 1.83 6.16L9 12.65z"></path>' +
              '</svg>' + CHAR(10) +
              '<div class="favorite-count" title="Favorites">' +
                CAST(p.FavoriteCount AS NVARCHAR) +
              '</div>' + CHAR(10)              
            WHEN p.PostTypeId = 2 -- Answer
            AND EXISTS (SELECT * FROM Posts q WHERE q.Id = p.ParentId AND q.AcceptedAnswerId = p.Id) THEN
              '<svg class="svg-icon iconCheckmarkLg fc-green-500" width="36" height="36">' +
                '<path d="M6 14l8 8L30 6v8L14 30l-8-8v-8z"></path>' +
              '</svg>' + CHAR(10)
            ELSE ''
          END +
        '</div>' + CHAR(10) +
        '<div class="postcell post-layout--right">' + CHAR(10) +
          '<div class="post-text">' + CHAR(10) +
            p.Body +
          '</div>' + CHAR(10) +
          '<div class="post-taglist">' + CHAR(10) +
            ISNULL((
              SELECT STRING_AGG(t.TagRender, CHAR(10)) WITHIN GROUP (ORDER BY t.Count DESC)
              FROM Tags_CTE t
              JOIN PostTags pt ON pt.TagId = t.Id
              WHERE pt.PostId = p.Id
            ) + CHAR(10), '') +
          '</div>' + CHAR(10) +
          '<div class="post-signatures" style="float: right">' + CHAR(10) +
            '<div class="post-signature user-info">' + CHAR(10) +
              '<div class="user-action-time">' + CHAR(10) +
                'posted <time>' + CONVERT(NVARCHAR(MAX), p.CreationDate, 120) + '</time>' + CHAR(10) +
              '</div>' + CHAR(10) +
              u.UserRender + CHAR(10) +
            '</div>' + CHAR(10) +
          '</div>' + CHAR(10) +
        '</div>' + CHAR(10) +
        '<div class="post-layout--right">' + CHAR(10) +
          '<div class="comments">' + CHAR(10) +
            '<ul class="comments-list">' + CHAR(10) +
              ISNULL((
                SELECT STRING_AGG(c.CommentRender, CHAR(10)) WITHIN GROUP (ORDER BY c.CreationDate)
                FROM Comments_CTE c
                WHERE c.PostId = p.Id
              ) + CHAR(10), '') +
            '</ul>' + CHAR(10) +
          '</div>' + CHAR(10) +
        '</div>' + CHAR(10) +
      '</div>'
  FROM Posts p
  JOIN Users_CTE u ON u.Id = p.OwnerUserId
),

Answers_CTE AS
(
  SELECT p.*,
    AnswerRender = 
      '<div class="answer" style="border-bottom: 1px solid var(--black-075)">' + CHAR(10) +
        p.PostRender + CHAR(10) +
      '</div>'
  FROM Posts_CTE p
  WHERE p.PostTypeId = 2 -- Answer
),

Questions_CTE AS
(
  SELECT p.*,
    QuestionRender =
      '<div class="question-and-answers">' + CHAR(10) +
        '<h1 class="question-header" style="margin-bottom: 8px">' + CHAR(10) +
          '<a class="question-hyperlink" href="' + p.Url + '">' + CHAR(10) +
            p.TitleEncoded + CHAR(10) +
          '</a>' + CHAR(10) +
        '</h1>' + CHAR(10) +
        '<div style="padding-bottom: 8px; margin-bottom: 16px; border-bottom: 1px solid var(--black-075)">' + CHAR(10) +
          '<span style="color: var(--fc-light); margin: 0 2px 0 0">Asked</span>' + CHAR(10) +
          '<time>' + CONVERT(NVARCHAR(MAX), p.CreationDate, 120) + '</time>' + CHAR(10) +
          '<span style="color: var(--fc-light); margin: 0 2px 0 16px">Active</span>' + CHAR(10) +
          '<time>' + CONVERT(NVARCHAR(MAX), p.LastActivityDate, 120) + '</time>' + CHAR(10) +
          '<span style="color: var(--fc-light); margin: 0 2px 0 16px">Viewed</span>' + CHAR(10) +
          '<span>' + FORMAT(p.ViewCount, 'N0') + ' times</span>' + CHAR(10) +
        '</div>' + CHAR(10) +
        '<div class="question">' + CHAR(10) +
          p.PostRender + CHAR(10) +
        '</div>' + CHAR(10) +
        '<div class="answers" style="margin: 12px 0; border-bottom: 2px solid var(--bronze-darker)">' + CHAR(10) +
          '<div class="answers-header">' + CHAR(10) +
            '<h2>' + FORMAT(p.AnswerCount, 'N0') + ' Answer(s)</h2>' + CHAR(10) +
          '</div>' + CHAR(10) +
          ISNULL((
            SELECT STRING_AGG(a.AnswerRender, CHAR(10)) WITHIN GROUP (ORDER BY a.Score DESC)
            FROM Answers_CTE a
            WHERE a.ParentId = p.Id
          ) + CHAR(10), '') +
        '</div>' + CHAR(10) +
      '</div>'
  FROM Posts_CTE p
  WHERE p.PostTypeId = 1 -- Question
)

SELECT
'<html lang="en">
<head>
  <meta charset="utf-8"/>
  <title>Questions &amp; Answers</title>
  <script src="https://code.jquery.com/jquery-3.5.0.min.js"></script>
  <script async src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js"></script>
  <script>
    window.addEventListener("load", (event) => {
      $("pre:has(code)").addClass("prettyprint");
    });
  </script>
  <link rel="stylesheet" type="text/css" href="https://cdn.sstatic.net/Shared/stacks.css?v=25a7b20b759e"></link>
  <link rel="stylesheet" type="text/css" href="https://cdn.sstatic.net/Sites/stackoverflow/primary.css?v=76bc3e3ed3a0"></link>
  <style>
    body {
      width: 1000px;
      min-width: 1000px;
      margin: 0 auto;
    }
    .question-header .question-hyperlink {
      color: var(--black-700);
      font-size: 27px;
      text-decoration: none;
      margin-bottom: 0;
    }
    .post-text pre.prettyprint {
      padding: 12px 8px;
      border: 0;
    }
    .votecell {
      text-align: center;
    }
    .votecell .svg-icon {
      display: block;
      margin: auto;
    }
  </style>
</head>
<body>' + CHAR(10) +
ISNULL((
  SELECT STRING_AGG(q.QuestionRender, CHAR(10)) WITHIN GROUP (ORDER BY q.CreationDate ASC)
  FROM Questions_CTE q
  --WHERE q.Id IN (SELECT p.Id FROM #PostIds p)
  WHERE q.Id = 17663484
) + CHAR(10), '') +
'</body>
</html>'

Enter Parameters

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