自己做取款机模拟系统每天限制取款上限问题

[ljm19891118]

账户表(帐号,余额,身份证号)
交易记录(帐号,日期,金额)


上面是2张表
已知身份证号
1、怎么设定一个用户一天最多取款5000啊
2、列出改身份证号在2010.1.1到2010.2.1间的交易记录

谢谢了

[ljm19891118]

谢谢了 但是问什么那里要加上
N'取款'
类型这是要加 但是那N是什么意思？
基本的问题都解决了

[王向飞]

http://support.microsoft.com/kb/239530/zh-cn

[ljm19891118]

上课去了 中午回来

[王向飞]

select sum(b.金额) --只需判断此合计是否大于5000即可
from 账户表 as a join 交易记录 as b
on a.帐号 = b. 帐号 where a.身份证号 = @身份证号
and 
日期 between  convert(date,getdate())   and getdate()

[「已注销」]

CREATE TABLE #account
(
	帐号 varchar(20),
	余额 decimal(10,2),
	身份证号 varchar(18)
)

CREATE TABLE #deal
(	
	帐号 varchar(20),
	日期 datetime,
	金额 decimal(10,2),
	交易类型 nvarchar(10) -----添加交易类型
)

insert into #account select '11111',2000,'123456789'
insert into #account select '22222',9000,'000000000'
insert into #account select '33333',3700,'111111111'


insert into #deal select '11111','2010-09-26',300,N'取款'
insert into #deal select '11111','2010-09-12',4000,N'取款'
insert into #deal select '11111','2010-09-06',1600,N'取款'
insert into #deal select '11111','2010-09-06',3200,N'存款'
insert into #deal select '22222','2010-09-06',700,N'取款'
insert into #deal select '22222','2010-09-09',3600,N'取款'
insert into #deal select '22222','2010-09-21',1800,N'取款'
insert into #deal select '33333','2010-09-26',2600,N'取款'
insert into #deal select '33333','2010-09-12',900,N'取款'
insert into #deal select '33333','2010-09-06',3100,N'取款'

select * from #account

select * from #deal

-----1、怎么设定一个用户一天最多取款5000啊
SELECT [帐号],[日期],CASE WHEN SUM([金额])>5000 THEN N'超限' 
					 ELSE SUM([金额]) END TOTAL
FROM #DEAL
WHERE [交易类型]=N'取款'
GROUP BY [帐号],[日期]
ORDER BY [帐号]

------2、列出改身份证号在2010.1.1到2010.2.1间的交易记录
declare @beginDate datetime
declare @endDate   datetime

set @beginDate='2010-09-01'
set @endDate='2010-11-01'

SELECT A.[身份证号],D.帐号,D.日期,D.金额
FROM #ACCOUNT A,#DEAL D
WHERE A.帐号=D.帐号
AND D.日期 BETWEEN @beginDate AND @endDate