你需要的是这样的ma?Update [CUT] set QTY=SQTY-CQTY
解决方案 »
- SQL多表嵌套
- 依旧是排名问题
- 关于sql server2000 的时间函数处理
- Select语句为变量赋值时,可以同时查询数据吗?
- 在企业管理器中如何查看数据库和日志分别点用的空间量?
- ??大家帮忙看看这个SQL怎么写?
- 急:两表连接显示数据 在线等
- 请高手解决我的WinServer2003的remote Procedures Call(RPC)启动出错的问题
- 也是关于odbc的
- 初级问题:在mssql中怎样把table1的aa字段的记录复制到table2的aa字段里,这两个表都有字段name(name相同时复制)?
- SQL SSMS(管理工具) 如何关闭自动连接
- 创建分发数据库时我指定了数据文件的大小怎么生成的文件都是默认2.5MB!
如果是想要查询
SELECT CID,CTYPE,CQTY,SQTY-CQTY QTY,SQTY,SNO FROM [CUT]
第二个方法:直接update CUT set QTY=SQTY-CQTY
SELECT CID,CTYPE,CQTY,SQTY-CQTY QTY,SQTY-CQTY SQTY,SNO FROM [CUT]
那QTY存哪个值?
CID varchar(10) not null,
CTYPE varchar(10) not null,
CQTY int not null,
QTY as SQTY-CQTY PERSISTED,
SQTY int not null,
SNO varchar(10) not null
)insert into CUT
select 'F21','0010',8,10,'T1099' union all
select 'F22','0010',3,10,'T1099' union all
select 'F23','0010',2,10,'T1099' select * from CUT --当你update CQTY或者SQTY列时,QTY会自动更新
update CUT set SQTY=100 where CQTY=3 select * from CUT 不是的,就像取钱一样,机子里一共才1000,结果你取500,那只剩下500了,而你的方法,是还剩下1000
CID varchar(10) not null,
CTYPE varchar(10) not null,
CQTY int not null,
QTY as SQTY-CQTY PERSISTED,
SQTY int not null,
SNO varchar(10) not null
)insert into CUT
select 'F21','0010',8,10,'T1099' union all
select 'F22','0010',3,10,'T1099' union all
select 'F23','0010',2,10,'T1099' select * from CUT --当你update CQTY或者SQTY列时,QTY会自动更新
update CUT set SQTY=100 where CQTY=3 select * from CUT
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-29 14:53:43
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[CUT]
if object_id('[CUT]') is not null drop table [CUT]
go
create table [CUT]([CID] varchar(3),[CTYPE] varchar(4),[CQTY] int,[QTY] sql_variant,[SQTY] int,[SNO] varchar(5))
insert [CUT]
select 'F21','0010',8,null,10,'T1099' union all
select 'F22','0010',3,null,10,'T1099' union all
select 'F23','0010',2,null,10,'T1099' union all
select 'F31','0010',6,null,6,'T8899' union all
select 'F32','0010',2,null,6,'T8899'
--------------开始查询--------------------------
;WITH cte AS (
select cid,ctype,cqty,CASE WHEN sqty>=cqty THEN cqty ELSE sqty END AS qty ,sqty,sno ,ROW_NUMBER()OVER(PARTITION BY sno ORDER BY cqty DESC) id
from [CUT])
,cte1 AS
(
SELECT cid,ctype,cqty, qty ,sqty,sno,id
FROM cte
WHERE id=1
UNION ALL
SELECT a.cid,a.ctype,a.cqty,CASE WHEN b.sqty-b.cqty>=0 THEN b.sqty-b.cqty WHEN b.sqty-b.cqty<0 THEN 0 ELSE b.cqty END AS qty,CASE WHEN b.sqty-b.cqty>=0 THEN b.sqty-b.cqty ELSE b.cqty-b.qty END as sqty,a.sno,a.id
FROM cte a INNER JOIN cte1 b ON a.sno=b.sno AND a.id=b.id+1
)
SELECT cid, ctype, cqty , qty , sqty , sno
FROM cte1
ORDER BY cid
----------------结果----------------------------
/*
cid ctype cqty qty sqty sno
---- ----- ----------- ----------- ----------- -----
F21 0010 8 8 10 T1099
F22 0010 3 2 2 T1099
F23 0010 2 0 1 T1099
F31 0010 6 6 6 T8899
F32 0010 2 0 0 T8899
*/
(
SELECT 'F21' AS CID,'0010' AS CTYPE,8 AS CQTY,NULL AS QTY,10 AS SQTY,'T1099' AS SNO
UNION ALL
SELECT 'F22','0010',3,NULL,10,'T1099'
UNION ALL
SELECT 'F23','0010',2,NULL,10,'T1099'
UNION ALL
SELECT 'F31','0010',6,NULL,6,'T8899'
UNION ALL
SELECT 'F32','0010',2,NULL,6,'T8899'
)
SELECT cte.* INTO [CUT] FROM cte
SELECT *
,CASE WHEN sqty>=c1.t_cqty THEN cqty
ELSE
CASE WHEN ABS(sqty-c1.t_cqty) >cqty THEN 0 ELSE cqty+sqty-c1.t_cqty END
END AS Qty
FROM CUT AS c CROSS APPLY
(SELECT ctype,sno,sum(cqty) AS t_cqty FROM CUT WHERE cid<=c.cid GROUP BY ctype,sno)
AS c1 WHERE c1.ctype=c.ctype AND c1.sno=c.sno
结果
CID CTYPE CQTY QTY SQTY SNO ctype sno t_cqty Qty
F21 0010 8 NULL 10 T1099 0010 T1099 8 8
F22 0010 3 NULL 10 T1099 0010 T1099 11 2
F23 0010 2 NULL 10 T1099 0010 T1099 13 0
F31 0010 6 NULL 6 T8899 0010 T8899 6 6
F32 0010 2 NULL 6 T8899 0010 T8899 8 0
去数据时,同时将到当前行为止为止之前供需数据的合计SELECT * FROM CUT AS c
CROSS APPLY
(SELECT ctype,sno,sum(cqty) AS t_cqty FROM CUT WHERE cid<=c.cid GROUP BY ctype,sno)
AS c1 WHERE c1.ctype=c.ctype AND c1.sno=c.sno
如果总数据大于等于计算出的数量,也就是当前行也够,所以qty=cqty
如果小于总数量,再看是不是在更早一行就分配没了,如果没分配没则剩下作为当前行的qty,否则就是0