我想在一个查询中使来自两个不同表的数据位于一个位置,我必须使用pivot,而在另一个查询中,我想要将来自表1的三个相同客户名称的账单总和相加
表1查询
SELECT Cname
,Advance
,Bill
,Refund
FROM (
SELECT cname
,r.Amount
,R.PaymentType
FROM ReceiptDetails r
) AS sourcetable
PIVOT(
SUM(Amount)FOR PaymentType IN ([Advance] ,[Bill] ,[Refund])
) AS PivotTable表2查询
SELECT Cname
,SUM(cb.totalandvat)
FROM Customer_BillCname是两个表中的公共字段
基本上,我想要生成客户的缴费列表,因此从表一(收件人详细信息)姓名,(总和)预付款和其他付款,并从第二个表(账单详细信息)的总金额从多个账单
发布于 2016-01-07 12:49:39
只需在两个outer之间执行一个完整的外连接,如下所示:
SELECT
CName = coalesce(payment.cname,bill.cname)
,bill = coalesce(bill.Amount)
,Advance = coalesce(payment.Advance,0)
,Bill = coalesce(payment.Bill ,0)
,Refund = coalesce(payment.Refund ,0)
FROM (
SELECT Cname
,Advance
,Bill
,Refund
FROM (
SELECT cname
,r.Amount
,R.PaymentType
FROM ReceiptDetails r
) AS sourcetable
PIVOT(
SUM(Amount)FOR PaymentType IN ([Advance] ,[Bill] ,[Refund])
) AS PivotTable
) Payment
full outer join (
SELECT Cname
,SUM(cb.totalandvat) AS Amount
FROM Customer_Bill
) bill
ON payment.cname = bill.cnamehttps://stackoverflow.com/questions/34647286
复制相似问题