sql语句，列A的内容等于上行列B的内容，怎么写
sql语句，列A的内容等于上行列B的内容，怎么写
一个表里面结构
工序 数量
1 10
1 1
2 2
2 3
3 2
要得到一个结果
工序 计划数量 实际数量
1 11（首行最大值） 11
2 11 5
3 5 2
工序2的计划数量等于实际数量
工序3的计划数量等于2的实际数量
工序的实际数量 是工序数量的累加
首行的计划数量等于实际数量
[解决办法]
CREATE TABLE BOM(PID INT,ID INT)INSERT INTO BOM SELECT 801,101INSERT INTO BOM SELECT 801,102INSERT INTO BOM SELECT 801,103INSERT INTO BOM SELECT 801,601INSERT INTO BOM SELECT 601,101INSERT INTO BOM SELECT 601,105INSERT INTO BOM SELECT 601,501INSERT INTO BOM SELECT 501,106INSERT INTO BOM SELECT 501,121GOCREATE FUNCTION F_GETROOT(@PID INT)RETURNS INTASBEGIN DECLARE @ID INT WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID) BEGIN SET @ID=@PID SELECT @PID=PID FROM BOM WHERE ID=@ID END RETURN @PIDENDGOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOMGO/*PID ID----------- ----------- 801 101801 102801 103801 601801 101801 105801 501801 106801 121*/DROP FUNCTION F_GETROOTDROP TABLE BOMGO--生成测试数据create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute varchar(20))insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'insert into BOM_1 select 3 ,'A2','A3',3,'生产'insert into BOM_1 select 4 ,'A2','A4',2,'采购'insert into BOM_1 select 5 ,'A3','A5',2,'采购'insert into BOM_1 select 6 ,'A3','A6',1,'采购'insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'insert into BOM_1 select 9 ,'B2','B3',3,'生产'insert into BOM_1 select 10,'B2','B4',2,'采购'insert into BOM_1 select 11,'B3','B5',2,'采购'insert into BOM_1 select 12,'B3','B6',2,'采购'go --创建用户定义函数，用于取每个父节点下子节点的采购配置信息create function f_stock(@bom_head varchar(20))returns @t table(bom varchar(20),number int)asbegin declare @level int declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int) set @level=1 if exists(select 1 from BOM_1 where bom_head=@bom_head) insert into @a select bom_child,number,products_attribute,@level from BOM_1 where bom_head=@bom_head while exists(select 1 from @a where [level]=@level and products_attribute='生产') begin set @level=@level+1 insert into @a(bom,number,products_attribute,[level]) select a.bom_child,a.number,a.products_attribute,@level from BOM_1 a,@a b where a.bom_head=b.bom and b.[level]=@level-1 end insert into @t(bom,number) select bom,number from @a where products_attribute='采购' returnendgo--执行调用，取父节点'A'一个标准配置分解的采购信息及数量select * from dbo.f_stock('A')--生成测试数据create table BOM(ID INT,PID INT,MSG VARCHAR(1000))insert into BOM select 1,0,NULLinsert into BOM select 2,1,NULLinsert into BOM select 3,1,NULLinsert into BOM select 4,2,NULLinsert into BOM select 5,3,NULLinsert into BOM select 6,5,NULLinsert into BOM select 7,6,NULLgo--创建用户定义函数用于取每个父节点下子节点的采购配置信息create function f_getChild(@ID VARCHAR(10))returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)asbegin declare @i int set @i = 1 insert into @t select ID,PID,@i from BOM where PID = @ID while @@rowcount<>0 begin set @i = @i + 1 insert into @t select a.ID,a.PID,@i from BOM a,@t b where a.PID=b.ID and b.Level = @i-1 end returnendgo--执行查询select ID from dbo.f_getChild(3)go--输出结果/*ID----567*/--删除测试数据drop function f_getChilddrop table BOM创建用户定义函数,每个子节点de父节点的信息--生成测试数据create table BOM(ID int,parentID int,sClassName varchar(10))insert into BOM values(1,0,'1111' )insert into BOM values(2,1,'1111_1' )insert into BOM values(3,2,'1111-1-1' )insert into BOM values(4,3,'1111-1-1-1') insert into BOM values(5,1,'1111-2' )go--创建用户定义函数,每个子节点de父节点的信息create function f_getParent(@ID int)returns varchar(40)asbegin declare @ret varchar(40) while exists(select 1 from BOM where ID=@ID and parentID<>0) begin select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'') from BOM a,BOM b where a.ID=@ID and b.ID=a.parentID end set @ret=stuff(@ret,1,1,'') return @retendgo--执行查询select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOMgo--输出结果/*ID parentID ----------- ---------------------------------------- 1 2 13 1,24 1,2,35 1 */--删除测试数据drop function f_getParentdrop table BOMgo--参考资料
[解决办法]
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( col1 INT, col2 INT)GOINSERT INTO tbaSELECT 1, 10 UNIONSELECT 1, 1 UNIONSELECT 2, 2 UNIONSELECT 2, 3 UNIONSELECT 3, 2GOWITH t AS(SELECT col1,SUM(col2) AS totalFROM tbaGROUP BY col1)SELECT A.col1 AS 工序, CASE WHEN B.total IS NULL THEN A.total ELSE B.total END AS 计划数量, A.total AS 实际数量FROM t AS A LEFT OUTER JOIN t AS B ON A.col1 = B.col1 + 1工序 计划数量 实际数量1 11 112 11 53 5 2