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 #accountselect * 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 datetimeset @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
from 账户表 as a join 交易记录 as b
on a.帐号 = b. 帐号 where a.身份证号 = @身份证号
and
日期 between convert(date,getdate()) and getdate()
(
帐号 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 #accountselect * 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 datetimeset @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