| 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 masterGO-- Drop the database if it already existsIF EXISTS (SELECT name FROM sys.databases WHERE name = N'SB_TallyBook')DROP DATABASE SB_TallyBookGOCREATE DATABASE SB_TallyBookGOuse SB_TallyBookGO-- =============================================-- 科目表-- =============================================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(),'400','收入',0,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'40101','工资薪水',0,0)GO-- 支出科目insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'500','食',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50101','早餐',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50102','午餐',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50103','晚餐',1,0)GO-- =============================================-- 操作-- =============================================go-- 1、记一笔-- 1.1、记一笔收入insert Bill(billId,amount,subjectCode,payWay,remark,pubdate,accountId,flagDisabled)values(NEWID(),0,10000,'400','储蓄卡','2月分工资','2017-3-10',NEWID(),0)-- 1.2、记一笔支出insert Bill(billId,amount,subjectCode,payWay,remark,pubdate,accountId,flagDisabled)values(NEWID(),0,200,'500','现金','请客户吃饭','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.subjectCodewhere b.accountId='' and b.pubdate=''-- 2.2、账单-分类-- 2.2.1、select b.subjectCode,COUNT(*)'cnt' from Bill bgroup by b.subjectCodehaving 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.subjectCodewhere b.accountId='' and b.subjectCode=''
V-2.1,
-- =============================================-- 记账本-- 模仿小程序“记账e”业务流程-- 13:17 2017/3/15-- author:yb,gyg-- =============================================USE masterGO-- Drop the database if it already existsIF EXISTS (SELECT name FROM sys.databases WHERE name = N'SB_TallyBook')DROP DATABASE SB_TallyBookGOCREATE DATABASE SB_TallyBookGOuse SB_TallyBookGO-- =============================================-- 科目表-- =============================================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 --是否排除)GOGO-- =============================================-- 测试数据-- =============================================-- 收入科目insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'400','收入',0,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'40101','工资薪水',0,0)GO-- 支出科目insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'500','食',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50101','早餐',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50102','午餐',1,0)insert [Subject](subjectId,subjectCode,subjectName,subjectType,flagDisabled)values(NEWID(),'50103','晚餐',1,0)GO-- =============================================-- 操作-- =============================================go-- 1、记一笔-- 1.1、记一笔收入insert Bill(billId,billType,amount,subjectCode,payWay,remark,pubdate,accountId,flagDisabled)values(NEWID(),0,10000,'400','储蓄卡','2月分工资','2017-3-10',NEWID(),0)-- 1.2、记一笔支出insert Bill(billId,billType,amount,subjectCode,payWay,remark,pubdate,accountId,flagDisabled)values(NEWID(),0,200,'500','现金','请客户吃饭','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.subjectCodewhere b.accountId='' and b.pubdate=''-- 2.2、账单-分类-- 2.2.1、select b.subjectCode,COUNT(*)'cnt' from Bill bgroup by b.subjectCodehaving 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.subjectCodewhere b.accountId='' and b.subjectCode=''
2.0,
| 3，功能实现代码(Function Implementation Code) |
 | 作者：出处：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。 |