统计表-优快云博客

博客展示了2005年2月的财务数据，包含现金、银行存款、商品等信息，并给出了数据统计结果。同时提供了创建表、插入数据和进行数据查询的SQL代码，通过不同的查询语句对数据进行分类汇总统计。

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','ＢＯＣ',20)
insert into #b1 values('2005','2','100202','ＩＣＢＣ',30)
insert into #b1 values('2005','2','100201','ＢＯＣ',10)
insert into #b1 values('2005','2','100203','ＡＢＣ',50)
insert into #b1 values('2005','2','100202','ＩＣＢＣ',40)

SELECT * FROM #B1
/*
year       month      code       name       rmb
---------- ---------- ---------- ---------- ---------------------
2005       2          1001       CASH       10.0000
2005       2          1001       CASH       20.0000
2005       2          100201     ＢＯＣ        20.0000
2005       2          100202     ＩＣＢＣ       30.0000
2005       2          100201     ＢＯＣ        10.0000
2005       2          100203     ＡＢＣ        50.0000
2005       2          100202     ＩＣＢＣ       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     ＢＯＣ          30.0000
2005       2            100202     ＩＣＢＣ         70.0000
2005       2            100203     ＡＢＣ          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     ＢＯＣ          30.0000
2005       2          100202     ＩＣＢＣ         70.0000
2005       2          100203     ＡＢＣ          50.0000

(5 row(s) affected)

转载于:https://www.cnblogs.com/MoreTea/archive/2005/07/31/204094.html