求一比较困难的sql语句 急!!!!!!!!!!

Question

select 日期,
       人员编号,
       剩余钱数=总钱数-(select sum(已用钱数) from tablename where 人员编号=a.人员编号)
from tablename a

Answer 1

select taba.日期 ,taba,人员编号,(taba.总钱数-tabb.已用钱数 ) as 剩余钱数
from taba
left join 
(select 日期,人员编号 ,sum(已用钱数)  as 已用钱数 
from taba
group by 日期,人员编号 ) tabb
on taba.日期=tabb.日期 and taba.人员编号=tabb.人员编号

Answer 2

select AA.日期,AA.人员编号,AA.总钱数-BB.已用钱数 as 剩余钱数  from table1 AA join
(select A.人员编号,sum(A.已用钱数) as 已用钱数  from  table1 A group by A.人员编号) BB
 on AA.人员编号=BB.人员编号 order by AA.人员编号

Answer 3

创建函数
CREATE FUNCTION [dbo].[getmoney] (@id varchar(32))
RETURNS varchar(2000) AS  
BEGIN 
declare @money varchar(2000)
set @money = ''
select @money = convert(varchar(32),总钱数) from yourtable where 人员编号=@id
select @money = @money + '-' + convert(varchar(32),已用钱数) from yourtable where 人员编号=@id
return @money
END 
查询:
select 日期,人员编号,dbo.getmoney(人员编号) as 剩余钱数 from yourtable

Answer 4

--编一个自定义函数:
CREATE function dbo.usd_Fun(@人员编号 varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
declare @i int
declare @use int
select top 1 @re=总钱数 from table1 where 人员编号=@人员编号
select top 1 @i=[id],@use=已用钱数 from table1 where 人员编号=@人员编号
set @re=@re+'-'+cast(@use as varchar(8000)) set @i=@i+1
select @use=已用钱数 from table1 where 人员编号=@人员编号 and [id]=@i
while @@rowcount>0
begin
set @re=@re+'-'+cast(@use as varchar(8000))
set @i=@i+1
select @use=已用钱数 from table1 where 人员编号=@人员编号 and [id]=@i
end
return @re
end--调用时的示例代码：
declare @tb1 table(日期 datetime,人员编号 varchar(20),总钱数 int,已用钱数 int)
insert into @tb1 
select '2004/01/11',                '001',               10000,                       50 union all
select '2004/01/12',                '001',               10000,                      500 union all
select '2004/01/11',                '002',               10000,                       50 union all
select '2004/01/12',                '002',               10000,                     600  union all
select '2004/01/13',                '001',               10000,                     750  union all
select '2004/01/14',                '002',               10000,                     850
select * into table1  from @tb1 order by 人员编号,日期
alter table table1 add [id] int identity
select 日期,人员编号,剩余钱数=dbo.usd_Fun(人员编号) from table1 drop table table1/*
日期                                                     人员编号                 剩余钱数                                                                                                                                                                                                                                                             
------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2004-01-11 00:00:00.000                                001                  10000-50-500-750
2004-01-12 00:00:00.000                                001                  10000-50-500-750
2004-01-13 00:00:00.000                                001                  10000-50-500-750
2004-01-11 00:00:00.000                                002                  10000-50-600-850
2004-01-12 00:00:00.000                                002                  10000-50-600-850
2004-01-14 00:00:00.000                                002                  10000-50-600-850（所影响的行数为 6 行）
*/