sql利用视图实现一个数值型字段的求和问题

最新推荐文章于 2022-10-20 22:15:07 发布

weixin_34357887

最新推荐文章于 2022-10-20 22:15:07 发布

阅读量899

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 c# java

原文链接：https://yq.aliyun.com/articles/383371

本文介绍如何通过SQL创建视图来实现对客户收入和欠费情况的有效统计。利用CASE WHEN语句对金额字段进行拆分，分别计算收入与欠费总额。

有如下简单需求：客户购买产品，厂商想看到收入和欠费统计。
已存在的数据库主要表结构：客户表（Customer）和销售记录表（SoldRecord），另外相关表（如产品表Product）在此略过。
1、客户表
这个表很简单：

字段说明：

tid  int  自增字段 关键字

name 客户名

adddate 添加日期

address 客户所在地

2、销售记录表

字段说明：

tid  int  自增字段  关键字

customerid  客户tid

boughtdate 客户购买产品日期

paidmoney  金额（正数代表已付款，负数代表欠费）

客户需求分析：“厂商想看到收入和欠费统计”，分析上述表结构，可以看到问题的由来就出在这个表的paidmoney字段上。收入必须将paidmoney按照正数相加，欠费则必须按负数相加。这样我们就想到将paidmoney字段“拆一为二”，便于sql函数的统计计算。
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取值）。这样需要统计的话，直接对视图进行操作就可以了。
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）、销售记录表

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）、统计

select sum(income) as totalIncome,
sum(outcome) as totalOutcome from viewRecordDetail

小结：这里只是记录一下个人解决问题的思路和简单实践，并不推荐直接利用sql的dbms做这些琐碎的统计处理。sql固然强大，但是，汝之蜜糖，焉知不是我之毒药呢？其实强大的高级语言如c#，java等都可以轻松实现这些功能，更何况创建太多视图什么的不易维护。还是千方百计地创建合理的表结构，交给高级语言处理去吧。