我有以下查詢回傳兩列，我想對兩列求和并通過對兩列求和來創建第三列。
有什么辦法可以通過洗掉子查詢來重新創建以下查詢？我可以通過加入實作相同的任何方式嗎？
SELECT
IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
(SELECT COUNT(c1.content)
FROM comments c1
WHERE c1.parentcommentid = c2.id
AND c1.isdeleted = 0
AND c1.approved = 1) ChildContentcount --Anyway to remove the subquery
FROM
comments c2
WHERE
c2.discussionid = '257943'
AND c2.parentcommentid IS NULL
ORDER BY
c2.pinned DESC,
c2.createddate
樣本資料：
---------- --------------
| content | childcontent |
---------- --------------
| 1 | 8 |
| 0 | 0 |
| 1 | 3 |
---------- --------------
預期輸出：
---------- ---------------- ---------
| content | childcontent | sumdata |
---------- ---------------- ---------
| 1 | 8 | 9 |
| 0 | 0 | 0 |
| 1 | 3 | 4 |
| 1 | 8 | 9 |
---------- ---------------- ---------
uj5u.com熱心網友回復：
您可以使用CROSS APPLY或OUTER APPLY代替相關子查詢。
然后您可以重新使用這些值。
select c.pinned, c.createddate
, c.discussionid
, ca1.content
, ca2.childcontent
, (ca1.content ca2.childcontent) AS sumdata
FROM comments c
CROSS APPLY
(
SELECT CASE
WHEN c.isdeleted = 1 OR c.approved = 0 THEN 0
ELSE 1
END AS content
) ca1
CROSS APPLY
(
SELECT COUNT(c2.content) AS childcontent
FROM comments c2
WHERE c2.parentcommentid = c.id
AND c2.isdeleted = 0
AND c2.approved = 1
) ca2
WHERE c.discussionid = '257943'
AND c.parentcommentid IS NULL
ORDER BY
c.pinned DESC,
c.createddate;
uj5u.com熱心網友回復：
子查詢并對列求和：
select tbl.* , Contentcount ChildContentcount third_sum from
(
select IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
(SELECT COUNT(c1.content)
FROM comments c1
WHERE c1.parentcommentid = c2.id
AND c1.isdeleted = 0
AND c1.approved = 1) ChildContentcount
FROM
comments c2
WHERE
c2.discussionid = '257943'
AND c2.parentcommentid IS NULL ) tbl
如果您提供sql fiddle，我們可以嘗試以其他方式創建它
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361951.html
標籤：sql-server 查询语句
上一篇：從XML列讀取屬性值