7,763
社区成员
发帖
与我相关
我的任务
分享
select c.客户名称,sum(预付金额)+sum(已收金额)-sum(应收金额) 余额 from 收款明细表 a
full join 客户预付金额表 b on a.客户代码=b.客户代码
left join 客户名称表 c on a.客户代码=c.客户代码 or c.客户代码=b.客户代码
group by c.客户代码
(SELECT a.客户名称, (a.Balance - b.Prepay) AS 余额 FROM (SELECT 客户代码, 客户名称, (SUM(应收金额) - SUM(已收金额)) As Balance FROM 收款明细表 GROUP BY 客户代码, 客户名称) a, (SELECT 客户代码, SUM(预付金额) AS Prepay FROM GROUP BY 客户代码) b WHERE a.客户代码 = b.客户代码 ORDER BY a.客户名称)
UNION
(SELECT 客户名称, (SUM(应收金额) - SUM(已收金额)) AS 余额 FROM 收款明细表 GROUP BY 客户代码, 客户名称 WHERE NOT 客户代码 IN (SELECT DISTINCT 客户代码 FROM 客户预付金额表))
UNION
(SELECT a.客户名称, (a.Balance - b.Prepay) AS 余额 FROM (SELECT 客户代码, 客户名称, (SUM(应收金额) - SUM(已收金额)) As Balance FROM 客户基本资料表 GROUP BY 客户代码, 客户名称) a, (SELECT 客户代码, SUM(预付金额) AS Prepay FROM 客户预付金额表 GROUP BY 客户代码 WHERE NOT 客户代码 IN (SELECT DISTINCT 客户代码 FROM 收款明细表) b WHERE a.客户代码 = b.客户代码 ORDER BY a.客户名称)
select 客户名称,sum(预付金额)+sum(已收金额)-sum(应收金额) 余额 from
(select coalesce(a.客户代码,b.客户代码) 客户代码,isnull(已收金额,0) 已收金额,isnull(应收金额,0) 应收金额,isnull(预付金额,0) 预付金额 from
收款明细表 a
Full Join 客户预付金额表 b on a.客户代码=b.客户代码
)aa
Inner Join 客户名称表 bb On aa.客户代码=bb.客户代码
group by 客户名称
select a.客户名称,sum(a.已收金额)-sum(a.应收金额)+sum(case when b.预付金额 is null then 0 else b.预付金额 end ) as 余额 from 收款明细表 a,客户预付金额表 b where a.客户代码=b.客户代码 group by a.客户名称