--创建建库bankDBCREATE DATABASE bankDB ON ( NAME='bankDB_data', FILENAME='E:\bankDB_data.mdf', SIZE=3mb, FILEGROWTH=15% ) LOG ON ( NAME= 'bankDB_log', FILENAME='E:\bankDB_log.ldf', SIZE=3mb, FILEGROWTH=15% )GO/*$$$$$$$$$$$$$建表$$$$$$$$$$$$$$$$$$$$$$$$*/USE bankDBGOCREATE TABLE userInfo --用户信息表( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(20) NOT NULL, address VARCHAR(50))GOCREATE TABLE cardInfo --银行卡信息表( cardID CHAR(19) NOT NULL, curID VARCHAR(10) NOT NULL, savingID INT NOT NULL, openDate DATETIME NOT NULL, openMoney MONEY NOT NULL, balance MONEY NOT NULL, pass CHAR(6) NOT NULL, IsReportLoss BIT NOT NULL, customerID INT NOT NULL)GOCREATE TABLE tradeInfo --交易信息表( tradeDate DATETIME NOT NULL, tradeType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, tradeMoney MONEY NOT NULL, remark TEXT )GOCREATE TABLE Deposit --存款类型表( savingID INT IDENTITY(1,1), savingName VARCHAR(20) NOT NULL, descrip VARCHAR(50))GO/*$$$$$$$$$$$$$加约束$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/ALTER TABLE Deposit ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)GO/* userInfo表的约束customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入*/ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID), CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ), CONSTRAINT UQ_PID UNIQUE(PID), --CONSTRAINT CK_telephone CHECK( telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or len(telephone)=13 ) CONSTRAINT CK_telephone CHECK( telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )GO/*cardInfo表的约束cardID卡号必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义， 如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始curType货币必填，默认为RMBsavingType存款种类活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元,否则将销户pass密码必填，6位数字，默认为6个8IsReportLoss是否挂失 必填，是/否值，默认为”否”customerID顾客编号必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡*/ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), CONSTRAINT DF_curID DEFAULT('RMB') FOR curID, --CONSTRAINT CK_savingType CHECK(savingType IN ('活期','定活两便','定期')), CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate, CONSTRAINT CK_openMoney CHECK(openMoney>=1), CONSTRAINT CK_balance CHECK(balance>=1), CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'), CONSTRAINT DF_pass DEFAULT('888888') FOR pass, CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss, CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)GO/* tradeInfo表的约束tradeType 必填，只能是存入/支取 cardID卡号必填，外健，可重复索引tradeMoney交易金额必填，大于0tradeDate交易日期必填，默认为系统当前日期remark备注可选输入，其他说明*/ALTER TABLE tradeInfo ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')), CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID), CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0), CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDateGO--存款类型INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')INSERT INTO deposit (savingName) VALUES ('定活两便')INSERT INTO deposit (savingName) VALUES ('通知')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')SELECT * FROM DEPOSITINSERT INTO userInfo(customerName,PID,telephone,address ) VALUES('张三','123456789012345','010-67898978','北京海淀')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1234 5678',1,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES('李四','321245678912345678','0478-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1134',2,1,1,2)INSERT INTO userInfo(customerName,PID,telephone) VALUES('王五','567891234532124670','010-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1130',2,1,1,3)INSERT INTO userInfo(customerName,PID,telephone) VALUES('丁六','567891321242345618','0752-43345543')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1004',2,1,1,4)SELECT * FROM userInfoSELECT * FROM cardInfoGO/*张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录， 同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少300元）*//*--------------交易信息表插入交易记录--------------------------*/INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1234 5678',900) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1130',300) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1004',1000) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1212 1130',1900) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+1900 WHERE cardID='010 3576 1212 1130'--用例4:模拟常规业务-- 1.修改客户密码 --1.修改卡号(cardID)为"1010 3576 1234 5678"银行卡密码为123456 update cardInfo set pass='123456' where cardID='1010 3576 1234 5678' --2.修改卡号(cardID)为"1010 3576 1212 1134"银行卡密码为1231230 update cardInfo set pass='123123' where cardID ='1010 3576 1212 1134' -- 2.办理银行卡挂失 --卡号(cardID)为"1010 3576 1212 1134"因银行卡丢失,申请挂失 update cardInfo set IsReportLoss='1'where cardID='1010 3576 1212 1134' --查看该卡是否为挂失状态 (lsReportLoss为0 表示 未挂失状态,为1表示 挂失状态 select * from cardInfo where cardID='1010 3576 1212 1134' -- 3.统计银行资金流通余额和盈利计算 declare @inmoney money,@outmonry money select @inmoney=sum(tradeMoney) from tradeInfo where tradeType='存入' select @outmonry=sum(tradeMoney) from tradeInfo where tradeType='支取' print '资金流通余额为:'+convert(varchar(20),(@inmoney-@outmonry)) print '盈利结算为:'+convert(varchar(20),(@outmonry*0.008)-(@inmoney*0.003)) -- 4.查询本周开户信息 select 卡号=cardID,金额类型=curID,存款类型=savingID,开户日期=openDate, 开户金额=openMoney,余额=balance,是否挂失=IsReportLoss from cardInfo where (datediff(day,getDate(),openDate)<datepart(weekday,openDate)) --5.查询本月交易金额最高的卡号 print '本月交易最高的卡号是:' select distinct(cardID) from tradeInfo where tradeMoney in(select max(tradeMoney) from tradeInfo where (datediff(mm,getDate(),tradeDate)<datepart(mm,tradeDate))) --6.查询挂失客户 print '挂失帐号的客户信息:' select 用户名=customerName,联系电话=telephone from userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID where IsReportLoss='1' --7催款提醒业务 select 用户名=customerName,联系电话=telephone,卡上余额=balance from userInfo inner join cardInfo on userInfo.customerID =cardInfo.customerID where balance<200 and datepart(day,getDate())=30 --用例5:为三个表创建视图 if exists(select * from sysobjects where name='view_userInfo') drop view view_userInfo go create view view_userInfo as select 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address from userInfo go select * from view_userInfo if exists(select * from sysobjects where name='view_cardInfo') drop view view_cardInfo go create view view_cardInfo as select 卡号=cardID,货币=curID,存款类型=savingID,开户日期=openDate,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardInfo go select * from view_cardInfo if exists(select * from sysobjects where name='view_Info') drop view view_Info go create view view_Info as select 交易日期=tradeDate,交易类型=tradeType,卡号=cardID,交易金额=tradeMoney,备注=remark from tradeInfo go select * from view_Info --用例6: --使用储存过程实现业务处理 --1.完成存款或取款业务 if exists(select * from sysobjects where name='proc_takeMoney') drop procedure proc_takeMoney go create procedure proc_takeMoney @card varchar(20)=null, @transMoney money=null, @transType varchar(10)=null, @password varchar(20)=null as if not exists(select * from cardInfo where cardID=@card) begin raiserror ('卡号不存在!',18,4) return end begin transaction declare @pass varchar(20) declare @numerror int set @numerror=0 select @pass=pass from cardInfo where cardID=@card if(@pass=@password and @transType='支取') begin update cardInfo set balance=balance-@transMoney where cardID=@card and pass=@password set @numerror=@numerror+@@error end else if(@transType='存入') begin update cardInfo set balance=balance+@transMoney where cardID=@card set @numerror=@numerror+@@error end if(@numerror<>0) begin print '交易正进行,请稍等.........' print '交易失败!余额不足!' select 卡号=@card,余额=balance from cardInfo where cardID=@card rollback transaction end else begin print '交易正进行,请稍等.........' print '交易成功!交易金额:'+convert(varchar(20),@transMoney) select 卡号=@card,余额=balance from cardInfo where cardID=@card insert into tradeInfo(cardID,tradeType,tradeMoney) values(@card,@transType,@transMoney) commit transaction end go exec proc_takeMoney @card='1010 3576 1234 5678',@transMoney=100,@transType='支取',@password='123456' exec proc_takeMoney @card='1010 3576 1212 1134',@transMoney=500,@transType='存入' select 卡号=cardID,货币种类=curID,存款类型=savingID,开户日期=openDate, 余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardInfo select 交易日期=tradeDate,交易类型=tradeType,卡号=cardID,交易金额=tradeMoney,备注=remark from tradeInfo --2.产生随机卡号 if exists(select * from sysobjects where name='proc_randCardID') drop procedure proc_randCardID go create procedure proc_randCardID @randCardID varchar(30) output as declare @r numeric(15,8) declare @r2 varchar(20) select @r=rand((datepart(mm,getDate())*100000)+(datepart(ss,getDate())*1000)+(datepart(ms,getDate()))) set @r2=@r select @randCardID='1010 3576 '+substring(@r2,3,4)+' '+substring(@r2,7,4) go declare @mycardID varchar(20) exec proc_randCardID @mycardID output print '产生的随机卡号为：'+convert(varchar(20),@mycardID) --3.完成开户业务 --检测该储存过程是否存在，有则删除 if exists(select * from sysobjects where name='proc_openAccount')drop procedure proc_openAccountgo--创建储存过程create procedure proc_openAccount@customerName varchar(20),@PID varchar(20),@telephone varchar(15),@openMoney money,@savingType int,@address varchar(255)asdeclare @mycardID varchar(20)declare @customerID intexec proc_randCardID @mycardID outputwhile exists(select * from cardInfo where cardID=@mycardID) exec proc_randCardID @mycardID outputwhile not exists(select * from cardInfo where cardID=@mycardID) begin insert into userInfo(customerName,PID,telephone,address) values(@customerName,@PID,@telephone,@address) select @customerID=customerID from userInfo where PID=@PID insert into cardInfo(cardID,savingID,openMoney,balance,customerID) values(@mycardID,@savingType,@openMoney,@openMoney,@customerID) declare @day datetime select @day=openDate from cardInfo where customerID=@customerID print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+convert(varchar(20),@mycardID) print '开户日期'+convert(varchar(30),@day)+' 开户金额：'+convert(varchar(20),@openMoney) end goexec proc_openAccount @customerName='张飞',@PID='213445678912348551',@telephone='0702-11567866',@openMoney=100000,@savingType=1,@address='河南新乡'select 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address from userInfoselect 卡号=cardID,贷币种类=curID,存款类型=savingID,开户日期=openDate,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardInfo --4.分页显示查询交易数据 if exists (select * from sysobjects where name='usp_pagingDisplay') drop procedure usp_pagingDisplay go create procedure usp_pagingDisplay @records int = 10, @page int = 1AS SET NOCOUNT ON DECLARE @rec1 int SET @rec1 = @records --@page * @records DECLARE @rec2 int SET @rec2 = (@page - 1) * @records DECLARE @statement nvarchar(200) SET @statement='SELECT TOP ' + CAST(@rec1 AS varchar(10)) + ' tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 FROM tradeInfo WHERE cardID not in (SELECT TOP '+ CAST(@rec2 AS varchar(10)) + ' cardID FROM tradeInfo)' --print @statement EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2GO--EXEC usp_pagingDisplay 2,2 --5.打印客户对账单 if exists (select * from sysobjects where name = 'usp_CheckSheet')drop proc usp_CheckSheetGOCREATE PROCEDURE usp_CheckSheet @cardID varchar(19), @date1 datetime=NULL, @date2 datetime=NULLASDECLARE @custName varchar(20)DECLARE @curName varchar(20)DECLARE @savingName varchar(20)DECLARE @openDate datetimeSELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName, @savingName=d.savingName , @openDate=c.openDate FROM cardInfo c, userInfo u, deposit dWHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID --and u.customerName = user_name()PRINT '卡号：' + @cardIDPRINT '姓名:' + @custNamePRINT '货币:' + @curNamePRINT '存款类型：' + @savingNamePRINT '开户日期：' + CAST(DATEPART(yyyy,@openDate) AS VARCHAR(4))+'年' + CAST(DATEPART(mm,@openDate) AS VARCHAR(2))+'月' + CAST(DATEPART(dd,@openDate) AS VARCHAR(2))+'日'PRINT ' 'print '--------------------------------------------------------------------' IF @date1 IS NULL AND @date2 IS NULL BEGINSELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID='1010 3576 1212 1134'--@cardIDORDER BY tradeDateRETURN END ELSE IF @date2 IS NULL SET @date2 = getdate() SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2ORDER BY tradeDateGO--测试打印对帐单EXEC usp_CheckSheet '1010 3576 1212 1134'EXEC usp_CheckSheet '1010 3576 1212 1134','2009-11-2','2009-11-30' --6.统计未发生交易的账户 if exists (select * from sysobjects where name = 'usp_getWithoutTrade')drop proc usp_getWithoutTradeGOcreate procedure usp_getWithoutTrade @Num int output, @Amount decimal(18,2) output, @date1 datetime = NULL, @date2 datetime = NULLAS IF @date1 IS NULL BEGINdeclare @dateStr varchar(50)set @dateStr = convert(varchar(4),DATEPART(YY,GETDATE())) + '-'+convert(varchar(2),DATEPART(mm,GETDATE())) + '-1 00:00:00.000' set @date1 = convert(datetime, @datestr,101) END IF @date2 IS NULLSET @date2 = getdate() SELECT distinct u.customerID 客户号,u.customerName 客户姓名,u.PID 身份证号,u.telephone 电话,address 地址 FROM userInfo u JOIN cardInfo c ON u.customerID = c.customerID WHERE c.cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2) SELECT @Num=COUNT(customerID), @Amount=SUM(balance) FROM cardInfo WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2)GODECLARE @NUM intDECLARE @Amount decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2PRINT '统计未发生交易的客户'PRINT '---------------------------------------'PRINT '客户人数：' + CAST(@NUM AS varchar(10)) + ' 客户总余额：' + CAST(@Amount AS varchar(20)) --7.统计银行卡交易量和交易额 if exists (select * from sysobjects where name = 'usp_getTradeInfo')drop proc usp_getTradeInfoGOcreate procedure usp_getTradeInfo @Num1 int output, @Amount1 decimal(18,2) output, @Num2 int output, @Amount2 decimal(18,2) output, @date1 datetime, @date2 datetime = NULL, @address varchar(20) = NULLAS -- 初始化变量 SET @Num1 = 0 SET @Amount1 = 0 SET @Num2 = 0 SET @Amount2 = 0 IF @date2 IS NULLSET @date2 = getdate() IF @address IS NULL BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' END ELSE BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' AND address Like '%'+@address+'%' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' AND address Like '%'+@address+'%' ENDGO--测试DECLARE @CNT1 intDECLARE @Total1 decimal(18,2)DECLARE @CNT2 intDECLARE @Total2 decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getTradeInfo @CNT1 OUTPUT, @Total1 OUTPUT, @CNT2 OUTPUT, @Total2 OUTPUT, @date1, @date2--, '北京'PRINT '统计银行卡交易量和交易额'PRINT ''PRINT '起始日期：' + CONVERT(varchar(10),@date1,102) + ' 截止日期：' + CONVERT(varchar(10),@date2,102)PRINT '-----------------------------------------------------------'PRINT '存入笔数：' + CAST(@CNT1 AS varchar(20)) + ' 存入金额：' + CAST(@Total1 AS varchar(20))PRINT '支取笔数：' + CAST(@CNT2 AS varchar(20)) + ' 支取金额：' + CAST(@Total2 AS varchar(20))PRINT '-----------------------------------------------------------'PRINT '发生笔数：' + CAST(@CNT1+@CNT2 AS varchar(20)) + ' 结余金额：' + CAST(@Total1-@Total2 AS varchar(20))GO --用例7.利用事物实现较复杂的数据更新 --创建事务if exists(select * from sysobjects where name='proc_transfer') drop procedure proc_transfer gocreate procedure proc_transfer@card1 varchar(20),@card2 varchar(20),@outmoney moneyasbegin transaction print '开始转帐，请稍等..........'exec proc_takeMoney @card=@card1,@transMoney=@outmoney,@transType='支取',@password='888888'if(@@error<>0) begin print '交易失败！' rollback transaction endelse begin print '交易正在进行，请稍等.........' update cardInfo set balance=balance+@outmoney where cardID=@card2 print '交易成功！交易金额：'+convert(varchar(20),@outmoney) select 卡号=@card2,余额=balance from cardInfo where cardID=@card2 print '转帐成功！' insert into tradeInfo(cardID,tradeType,tradeMoney) values(@card2,'存入',@outmoney) commit transaction end go exec proc_transfer @card1='1010 3576 1212 1134',@card2='1010 3576 1234 5678',@outmoney=200 select 卡号=cardID,货币种类=curID,存款类型=savingID,开户日期=openDate, 余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardInfo select 交易日期=tradeDate,交易类型=tradeType,卡号=cardID,交易金额=tradeMoney,备注=remark from tradeInfo
0 0
