日期:2014-05-18 浏览次数:20554 次
DECLARE @wldw TABLE (单位ID int, 单位名称 nvarchar(11), 科室名称 nvarchar(10), root int, 档案文件 nvarchar(10), 单位拼音 nvarchar(10)) INSERT @wldw VALUES(1,'AAAA','AAAA','0','A001','AAAA') INSERT @wldw VALUES(2,'AAAA','A1','1','A001','AAAA') INSERT @wldw VALUES(3,'AAAA','A2','2','A001','AAAA') INSERT @wldw VALUES(4,'BBBB','BBBB','0','B001','BBBB') INSERT @wldw VALUES(5,'BBBB','B1','1','B001','BBBB') INSERT @wldw VALUES(6,'BBBB','B2','2','B001','BBBB') INSERT @wldw VALUES(7,'BBBB','B3','3','B001','BBBB') DECLARE @YSZK TABLE (FID int,单据编号 nvarchar(11), 单位ID int, 货款金额 money) INSERT @YSZK VALUES(1,'20110403001','1',100) INSERT @YSZK VALUES(2,'20110403002','1',100) INSERT @YSZK VALUES(3,'20110403003','2',100) INSERT @YSZK VALUES(4,'20110403004','3',100) INSERT @YSZK VALUES(5,'20110403005','3',100) INSERT @YSZK VALUES(6,'20110403006','5',100) INSERT @YSZK VALUES(7,'20110403007','4',100) INSERT @YSZK VALUES(8,'20110403008','5',100) DECLARE @FKJL TABLE (FID int,单据编号 nvarchar(11), 付款款金额 money) INSERT @FKJL VALUES(1,'20110403001',-50) INSERT @FKJL VALUES(2,'20110403002',-100) INSERT @FKJL VALUES(3,'20110403003',-100) INSERT @FKJL VALUES(4,'20110403004',-100) INSERT @FKJL VALUES(5,'20110403005',-100) INSERT @FKJL VALUES(6,'20110403001',-20) INSERT @FKJL VALUES(7,'20110403002',-30)
DECLARE @wldw TABLE (单位ID int, 单位名称 nvarchar(11), 科室名称 nvarchar(10), root int, 档案文件 nvarchar(10), 单位拼音 nvarchar(10)) INSERT @wldw VALUES(1,'AAAA','AAAA','0','A001','AAAA') INSERT @wldw VALUES(2,'AAAA','A1','1','A001','AAAA') INSERT @wldw VALUES(3,'AAAA','A2','2','A001','AAAA') INSERT @wldw VALUES(4,'BBBB','BBBB','0','B001','BBBB') INSERT @wldw VALUES(5,'BBBB','B1','1','B001','BBBB') INSERT @wldw VALUES(6,'BBBB','B2','2','B001','BBBB') INSERT @wldw VALUES(7,'BBBB','B3','3','B001','BBBB') DECLARE @YSZK TABLE (FID int,单据编号 nvarchar(11), 单位ID int, 货款金额 money) INSERT @YSZK VALUES(1,'20110403001','1',100) INSERT @YSZK VALUES(2,'20110403002','1',100) INSERT @YSZK VALUES(3,'20110403003','2',100) INSERT @YSZK VALUES(4,'20110403004','3',100) INSERT @YSZK VALUES(5,'20110403005','3',100) INSERT @YSZK VALUES(6,'20110403006','5',100) INSERT @YSZK VALUES(7,'20110403007','4',100) INSERT @YSZK VALUES(8,'20110403008','5',100) DECLARE @FKJL TABLE (FID int,单据编号 nvarchar(11), 付款款金额 money) INSERT @FKJL VALUES(1,'20110403001',-50) INSERT @FKJL VALUES(2,'20110403002',-100) INSERT @FKJL VALUES(3,'20110403003',-100) INSERT @FKJL VALUES(4,'20110403004',-100) INSERT @FKJL VALUES(5,'20110403005',-100) INSERT @FKJL VALUES(6,'20110403001',-20) INSERT @FKJL VALUES(7,'20110403002',-30) ; SELECT a.*,ISNULL(货款金额,0) AS 货款金额, ISNULL(付款款金额,0) AS 付款款金额,ISNULL(欠款金额,0) AS 欠款金额 FROM @wldw AS a left JOIN (SELECT a.单位ID,SUM(货款金额) AS 货款金额,SUM(ISNULL(付款款金额,0)) AS 付款款金额,SUM(货款金额-ISNULL(付款款金额,0)) AS 欠款金额 FROM @YSZK AS a LEFT JOIN @FKJL AS b ON a.单据编号=b.单据编号 GROUP BY a.单位ID)b ON a.单位ID=b.单位ID /* 单位ID 单位名称 科室名称 root 档案文件 单位拼音 货款金额 付款款金额 欠款金额 1 AAAA AAAA 0 A001 AAAA 400.00 -200.00 600.00 2 AAAA A1 1 A001 AAAA 100.00 -100.00 200.00 3 AAAA A2 2 A001 AAAA 200.00 -200.00 400.00 4 BBBB BBBB 0 B001 BBBB 100.00 0.00 100.00 5 BBBB B1 1 B001 BBBB 200.00 0.00 200.00 6 BBBB B2 2 B001 BBBB 0.00 0.00 0.00 7 B