子查询具有不同条件的同一列

我正在尝试编写一个子查询以从我已经在主查询中选择的列之一中检索值，但条件不同。

enter image description here

您可以尝试一下吗，我认为您可以使用子查询查询费用和信用额

SELECT DISTINCT
[date],[customer],Charge = (SELECT Revenue FROM TABLE t2 WHERE t2.customer = t1.customer AND t2.[type] = 'Charge'),Credit = (SELECT Revenue FROM TABLE t2 WHERE t2.customer = t1.customer AND t2.[type] = 'Credit')
FROM TABLE t1


带有示例数据：

CREATE TABLE #temp (thedate date,customer VARCHAR(100),revenue INT,[type] VARCHAR(100))
INSERT INTO #temp values ('2019-01-01','a',10,'charge'),('2019-01-01',20,'Credit'),'b',30,'charge')
SELECT DISTINCT
[thedate],Charge = (SELECT Revenue FROM #temp t2 WHERE t2.customer = t1.customer AND t2.[type] = 'Charge'),Credit = (SELECT Revenue FROM #temp t2 WHERE t2.customer = t1.customer AND t2.[type] = 'Credit')
FROM #temp t1

,

我相信这是您想要的：

CREATE TABLE #temp (thedate date,[type] VARCHAR(100))
INSERT INTO #temp values ('2019-01-01','Acme,Inc.',1000,'Charge'),'Amazon',500,100,'Credit')
SELECT [Date],[Customer],[Revenue] as [Charge],[Credit]
FROM #temp t1
OUTER APPLY (
SELECT sum(revenue) credit
FROM #temp t2
WHERE t1.customer = t2.customer
AND t2.[type] = 'credit'
) x
WHERE t1.[type] = 'charge'


仅根据您的输入数据和预期结果进行判断，上述查询就足够了。但是，您在问题上发表的评论与预期结果的图像有些矛盾。

这是我查询的输出，基于您的输入数据：

CREATE TABLE #temp (thedate date,'CREDIT'),'Credit')
SELECT THEDATE,CUSTOMER,MAX(CASE
WHEN TYPE = 'CREDIT' THEN revenue
END) AS CREDIT,MAX(CASE
WHEN TYPE = 'CHARGE' THEN REVENUE
END) AS CHARGE
FROM #TEMP
GROUP BY CUSTOMER,THEDATE


或者您可以尝试使用PIVOT功能

SELECT *
FROM #TEMP
PIVOT (MAX(REVENUE)
FOR
[TYPE] IN ([CHARGE],[CREDIT])
) AS PIV