如何修復SQL錯誤“當EXISTS未引入子查詢時，選擇串列中只能指定一個運算式。”-有解無憂

我一直在用頭撞墻，試圖讓 SELECT 中的最少子查詢起作用，只是想不出一種方法讓它只為最后一個提供任何型別的輸出。沒有它，代碼就可以作業。

SELECT U.Username, COUNT(DISTINCT P.PostID) AS 'Total Posts',
           CONVERT(CHAR(10), ISNULL((SELECT SUM(R.Upvote) FROM Ratings R WHERE U.UserID = R.FK_UserID), 0)) AS 'Karma',
           CONVERT(CHAR(10), ISNULL((SELECT U.UserID, SUM(R.Upvote) FROM Users JOIN Posts ON Posts.FK_UserID = UserID
               JOIN Ratings ON Ratings.FK_PostID = PostID GROUP BY UserID), 0)) AS 'Total Karma Received'
    FROM Users U
    FULL OUTER JOIN Ratings R on U.UserID = R.FK_UserID
    FULL OUTER JOIN Posts P on U.UserID = P.FK_UserID
    GROUP BY U.Username, U.UserID
    ORDER BY 'Total Posts' DESC

uj5u.com熱心網友回復：

'Total Karma Received' 的子查詢運算式應該只回傳一個值，但您的子查詢：SELECT U.UserId, SUM(R.UpVote)...GROUP BY UserId準備回傳至少兩個值（UserId 和 Sum），但也有多行。它應該回傳什么：'Total UpVotes for all Users'，或者'Total UpVotes for this User'，或者......？

uj5u.com熱心網友回復：

如果您只想要該用戶的贊成票，您可以移動userID到內部查詢中的WHERE ，或者如果您想要所有用戶的所有贊成票的總和，則可以完全洗掉它。

SELECT 
  U.Username, 
  COUNT(DISTINCT P.PostID) AS 'Total Posts',
  CONVERT(CHAR(10), 
          ISNULL(
             (SELECT SUM(R.Upvote) 
              FROM Ratings R 
              WHERE U.UserID = R.FK_UserID),
          0)) AS 'Karma',
  CONVERT(CHAR(10), 
          ISNULL(
              (SELECT  SUM(R.Upvote) 
               FROM Users 
               JOIN Posts ON Posts.FK_UserID = UserID
               JOIN Ratings ON Ratings.FK_PostID = PostID 
               WHERE UserID = U.UserID 
               GROUP BY UserID), 
           0)) AS 'Total Karma Received'
FROM Users U
FULL OUTER JOIN Ratings R on U.UserID = R.FK_UserID
FULL OUTER JOIN Posts P on U.UserID = P.FK_UserID
GROUP BY U.Username, U.UserID
ORDER BY 'Total Posts' DESC;

db<>在這里擺弄

uj5u.com熱心網友回復：

加入表比每行運行一個子查詢更簡單、更有效。

SELECT 
  U.userID,
  U.Username, 
  COUNT(DISTINCT P.PostID) AS 'Total Posts',
  SUM(given.upvote) AS 'Total Karma Given',
  SUM(recd.upvote) AS 'Total Karma Received' 
FROM Users U
LEFT JOIN posts P
ON U.userID = P.FK_UserID
LEFT JOIN ratings recd
ON P.postID= recd.FK_PostID
LEFT JOIN ratings given
ON U.userID = given.FK_UserID
GROUP BY U.userID,U.username
ORDER BY U.userID,U.username
GO

用戶名 | 用戶名 | 帖子總數 | 給予的總業力 | 收到的總業力
-----: | :--------- | ----------: | ----------------: | ------------------:
     1 | 用戶一 | 2 |              空| 1
     2 | 用戶二 | 1 |              空|                 空
     3 | 用戶三 | 0 |              空|                 空
     4 | 用戶四 | 0 | 1 |                 空值

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/471027.html

標籤：sql 存在

上一篇：ASP.NETCoreCSS隔離與Webpack

下一篇：sql選擇數量最少的訂單