一个蛋疼的问题

Question

RT 
环境如下create table Credit_card
(UserID int ,
Username varchar(20),
Bank varchar(20),
Monetary money,
Post
insert into Credit_card
select '1' ,'万万' ,'中国银行' ,'300' ,GETDATE()  
union all 
select '2','张三','招商银行','400',GETDATE()
union all 
select '3','万万','中国银行','500',GETDATE()
union all
select '4','李四','农业银行','600',GETDATE()
union all
select '5','马六','招商银行','700',GETDATE()
union all
select '6','大苏','海峡银行','800',GETDATE()
union all
select '7','大三','招商银行','900',GETDATE()
union all
select '8','耳朵','招商银行','700',GETDATE()
union all
select '9','发给','海峡银行','800',GETDATE()
union all
select '10','法定','招商银行','900',GETDATE()
union all
select '11','而太热','招商银行','900',GETDATE()要的结果：UserID      Username             Bank                 Monetary              Post_date               Balance（剩余公款）               Cost（用了多少）
----------- -------------------- -------------------- --------------------- ----------------------- --------------------- ---------------------
1           万万                   中国银行                 300.00                2011-10-07 09:34:23.343 2700.00               300.00
2           李四                   中国银行                 400.00                2011-09-20 00:00:00.000 2300.00               400.00
3           马六                   中信银行                 500.00                2011-09-22 00:00:00.000 1800.00               500.00
4           大苏                   工商银行                 600.00                2011-09-23 00:00:00.000 1200.00               600.00
5           吉萨                   海峡银行                 700.00                2011-09-24 00:00:00.000 500.00                700.00
6           奈斯                   农业银行                 800.00                2011-09-25 00:00:00.000 -300.00               500.00
6           奈斯                   农业银行                 800.00                2011-09-25 00:00:00.000 700.00                300.00
7           张三                   招商银行                 900.00                2011-09-08 11:48:25.923 -200.00              700
7           张三                   招商银行                 900.00                2011-09-08 11:48:25.923  1800              200
刚开始默认账户里有3000公款  减到第六条时3000减完了 在拿1000 进去  第七条 1000 不够 在拿2000进去 依次下去 
尽量只用select语句写

Answer 1

--库存先进先出简单例子：create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j 
    else 
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
    end
    from t a where name=@name and j!=c 
end
else
    raiserror('库存不足',16,1)    
    return 
go
--测试：exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp

Answer 2

不知道 师傅让这么写得   还要用select语句  想不明白这到底要这么搞   求助

Answer 3

恩 师傅说有这个表    这个用select语句这么写

Answer 4

SELECT *, ROW_NUMBER() OVER (ORDER BY GETDATE()) AS SEQ 
INTO #
FROM Credit_cardDECLARE @TB table 
(UserID int ,
Username varchar(20),
Bank varchar(20),
Monetary money,
Post DATETIME,
SEQ INT,
Balance money,
Cost money
)DECLARE @TotalCost money, @AMT MONEY
DECLARE @N INT
DECLARE @TEMP TABLE(CURRCOST MONEY)SET @TotalCost=(SELECT SUM(Monetary) FROM #)
SET @AMT=3000
SET @N=1WHILE (@TotalCost>ISNULL((SELECT SUM(Cost) FROM @TB),0))
BEGIN
INSERT @TB
OUTPUT INSERTED.Cost INTO @TEMP
SELECT TOP 1 #.*,@AMT-(#.Monetary-ISNULL(T.Cost,0)) AS BAL,
CASE WHEN #.Monetary-ISNULL(T.Cost,0)>@AMT THEN @AMT ELSE #.Monetary-ISNULL(T.Cost,0) END Cost
FROM # LEFT JOIN (SELECT SEQ, SUM(Cost) Cost FROM @TB GROUP BY SEQ) AS T
ON #.SEQ=T.SEQ
WHERE #.Monetary-ISNULL(T.Cost,0)>0
ORDER BY #.SEQ SET @AMT=(SELECT @AMT-CURRCOST FROM @TEMP)
DELETE @TEMP IF @AMT=0
BEGIN
SET @AMT=@N*1000
SET @N=@N+1
END

ENDSELECT UserID,Username,Bank,Monetary,Post,Balance,Cost
FROM @TBDROP TABLE #
/*
UserID      Username             Bank                 Monetary              Post                    Balance               Cost
----------- -------------------- -------------------- --------------------- ----------------------- --------------------- ---------------------
1           万万                   中国银行                 300.00                2011-10-11 16:39:06.007 2700.00               300.00
2           张三                   招商银行                 400.00                2011-10-11 16:39:06.007 2300.00               400.00
3           万万                   中国银行                 500.00                2011-10-11 16:39:06.007 1800.00               500.00
4           李四                   农业银行                 600.00                2011-10-11 16:39:06.007 1200.00               600.00
5           马六                   招商银行                 700.00                2011-10-11 16:39:06.007 500.00                700.00
6           大苏                   海峡银行                 800.00                2011-10-11 16:39:06.007 -300.00               500.00
6           大苏                   海峡银行                 800.00                2011-10-11 16:39:06.007 700.00                300.00
7           大三                   招商银行                 900.00                2011-10-11 16:39:06.007 -200.00               700.00
7           大三                   招商银行                 900.00                2011-10-11 16:39:06.007 1800.00               200.00
8           耳朵                   招商银行                 700.00                2011-10-11 16:39:06.007 1100.00               700.00
9           发给                   海峡银行                 800.00                2011-10-11 16:39:06.007 300.00                800.00
10          法定                   招商银行                 900.00                2011-10-11 16:39:06.007 -600.00               300.00
10          法定                   招商银行                 900.00                2011-10-11 16:39:06.007 2400.00               600.00
11          而太热                  招商银行                 900.00                2011-10-11 16:39:06.007 1500.00               900.00
*/

Answer 5

;with cte as(
select *,(select SUM(Monetary) from CreditCard_Cost where userid<=a.Userid) as Balance from CreditCard_Cost a )
, cte2 as (
select case when  balance<=3000 then (select Income from CreditCard_Income2 where id=1)  else (select sum(Income) from CreditCard_Income2 ) end
as Income,ROW_NUMBER() over(order by getdate()) as rowid from cte)
select a.UserID,A.Username,A.Bank,A.Monetary,A.Post_date,b.Income-a.Balance cost,a.Monetary from cte a, cte2 b where a.userid=b.rowid