有一个主表是账号表:
ID,账号,初始金额
1,账号1,100
2,账号2,200 子表1是收入表:
账号,类别,时间,金额
账号1,收入,2009.08.11 ,50
账号1,收入,2009.08.10 ,150
账号2,收入,2009.08.04 ,50
账号2,收入,2009.08.17 ,250子表2是支出表:
账号,类别,时间,金额
账号1,支出,2009.08.11 ,150
账号1,支出,2009.08.10 ,250
账号2,支出,2009.08.04 ,350
账号2,支出,2009.08.17 ,150想生成如下报表:
账号,入合计,出合计,余额
账号1,200,400,-100
账号2,300,500,0这个问题困扰我两天了,请高手赐教啊。
ID,账号,初始金额
1,账号1,100
2,账号2,200 子表1是收入表:
账号,类别,时间,金额
账号1,收入,2009.08.11 ,50
账号1,收入,2009.08.10 ,150
账号2,收入,2009.08.04 ,50
账号2,收入,2009.08.17 ,250子表2是支出表:
账号,类别,时间,金额
账号1,支出,2009.08.11 ,150
账号1,支出,2009.08.10 ,250
账号2,支出,2009.08.04 ,350
账号2,支出,2009.08.17 ,150想生成如下报表:
账号,入合计,出合计,余额
账号1,200,400,-100
账号2,300,500,0这个问题困扰我两天了,请高手赐教啊。
SUM(B.金额 )+A.初始金额 -SUM(C.金额 ) AS 余额
FROM A ,B,C WHERE A.账号=B.账号 AND A.账号=C.账号GROUP BY A.账号
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 11:41:37
-------------------------------------
--> 生成测试数据: @账号表
DECLARE @账号表 TABLE (ID INT,账号 VARCHAR(5),初始金额 INT)
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150--SQL查询如下:SELECT A.账号,ISNULL(B.金额,0) AS 入合计,ISNULL(C.金额,0) AS 出合计,
A.初始金额 + ISNULL(B.金额,0) - ISNULL(C.金额,0) AS 余额
FROM @账号表 AS A
LEFT JOIN (SELECT 账号,SUM(金额) AS 金额 FROM @收入表 GROUP BY 账号) AS B
ON A.账号 = B.账号
LEFT JOIN (SELECT 账号,SUM(金额) AS 金额 FROM @支出表 GROUP BY 账号) AS C
ON A.账号 = C.账号/*
账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0(2 行受影响)
*/
账号=a.账号,
入合计=SUM(b.金额),
出合计=SUM(c.金额),
余额=初始金额-SUM(b.金额)-SUM(c.金额)
from a join b on a.账号=b.账号
join c on a.账号=c.账号
group by a.账号
出合计=sum(case when 金额<0 then -金额 else 0 end),
余额=sum(金额) from(
select 账号,金额 from 账号表 unioni all
select 账号,金额 from 收入表 unioni all
select 账号,-金额 from 支出表)group by 账号
DECLARE @账号表 TABLE (ID INT,账号 VARCHAR(5),初始金额 INT)
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150
select
账号=a.账号,
入合计=SUM(distinct b.金额),
出合计=SUM(distinct c.金额),
余额=max(初始金额)+SUM(distinct b.金额)-SUM(distinct c.金额)
from @账号表 a join @收入表 b on a.账号=b.账号
join @支出表 c on a.账号=c.账号
group by a.账号
/*
账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0 */
数据借用
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150select 账号表.账号,入合计,出合计,余额=初始金额+入合计-出合计
from @账号表 账号表 join (select 账号,sum(金额) 入合计 from @收入表 group by 账号) 收入表
on 账号表.账号=收入表.账号join (select 账号,sum(金额) 出合计 from @支出表 group by 账号) 支出表on 账号表.账号=支出表.账号/*
账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0(2 行受影响)
*/
DECLARE @账号表 TABLE (ID INT,账号 VARCHAR(5),初始金额 INT)
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150
select
账号=a.账号,
入合计=isnull(SUM(distinct b.金额),0),
出合计=isnull(SUM(distinct c.金额),0),
余额=max(初始金额)+isnull(SUM(distinct b.金额),0)-isnull(SUM(distinct c.金额),0)
from @账号表 a left join @收入表 b on a.账号=b.账号
left join @支出表 c on a.账号=c.账号
group by a.账号
/*
账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0 */
...还是用Left
a.账号,b.sum(金额),c.sum(金额),
余额=b.sum(金额)-c.sum(金额)+a.初始金额,
from
账号表 a,
(select 账号,类别,sum(金额) as 入合计 from 子表1 group by 账号,类别)b,
(select 账号,类别,sum(金额) as 出合计 from 子表2 group by 账号,类别)c
where
a.账号=b.账号
and
a.账号=c.账号
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150SELECT A.账号,SUM(B.金额 )AS 入合计,SUM(C.金额 )AS 出合计,
SUM(B.金额 )+SUM(A.初始金额) -SUM(C.金额 ) AS 余额
FROM @账号表 A ,@收入表 B ,@支出表 C WHERE A.账号=B.账号 AND A.账号=C.账号GROUP BY A.账号
(所影响的行数为 2 行)
(所影响的行数为 4 行)
(所影响的行数为 4 行)账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 400 800 0
账号2 600 1000 400(所影响的行数为 2 行)
修改一下
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150SELECT A.账号,SUM(DISTINCT B.金额 )AS 入合计,SUM(DISTINCT C.金额 )AS 出合计,
SUM(DISTINCT B.金额 )+SUM(DISTINCT A.初始金额) -SUM(DISTINCT C.金额 ) AS 余额
FROM @账号表 A ,@收入表 B ,@支出表 C WHERE A.账号=B.账号 AND A.账号=C.账号GROUP BY A.账号(所影响的行数为 2 行)
(所影响的行数为 4 行)
(所影响的行数为 4 行)账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0(所影响的行数为 2 行)
没想像中简单呀,晕
DECLARE @账号表 TABLE (ID INT,账号 VARCHAR(5),初始金额 INT)
INSERT INTO @账号表
SELECT 1,'账号1',100 UNION ALL
SELECT 2,'账号2',200
--> 生成测试数据: @收入表
DECLARE @收入表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @收入表
SELECT '账号1','收入','2009.08.11',50 UNION ALL
SELECT '账号1','收入','2009.08.10',150 UNION ALL
SELECT '账号2','收入','2009.08.04',50 UNION ALL
SELECT '账号2','收入','2009.08.17',250
--> 生成测试数据: @支出表
DECLARE @支出表 TABLE (账号 VARCHAR(5),类别 VARCHAR(4),时间 DATETIME,金额 INT)
INSERT INTO @支出表
SELECT '账号1','支出','2009.08.11',150 UNION ALL
SELECT '账号1','支出','2009.08.10',250 UNION ALL
SELECT '账号2','支出','2009.08.04',350 UNION ALL
SELECT '账号2','支出','2009.08.17',150select
a.账号,b.入合计,c.出合计,
余额=b.入合计-c.出合计+a.初始金额
from
@账号表 a,
(select 账号,类别,sum(金额) as 入合计 from @收入表 b group by 账号,类别)b,
(select 账号,类别,sum(金额) as 出合计 from @支出表 c group by 账号,类别)c
where
a.账号=b.账号
and
a.账号=c.账号
/*账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0(2 行受影响)*/
select a.帐号,a.收入 入合计,b.支出 出合计,(c.初始金额+a.收入-b.支出) 余额 from
(select 帐号,sum(金额) 收入 from 子表1 group by 帐号) a
inner join
(select 帐号,sum(金额) 支出 from 子表2 group by 帐号) b
on a.帐号=b.帐号
inner join
帐号表 c
on a.帐号=c.帐号
----------------------------------
-- SQL后花园
-- 小卒
----------------------------------if object_id('账号表')is not null drop table 账号表
go
create table 账号表(ID INT,账号 nvarchar(20),初始金额 INT )
insert 账号表 select
1,'账号1',100 union all select
2,'账号2',200 if object_id('收入表')is not null drop table 收入表
go
create table 收入表(账号 nvarchar(20),类别 nvarchar(20),时间 DATETIME ,金额 INT )
insert 收入表 select
'账号1','收入','2009.08.11' ,50 union all select
'账号1','收入','2009.08.10' ,150 union all select
'账号2','收入','2009.08.04' ,50 union all select
'账号2','收入','2009.08.17',250 if object_id('支出表')is not null drop table 支出表
go
create table 支出表(账号 nvarchar(20),类别 nvarchar(20),时间 DATETIME ,金额 INT )
insert 支出表 select
'账号1','支出','2009.08.11' ,150 union all select
'账号1','支出','2009.08.10' ,250 union all select
'账号2','支出','2009.08.04' ,350 union all select
'账号2','支出','2009.08.17' ,150
SELECT T1.账号,[入合计],[出合计],余额=[入合计]-[出合计]+初始金额 from
(SELECT A.账号,[入合计]=sum(B.金额),初始金额 from 账号表 A
JOIN 收入表 B ON A.账号=B.账号
GROUP BY A.账号,初始金额)t1
join(
SELECT A.账号,[出合计]=SUM(C.金额) from 账号表 A
JOIN 支出表 C ON A.账号=C.账号
GROUP BY A.账号)t2
on t1.账号=t2.账号
账号 入合计 出合计 余额
-------------------- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0(2 行受影响)
账号,
入合计=sum(收入),
出合计=sum(支出),
余额=sum(初始金额)+sum(收入)-sum(支出)
from
(
select 账号,初始金额,0 as 收入, 0 as 支出 from 账号表
union all
select 账号,0,金额,0 from 收入表
union all
select 账号,0,0,金额 from 支出表
)t
group by
账号/**账号 入合计 出合计 余额
----- ----------- ----------- -----------
账号1 200 400 -100
账号2 300 500 0**/