tSQL 语法 for 'If A does not exist in Query Return B, but if A does exist only return A'

Question

我有一个查询，可以从Gltable返回当前和未来的收入。我需要返回两种发布类型。如果不存在postingType a，则返回postingType b，如果存在postingtype a仅返回postingtype A. db是mssql 2014。

我尝试了下面的几种变体

WHERE (EXISTS (SELECT 1 FROM GLAccountPosting WHERE PostingType = 10) 
        )
        OR
        NOT EXISTS (SELECT 1 FROM GLAccountPosting WHERE PostingType = 14) 
        (;
WHERE 
postingtype in (14, 10)
AND
    (StartDateTime > '2019/07/01' AND postingtype = 10)
    OR
     (StartDateTime < '2019/07/01' AND postingtype = 14);
WHERE
    (StartDateTime > '2019/07/01' AND postingtype = 10 ) OR
    (StartDateTime < '2019/07/01' OR postingtype = 14);

在下面查询(由于非常大(：

SELECT 
prepay.Prepay,
Case When GAP.postingtype = 14 then isnull(sum(GAP.CreditValue),0) ELSE 0 end as CreditTodate,
Case When GAP.postingtype = 14 then prepay.Prepay -  isnull(sum(GAP.CreditValue),0) ELSE prepay.Prepay   end AS Balance,
GAP.PostingType

 FROM
GLAccountPosting AS GAP
    JOIN (SELECT 
                        SalesTransactions.BranchID,
                        Departments.DepartmentGUID,
                        SalesTransactions.SalesTransactionGUID,
                        SalesTransactionDetails.SalesTransactionLineGUID,
                        Sum(SalesTransactionDetails.Prepayvalue) AS Prepay

                 FROM   
                      intellimanager.dbo.SalesTransactionDetails
                      INNER JOIN  intellimanager.dbo.SalesTransactions ON SalesTransactionDetails.SalesTransactionGUID=SalesTransactions.SalesTransactionGUID
                      INNER JOIN  intellimanager.dbo.SalesTransactionLines ON SalesTransactionDetails.SalesTransactionLineGUID=SalesTransactionLines.SalesTransactionLineGUID
                      INNER JOIN  intellimanager.dbo.Departments on Departments.DepartmentGUID = SalesTransactionDetails.ItemDepartmentGUID
                      left  JOIN  intellimanager.dbo.BookingLinesDetails on BookingLinesDetails.BookinglinesDetailGUID = SalesTransactionDetails.BookinglinesDetailGUID
                      LEFT JOIN  intellimanager.dbo.BookingLines on BookingLines.BookingLineGUID = BookingLinesDetails.BookingLineGUID
                 WHERE  
                      (
                            SalesTransactionDetails.AccrualStatus = 1 OR SalesTransactionDetails.AccrualStatus=2 -- 1 = reversed, 2 = deferred
                      ) AND
                        SalesTransactions.PostingDateTime < DateAdd(day,1,'2019/04/27') and
                        SalesTransactionDetails.BranchID IN (SELECT number FROM dbo.InzSplitInt(1)) AND-- selected branch 
                        SalesTransactionDetails.AccrualType = 2 AND  --- sessions only
                        SalesTransactionDetails.PrepayValue <> 0 AND
                        isnull(SalesTransactionLines.CreditSalesTransactionLineGUID,0x0) = 0x0  and -- remove credited lines      
                        SalesTransactions.status = 1 and -- remove not finalised and cancelled invoices
                        BookingLinesDetails.ItemType = 1 and  
                      (BookingLinesDetails.BookingDetailProgress not in (2,4) or 
                        (
                            BookingLinesDetails.BookingDetailProgress in (2,4) and SalesTransactionDetails.AccrualStatus <> 1)
                        ) 
                        and
                        (   
                            BookingLinesDetails.CalendarLinkGUID is null or 
                            (BookingLinesDetails.CalendarLinkGUID is not null and BookingLinesDetails.StartDateTime >= '2019/01/01'
                            )
                        ) 
                         -- include all sales tran details that are for bookings in the future
                         -- whether they have been completed or not and whether they have 
                        GROUP BY 
                            SalesTransactions.BranchID,
                            SalesTransactions.SalesTransactionGUID,
                            SalesTransactionDetails.SalesTransactionLineGUID,
                            Departments.DepartmentGUID,
                            BookingLinesDetails.StartDateTime) As prepay on prepay.SalesTransactionLineGUID = GAP.SalesTransactionLineGUID
                        JOIN salestransactionlines stl on stl.salestransactionlineguid = gap.salestransactionlineguid
                        LEFT JOIN salestransactiondetails sd on sd.SalesTransactiondetailGUID = GAP.SalesTransactiondetailGUID

where 
        GAP.ItemType = 1

group by

GAP.GLPostingDescription,
GAP.postingtype,
GAP.FormattedGLAccountCode,
prepay.Prepay,
GAP.PostingType

我需要的结果是：

Prepaid   Current Future  postingtype
26.90     0.00    26.90   10
215.20  215.20     0.00   14

每行可能同时具有帖子型10和14，但我只想一次看到一个。如果两者都出现相同的交易，则总计不准确。

Answer 1

这是您的意思吗？

DECLARE @t TABLE (TransactionID INT)
DECLARE @p TABLE (PostingType CHAR(1), TransactionID INT)
INSERT INTO @t
(TransactionID)
VALUES
(1),
(2),
(3)
INSERT INTO @p
(PostingType, TransactionID)
VALUES
('A', 1),
('B', 1),
('A', 2),
('B', 3)
SELECT t.TransactionID FROM @t t
WHERE
	EXISTS
		(
			SELECT 1 FROM @p p
			PIVOT  
			(  
			COUNT(p.PostingType)  
			FOR p.PostingType IN ([A], [B])  
			) sq
			WHERE
				(
					([A] = 0 AND [B] > 0) --Postingtype A does not exist then return postingtype B
					OR ([A] > 0 AND [B] = 0) --If postingtype A exists only return postingtype A
				)
				AND sq.TransactionID = t.TransactionID
		)