SQL 语句

azsoft 2014-12-09 01:11:07
declare @table table (A varchar(10),B int,C int)
insert into @table values ('A',100,1)
insert into @table values ('A',100,2)
insert into @table values ('A',100,3)
insert into @table values ('A',100,4)
insert into @table values ('A',100,5)
insert into @table values ('A',100,6)
insert into @table values ('A',100,7)
insert into @table values ('A',100,8)

declare @table1 table (A varchar(10),B int)
insert into @table1 values ('A',625)

-- 结果(结合@table1)
select * from @table
A B C
--------------------------------------
A 0 1
A 0 2
A 0 3
A 0 4
A 0 5
A 75 6
A 100 7
A 100 8
...全文
166 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
azsoft 2014-12-09
  • 打赏
  • 举报
回复
非常感谢,学到了,CTE确实好用。但是,实际业务要更复杂些,我在想用函数来实现,会不会更好些。结贴了
还在加载中灬 2014-12-09
  • 打赏
  • 举报
回复
declare @table table (A varchar(10),B int,C INT,D FLOAT)
insert into @table values ('A',100,1,1.02)
insert into @table values ('A',100,2,1.12)
insert into @table values ('A',100,3,1.33)
insert into @table values ('A',100,4,1.14)
insert into @table values ('A',100,5,1.35)

-- @table1 也需要个顺序列,没有就ROW_NUMBER加一个
declare @table1 table (A varchar(10),B INT,C INT)
insert into @table1 values ('A',125,1)
insert into @table1 values ('A',10,2)
insert into @table1 values ('A',20,3)
insert into @table1 values ('A',30,4)

DECLARE @RESULT TABLE(A varchar(10),B INT,C1 INT,D FLOAT,T1_ROW INT,B2 INT,C2 INT)
;WITH CTE AS(
	SELECT
		T1.A
		,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B
		,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END B2
		,T1.C
		,T2.B-T1.B DIFF
		,T1.D D
		,T1.C C1
		,T2.C C2
		,1 T1_ROW
		,1 T2_ROW
	FROM @table T1
		JOIN @table1 T2 ON T1.A=T2.A
	WHERE T1.C=1 AND T2.C=1
	UNION ALL
	SELECT
		T1.A
		,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END
		,CASE WHEN T1.B>T2.DIFF THEN T2.DIFF ELSE T1.B END
		,T1.C
		,T2.DIFF-T1.B 
		,T1.D
		,T1.C
		,T2.C2
		,1
		,T2.T2_ROW+1
	FROM @table T1
		JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C1+1 AND T2.DIFF>0
	UNION ALL
	SELECT
		T2.A
		,T2.B-T1.B
		,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END
		,T2.C
		,T2.DIFF+T1.B
		,T2.D
		,T2.C1
		,T1.C
		,T2.T1_ROW+1
		,1
	FROM @table1 T1
		JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C2+1 AND T2.DIFF<0
	UNION ALL
	SELECT
		T1.A
		,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END
		,CASE WHEN T1.B>T2.B THEN T2.B ELSE T1.B END
		,T1.C
		,T2.B-T1.B
		,T1.D
		,T1.C
		,T2.C
		,1
		,1
	FROM CTE T0
		JOIN @table T1 ON T0.C1+1=T1.C
		JOIN @table1 T2 ON T0.C2+1=T2.C
	WHERE T0.DIFF=0
)
--SELECT * FROM CTE
INSERT INTO @RESULT
SELECT A,B,C1,D,T1_ROW,B2,C2 FROM CTE

-- @table 结果
SELECT T1.A,ISNULL(T2.B,T1.B)B,T1.C,T1.D FROM @table T1
	LEFT JOIN @RESULT T2 ON T2.T1_ROW=1 AND T1.C=T2.C1
-- @table1 结果
SELECT T1.A,T2.B2 B,ISNULL(T2.D,0)D FROM @table1 T1
	LEFT JOIN @RESULT T2 ON T1.C=T2.C2
--如果你需要根据A分组,则序号还要另外处理
azsoft 2014-12-09
  • 打赏
  • 举报
回复
declare @table table (A varchar(10),B int,C int,D float) insert into @table values ('A',100,1,1.02) insert into @table values ('A',100,2,1.12) insert into @table values ('A',100,3,1.33) insert into @table values ('A',100,4,1.14) insert into @table values ('A',100,5,1.35) declare @table1 table (A varchar(10),B int) insert into @table1 values ('A',125) insert into @table1 values ('A',10) insert into @table1 values ('A',20) insert into @table1 values ('A',30) declare @table2 table (A varchar(10),B int,C float) ;WITH CTE AS( SELECT TOP 1 T1.A ,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B ,T1.C ,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF FROM @table T1 JOIN @table1 T2 ON T1.A=T2.A ORDER BY T1.C UNION ALL SELECT T1.A ,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END ,T1.C ,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF FROM @table T1 JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1 ) SELECT A,B,C FROM CTE --- 好像结果不对 A 0 1 A 75 2 A 100 3 A 100 4 A 100 5
azsoft 2014-12-09
  • 打赏
  • 举报
回复
这样 declare @table table (A varchar(10),B int,C int,D float) insert into @table values ('A',100,1,1.02) insert into @table values ('A',100,2,1.12) insert into @table values ('A',100,3,1.33) insert into @table values ('A',100,4,1.14) insert into @table values ('A',100,5,1.35) declare @table1 table (A varchar(10),B int) insert into @table1 values ('A',125) insert into @table1 values ('A',10) insert into @table1 values ('A',20) insert into @table1 values ('A',30) declare @table2 table (A varchar(10),B int,C float) -- @table 结果 --------------------------------------- A 0 1 1.02 A 15 2 1.12 A 100 3 1.33 A 100 4 1.14 A 100 5 1.35 -- @table2 结果 --------------------------------------- A 100 1.02 A 25 1.12 A 10 1.12 A 20 1.12 A 30 1.12
还在加载中灬 2014-12-09
  • 打赏
  • 举报
回复
没明白,把结果写入到一个表中?
azsoft 2014-12-09
  • 打赏
  • 举报
回复
递归的方法好,以前没有用过,不过,现在如果递归的同时,把轮询的值写入到另外一个表中,该如何处理呢?谢谢
还在加载中灬 2014-12-09
  • 打赏
  • 举报
回复
楼主你算错了
;WITH CTE AS(
	SELECT TOP 1 T1.A
		,CASE WHEN T1.B>T2.B THEN T1.B-T2.B ELSE 0 END B
		,T1.C
		,CASE WHEN T1.B>=T2.B THEN 0 ELSE T2.B-T1.B END DIFF
	FROM @table T1
		JOIN @table1 T2 ON T1.A=T2.A
	ORDER BY T1.C
	UNION ALL
	SELECT
		T1.A
		,CASE WHEN T1.B>T2.DIFF THEN T1.B-T2.DIFF ELSE 0 END
		,T1.C
		,CASE WHEN T1.B>=T2.DIFF THEN 0 ELSE T2.DIFF-T1.B END DIFF
	FROM @table T1
		JOIN CTE T2 ON T1.A=T2.A AND T1.C=T2.C+1
)
SELECT A,B,C FROM CTE

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧