有如下简单需求:客户购买产品,厂商想看到收入和欠费统计。
已存在的数据库主要表结构:客户表(Customer)和销售记录表(SoldRecord),另外相关表(如产品表Product)在此略过。
1、客户表
这个表很简单:
字段说明:
字段说明:
3、视图创建(viewRecordDetail)
4、简单示例
下面向两个表里填充一些数据,测试一下。
(1)、客户表
已存在的数据库主要表结构:客户表(Customer)和销售记录表(SoldRecord),另外相关表(如产品表Product)在此略过。
1、客户表
这个表很简单:
字段说明:
tid int 自增字段 关键字
name 客户名
adddate 添加日期
address 客户所在地
2、销售记录表
name 客户名
adddate 添加日期
address 客户所在地
字段说明:
tid int 自增字段 关键字
customerid 客户tid
boughtdate 客户购买产品日期
paidmoney 金额(正数代表已付款,负数代表欠费)
客户需求分析:“厂商想看到收入和欠费统计”,分析上述表结构,可以看到问题的由来就出在这个表的paidmoney字段上。收入必须将paidmoney按照正数相加,欠费则必须按负数相加。这样我们就想到将paidmoney字段“
拆一为二
”,便于sql函数的统计计算。
customerid 客户tid
boughtdate 客户购买产品日期
paidmoney 金额(正数代表已付款,负数代表欠费)
3、视图创建(viewRecordDetail)
SELECT
dbo.Customer.tid, dbo.Customer.name, dbo.Customer.addDate, dbo.Customer.address, dbo.SoldRecord.boughtDate, dbo.SoldRecord.tid
AS
sid,
ABS ( CASE WHEN dbo.SoldRecord.paidMoney > 0 THEN dbo.SoldRecord.paidMoney ELSE 0 END ) AS income,
ABS ( CASE WHEN dbo.SoldRecord.paidMoney < 0 THEN dbo.SoldRecord.paidMoney ELSE 0 END ) AS outcome, dbo.SoldRecord.paidMoney
FROM dbo.Customer INNER JOIN
dbo.SoldRecord ON dbo.Customer.tid = dbo.SoldRecord.CustomerId
GROUP BY dbo.Customer.tid, dbo.SoldRecord.tid, dbo.Customer.name, dbo.Customer.addDate, dbo.Customer.address, dbo.SoldRecord.boughtDate,
dbo.SoldRecord.paidMoney
很明显,两个表的连接查询创建一个视图,重点是利用case when将paidmoney的拆分。其中income为收入,outcome为欠费,函数ABS取绝对值(我们当然可以利用其他函数如sum取值)。这样需要统计的话,直接对视图进行操作就可以了。
ABS ( CASE WHEN dbo.SoldRecord.paidMoney > 0 THEN dbo.SoldRecord.paidMoney ELSE 0 END ) AS income,
ABS ( CASE WHEN dbo.SoldRecord.paidMoney < 0 THEN dbo.SoldRecord.paidMoney ELSE 0 END ) AS outcome, dbo.SoldRecord.paidMoney
FROM dbo.Customer INNER JOIN
dbo.SoldRecord ON dbo.Customer.tid = dbo.SoldRecord.CustomerId
GROUP BY dbo.Customer.tid, dbo.SoldRecord.tid, dbo.Customer.name, dbo.Customer.addDate, dbo.Customer.address, dbo.SoldRecord.boughtDate,
dbo.SoldRecord.paidMoney
4、简单示例
下面向两个表里填充一些数据,测试一下。
(1)、客户表
use
testdb
insert into customer select ' jeff wong ' , getdate (), ' beijing '
union all select ' jeffery zhao ' , getdate (), ' shanghai '
union all select ' dudu ' , getdate (), ' shanghai '
union all select ' terrylee ' , getdate (), ' tianjin '
(2)、销售记录表
insert into customer select ' jeff wong ' , getdate (), ' beijing '
union all select ' jeffery zhao ' , getdate (), ' shanghai '
union all select ' dudu ' , getdate (), ' shanghai '
union all select ' terrylee ' , getdate (), ' tianjin '
use
testdb
insert into soldRecord select 1 , getdate (), 168
union all select 2 , getdate (), 223
union all select 1 , getdate (), - 7500
union all select 1 , getdate (), 268
union all select 4 , getdate (), - 113
union all select 3 , getdate (), 500
union all select 1 , getdate (), 22
union all select 4 , getdate (), 15
union all select 3 , getdate (), - 15000
union all select 1 , getdate (), 200
union all select 2 , getdate (), 111
union all select 2 , getdate (), 7000
union all select 2 , getdate (), 268
(3)、统计
insert into soldRecord select 1 , getdate (), 168
union all select 2 , getdate (), 223
union all select 1 , getdate (), - 7500
union all select 1 , getdate (), 268
union all select 4 , getdate (), - 113
union all select 3 , getdate (), 500
union all select 1 , getdate (), 22
union all select 4 , getdate (), 15
union all select 3 , getdate (), - 15000
union all select 1 , getdate (), 200
union all select 2 , getdate (), 111
union all select 2 , getdate (), 7000
union all select 2 , getdate (), 268
select
sum
(income)
as
totalIncome,
sum (outcome) as totalOutcome from viewRecordDetail
sum (outcome) as totalOutcome from viewRecordDetail
小结:这里只是记录一下个人解决问题的思路和简单实践,并不推荐直接利用sql的dbms做这些琐碎的统计处理。sql固然强大,但是,汝之蜜糖,焉知不是我之毒药呢?其实强大的高级语言如c#,java等都可以轻松实现这些功能,更何况创建太多视图什么的不易维护。还是千方百计地创建合理的表结构,交给高级语言处理去吧。
本文转自JeffWong博客园博客,原文链接:http://www.cnblogs.com/jeffwongishandsome/archive/2009/06/28/1512208.html,如需转载请自行联系原作者