| ylbtech-dbs:DBS-Tally book（记账本） |
-- =============================================
-- 记账本
-- 模仿小程序“记账e”业务流程
-- 13:17 2017/3/15
-- author:yb,gyg
-- =============================================
| 1，数据库关系图(Database Diagram) 返回顶部 |
| 2，数据库设计脚本(Database Design Script)返回顶部 |
V-2.2,
-- =============================================
-- 记账本
-- 模仿小程序“记账e”业务流程
-- 13:17 2017/3/15
-- author:yb,gyg
-- =============================================
USE master
GO -- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'SB_TallyBook'
)
DROP DATABASE SB_TallyBook
GO CREATE DATABASE SB_TallyBook
GO
use SB_TallyBook GO
-- =============================================
-- 科目表
-- =============================================
create table [Subject]
(
subjectId uniqueidentifier primary key,--编号【UI、PK】
subjectCode varchar(50),--科目代码
subjectName varchar(50),--分类名称
subjectType bit,--类别：0=收入-来源；1=支付-用途
flagDisabled bit --是否排除
) GO
-- =============================================
-- 账单表
-- =============================================
create table Bill
(
billId uniqueidentifier primary key,--编号【UI、PK】
subjectCode varchar(50),--科目代码
amount money,--金额
payWay varchar(50),--支付方式
remark varchar(200),--备注
pubdate datetime, --日期
accountId uniqueidentifier,--账户ID【UI、UQ】
flagDisabled bit --是否排除
) GO
-- =============================================
-- 测试数据
-- ============================================= -- 收入科目
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','收入',0,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','工资薪水',0,0) GO
-- 支出科目
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','食',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','早餐',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','午餐',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','晚餐',1,0) GO
-- =============================================
-- 操作
-- ============================================= go
-- 1、记一笔
-- 1.1、记一笔收入
insert Bill(billId,amount,subjectCode,payWay,remark,pubdate
,accountId,flagDisabled)
values(NEWID(),0,10000,'','储蓄卡','2月分工资'
,'2017-3-10',NEWID(),0)
-- 1.2、记一笔支出
insert Bill(billId,amount,subjectCode,payWay,remark,pubdate
,accountId,flagDisabled)
values(NEWID(),0,200,'','现金','请客户吃饭'
,'2017-3-17',NEWID(),0) -- 2、账单
-- 2.1、账单-分时
-- 2.1.1、?如果按日期分组，例如：2017-3；2017-2
-- 2.1.2、
select b.billId,b.billType,b.amount,b.subjectCode,b.payWay
,b.remark,b.pubdate,b.flagDisabled,s.subjectType,s.subjectName from Bill b inner join [Subject] s on b.subjectCode=s.subjectCode
where b.accountId='' and b.pubdate=''
-- 2.2、账单-分类
-- 2.2.1、
select b.subjectCode,COUNT(*)'cnt' from Bill b
group by b.subjectCode
having b.accountId=''
-- 2.2.2、
select b.billId,b.billType,b.amount,b.subjectCode,b.payWay
,b.remark,b.pubdate,b.flagDisabled,s.subjectType,s.subjectName from Bill b inner join [Subject] s on b.subjectCode=s.subjectCode
where b.accountId='' and b.subjectCode=''
V-2.1,
-- =============================================
-- 记账本
-- 模仿小程序“记账e”业务流程
-- 13:17 2017/3/15
-- author:yb,gyg
-- =============================================
USE master
GO -- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'SB_TallyBook'
)
DROP DATABASE SB_TallyBook
GO CREATE DATABASE SB_TallyBook
GO
use SB_TallyBook
GO
-- =============================================
-- 科目表
-- =============================================
create table [Subject]
(
subjectId uniqueidentifier primary key,--编号【UI、PK】
subjectCode varchar(50),--科目代码
subjectName varchar(50),--分类名称
subjectType bit,--类别：0=收入-来源；1=支付-用途
flagDisabled bit --是否排除
) GO
-- =============================================
-- 账单表
-- =============================================
create table Bill
(
billId uniqueidentifier primary key,--编号【UI、PK】
billType bit,--类别：0=收入；1=支付
amount money,--金额
subjectCode varchar(50),--科目代码
payWay varchar(50),--支付方式
remark varchar(200),--备注
pubdate datetime, --日期
accountId uniqueidentifier,--账户ID【UI、UQ】
flagDisabled bit --是否排除
)
GO GO
-- =============================================
-- 测试数据
-- ============================================= -- 收入科目
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','收入',0,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','工资薪水',0,0) GO
-- 支出科目
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','食',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','早餐',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','午餐',1,0)
insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)
values(NEWID(),'','晚餐',1,0) GO
-- =============================================
-- 操作
-- ============================================= go
-- 1、记一笔
-- 1.1、记一笔收入
insert Bill(billId,billType,amount,subjectCode,payWay,remark
,pubdate,accountId,flagDisabled)
values(NEWID(),0,10000,'','储蓄卡','2月分工资'
,'2017-3-10',NEWID(),0)
-- 1.2、记一笔支出
insert Bill(billId,billType,amount,subjectCode,payWay,remark
,pubdate,accountId,flagDisabled)
values(NEWID(),0,200,'','现金','请客户吃饭'
,'2017-3-17',NEWID(),0) -- 2、账单
-- 2.1、账单-分时
-- 2.1.1、?如果按日期分组，例如：2017-3；2017-2
-- 2.1.2、
select b.billId,b.billType,b.amount,b.subjectCode,b.payWay
,b.remark,b.pubdate,b.flagDisabled,s.subjectType,s.subjectName from Bill b inner join [Subject] s on b.subjectCode=s.subjectCode
where b.accountId='' and b.pubdate=''
-- 2.2、账单-分类
-- 2.2.1、
select b.subjectCode,COUNT(*)'cnt' from Bill b
group by b.subjectCode
having b.accountId=''
-- 2.2.2、
select b.billId,b.billType,b.amount,b.subjectCode,b.payWay
,b.remark,b.pubdate,b.flagDisabled,s.subjectType,s.subjectName from Bill b inner join [Subject] s on b.subjectCode=s.subjectCode
where b.accountId='' and b.subjectCode=''
2.0,
| 3，功能实现代码(Function Implementation Code)返回顶部 |
 |
作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 |