统计表
year month code name rmb
2005 2 1001 现金 10
2005 2 1001 现金 20
2005 2 100201 中行存款 20
2005 2 100202 工行存款 30
2005 2 100201 中行存款 10
2005 2 100203 农行存款 50
2005 2 100202 工行存款 40
2005 2 100301 304钢管 20
2005 2 100301 304钢管 30
2005 2 100302 316钢管 30
结果
year month code nanme rmb
2005 2 1001 现金 30
2005 2 1002 银行存款 150
2005 2 100201 中行存款 30
2005 2 100202 工行存款 70
2005 2 100203 农行存款 50
2005 2 1003 商品 80
2005 2 100301 304钢管 50
2005 2 100302 316钢管 30
另外有
B2 结构和数据如下
code nanme
1001 现金
1002 银行存款
1003 商品
1004 应收帐款
...
create table #b1 (year varchar(10),month varchar(10),code varchar(10),
name varchar(10),rmb money)
--delete from #b1
insert into #b1 values('2005','2','1001','CASH',10)
insert into #b1 values('2005','2','1001','CASH',20)
insert into #b1 values('2005','2','100201','BOC',20)
insert into #b1 values('2005','2','100202','ICBC',30)
insert into #b1 values('2005','2','100201','BOC',10)
insert into #b1 values('2005','2','100203','ABC',50)
insert into #b1 values('2005','2','100202','ICBC',40)
SELECT * FROM #B1
/*
year month code name rmb
---------- ---------- ---------- ---------- ---------------------
2005 2 1001 CASH 10.0000
2005 2 1001 CASH 20.0000
2005 2 100201 BOC 20.0000
2005 2 100202 ICBC 30.0000
2005 2 100201 BOC 10.0000
2005 2 100203 ABC 50.0000
2005 2 100202 ICBC 40.0000
(7 row(s) affected)
*/
SELECT B.[YEAR] ,B.[MONTH],B.[CODE],B.[NAME],SUM(rmb) AS RMB FROM
#B1 B
WHERE B.[name] = 'CASH'
GROUP BY
B.[YEAR] ,B.[MONTH],B.[CODE],B.[NAME]
UNION ALL
SELECT B.[YEAR] ,B.[MONTH],CODE = LEFT(B.[CODE],4),'BANK DEPOSIT' ,SUM(rmb) FROM
#B1 B
WHERE B.[name] <> 'CASH'
GROUP BY
B.[YEAR] ,B.[MONTH],LEFT(B.[CODE],4)
UNION ALL
SELECT B.[YEAR] ,B.[MONTH],CODE =(SPACE(2) + B.[CODE]) ,B.[NAME],SUM(rmb) FROM
#B1 B
WHERE B.[name] <> 'CASH'
GROUP BY
B.[YEAR] ,B.[MONTH],B.[CODE],B.[NAME]
/*
YEAR MONTH CODE NAME RMB
---------- ---------- ------------ ------------ ---------------------
2005 2 1001 CASH 30.0000
2005 2 1002 BANK DEPOSIT 150.0000
2005 2 100201 BOC 30.0000
2005 2 100202 ICBC 70.0000
2005 2 100203 ABC 50.0000
(5 row(s) affected)
*/
SELECT B.[YEAR],B.[MONTH],CODE = LEFT(B.[CODE],4),NAME=CASE B.[NAME]
WHEN 'CASH' THEN B.[NAME]
ELSE 'BANK DEPOSIT'
END,SUM(RMB)
AS RMB
FROM #B1 B
GROUP BY B.[YEAR],B.[MONTH],LEFT(B.[CODE],4),
(CASE B.[NAME]
WHEN 'CASH' THEN B.[NAME]
ELSE 'BANK DEPOSIT'
END)
UNION ALL
SELECT B.[YEAR],B.[MONTH],B.[CODE],B.[NAME ],SUM(RMB)
FROM #B1 B
WHERE B.[NAME] <> 'CASH'
GROUP BY B.[YEAR],B.[MONTH],CODE,B.[NAME ]
/*
YEAR MONTH CODE NAME RMB
---------- ---------- ---------- ------------ ---------------------
2005 2 1001 CASH 30.0000
2005 2 1002 BANK DEPOSIT 150.0000
2005 2 100201 BOC 30.0000
2005 2 100202 ICBC 70.0000
2005 2 100203 ABC 50.0000
(5 row(s) affected)
*/