プログラマーとプログラミングに熱心な人々のための質問と回答
WITH PostUpvotes AS ( SELECT PostId, COUNT(*) AS PostUpvoteCount FROM Votes WHERE VoteTypeId = 2 GROUP BY PostId ), CommentUpvotes AS ( SELECT PostId AS CommentOnPostId, COUNT(*) AS CommentUpvoteCount FROM Votes WHERE VoteTypeId = 2 AND PostId IN (SELECT Id FROM Comments) GROUP BY PostId ) SELECT p.Id AS PostId, p.Body AS PostContent, -- Added line for post content p.OwnerUserId AS PostUserId, p.CreationDate AS PostCreationDate, c.Text AS CommentContent, -- Added line for comment content c.UserId AS CommentingUserId, c.CreationDate AS CommentCreationDate, p.Tags AS Tags, up.Reputation AS PostUserReputation, up.UpVotes AS PostUserUpVotes, up.DownVotes AS PostUserDownVotes, up.Views AS PostUserViews, uc.Reputation AS CommentingUserReputation, uc.UpVotes AS CommentingUserUpVotes, uc.DownVotes AS CommentingUserDownVotes, uc.Views AS CommentingUserViews, pu.PostUpvoteCount, cu.CommentUpvoteCount FROM Posts p LEFT JOIN Comments c ON c.PostId = p.Id LEFT JOIN Users up ON up.Id = p.OwnerUserId LEFT JOIN Users uc ON uc.Id = c.UserId LEFT JOIN PostUpvotes pu ON pu.PostId = p.Id LEFT JOIN CommentUpvotes cu ON cu.CommentOnPostId = c.PostId -- Fixed to reference c.PostId for accurate join WHERE p.CreationDate >= '2022-07-05' AND p.CreationDate <= '2022-07-20' AND ( p.Tags LIKE '%<python>%' OR p.Tags LIKE '%<machine-learning>%' OR p.Tags LIKE '%<data-science>%' OR p.Tags LIKE '%<scikit-learn>%' OR p.Tags LIKE '%<tensorflow>%' OR p.Tags LIKE '%<keras>%' OR p.Tags LIKE '%<pytorch>%' OR p.Tags LIKE '%<javascript>%' OR p.Tags LIKE '%<html>%' OR p.Tags LIKE '%<css>%' OR p.Tags LIKE '%<reactjs>%' OR p.Tags LIKE '%<angularjs>%' OR p.Tags LIKE '%<vue.js>%' OR p.Tags LIKE '%<django>%' OR p.Tags LIKE '%<flask>%' OR p.Tags LIKE '%<sql>%' OR p.Tags LIKE '%<mysql>%' OR p.Tags LIKE '%<postgresql>%' OR p.Tags LIKE '%<oracle>%' OR p.Tags LIKE '%<mongodb>%' OR p.Tags LIKE '%<redis>%' OR p.Tags LIKE '%<linux>%' OR p.Tags LIKE '%<windows>%' OR p.Tags LIKE '%<macos>%' OR p.Tags LIKE '%<bash>%' OR p.Tags LIKE '%<powershell>%' OR p.Tags LIKE '%<c>%' OR p.Tags LIKE '%<c++>%' OR p.Tags LIKE '%<haskell>%' OR p.Tags LIKE '%<scala>%' OR p.Tags LIKE '%<clojure>%' OR p.Tags LIKE '%<elixir>%' OR p.Tags LIKE '%<f#>%' OR p.Tags LIKE '%<android>%' OR p.Tags LIKE '%<ios>%' OR p.Tags LIKE '%<flutter>%' OR p.Tags LIKE '%<react-native>%' )