请客位朋友帮忙看看SQL这样的查询结果？

想到才能做到 2014-10-20 01:24:08
我有一个表T1，结构与数量如下：
日期 入库数量 出库数量
2014-01-02 500 0
2014-01-02 300
2014-01-02 500 0
2014-01-02 500
2014-01-02 800 0
2014-01-02 250 245


我想要的结果是增加多一列库存余额，该列计算公式：上一行余额+当前行入库-当前行出库
效果如下：
日期 入库数量 出库数量 库存余额
2014-01-02 500 0 500
2014-01-02 300 200
2014-01-02 500 0 700
2014-01-02 500 200
2014-01-02 800 0 1000
2014-01-02 250 245 1005


...全文
360 35 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
35 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
从游标的10000行数据秒出上看，你完全可以放在程序上遍历一次，又不复杂~~ 另外，如老虎所说，递归时，你可以撇开其它字段，等递归出结果再表连接~
Tiger_Zhao 2014-10-20
  • 打赏
  • 举报
 回复
让递归CTE只保留 rn 和 库存余额，要出结果时再和其他字段拼起来。
我这里做模拟递归1000次结果是秒出的。
WITH test AS (

    SELECT 1 RN, 

           Convert(decimal(18,2),RAND()*1000) AS InStore,

           Convert(decimal(18,2),RAND()*1000) AS OutStore

UNION ALL

    SELECT RN+1,

           Convert(decimal(18,2),RAND(RN*OutStore)*1000) AS InStore,

           Convert(decimal(18,2),RAND(RN+InStore)*1000) AS OutStore

      FROM test

     WHERE RN < 1000

)

,CTE AS (

    SELECT RN,

           Convert(decimal(18,2), InStore - OutStore) AS Stock

      FROM test

     WHERE RN = 1

UNION ALL

    SELECT t.RN,

           Convert(decimal(18,2), c.Stock + t.InStore - t.OutStore) AS Stock

      FROM CTE c

      JOIN test t

        ON t.RN = c.RN + 1

)

SELECT t.RN,

       t.InStore,

       t.OutStore,

       c.Stock

      FROM CTE c

      JOIN test t

        ON t.RN = c.RN

option (maxrecursion 0)
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('#MYTEST20141020')IS NOT NULL
	DROP TABLE #MYTEST20141020
CREATE TABLE #MYTEST20141020(
	A INT,
	B INT
)
GO
DECLARE @TEMP NUMERIC(18,2)
SET @TEMP=1000
DECLARE @INDEX INT
SET @INDEX=1
WHILE @INDEX<=1000
BEGIN
	INSERT INTO #MYTEST20141020
	SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	UNION ALL SELECT CAST(RAND()*@TEMP AS NUMERIC(18,2)),CAST(RAND()*@TEMP AS NUMERIC(18,2))
	SET @INDEX=@INDEX+1
END
GO
--以上测试数据
--以下游标
DECLARE @STARTTIME DATETIME
SET @STARTTIME=GETDATE()
DECLARE MYCURSOR CURSOR SCROLL FOR
SELECT * FROM #MYTEST20141020

OPEN MYCURSOR
DECLARE @T AS TABLE(A NUMERIC(18,2), B NUMERIC(18,2),ER NUMERIC(18,2))
DECLARE @A NUMERIC(18,2),@B NUMERIC(18,2),@ER NUMERIC(18,2)
SET @ER=0

FETCH NEXT FROM MYCURSOR INTO @A,@B
WHILE @@FETCH_STATUS=0
BEGIN
	SET @ER=@ER+@A-@B
	INSERT INTO @T
	SELECT @A,@B,@ER
	FETCH NEXT FROM MYCURSOR INTO @A,@B
END

CLOSE MYCURSOR
DEALLOCATE MYCURSOR
SELECT * FROM @T
SELECT DATEDIFF(MS,@STARTTIME,GETDATE())[毫秒]
GO
DROP TABLE #MYTEST20141020
不知道数据模仿得对不对，游标神速~~
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
我在做测试数据，这种有顺序的，我想测试下游标~
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 27 楼 ky_min 的回复:
[quote=引用 26 楼 F172515968 的回复:] [quote=引用 21 楼 ky_min 的回复:] 每次都去动态执行查询 大数据的递归运算肯定慢 可不可以考虑做成静态的，一次执行，多次受益，体现那神一样的速度~~
做静态估计也不现实，因为返回的记录需要动态根据不同条件返回不同结果；[/quote] 应该到最后也是由程序来显示吧 可以在显示时加个中间变量，这样就算是一两千行的数量也只遍历一次 这样应该会提高很高的速度[/quote] 如果SQL语句没有比较好的办法的话，我感觉也只能在程序设计中加入一个列，然后对该列进行循环赋值了。
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
其实现在

引用 28 楼 reenjie 的回复:
[quote=引用 24 楼 F172515968 的回复:]
[quote=引用 6 楼 reenjie 的回复:]
[quote=引用 4 楼 F172515968 的回复:]
[quote=引用 3 楼 reenjie 的回复:]

不知道入庫數量怎麼有為空?不是為0嗎?
假設入庫數量的字段類型是數字


with cte as 

(

select *,row_number() over(order by getdate() asc) as rId from T1

)

select a.[日期],a.[入库数量],a.[出库数量],(select sum(入库数量) from cte where rId<=a.rId)-a.[出库数量]  from cte as a


对，是为0的，数据是我用文本手动写的[/quote]
那就行,上面的sql可以得到你想要的結果嗎?[/quote]

这个方法没有报错，不过计算结果有一部份不对；另外效率也同样很低。返回上面那个1368行的记录所用时间也是两分四十多秒。不知还有没有什么更好的方法？[/quote]
先執行你得到源數據的查詢，看耗時多長。
可能那一部分應該是你要優化的部分。[/quote]

返回源数据的时间不到一秒，如下图：
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 20 楼 Tiger_Zhao 的回复:
你中间处理的数据远远超过一千多条！！！
CTE 前面一个表达式中，将 where 条件应用到每个 union all 分支的 select 语句中，这样才是将需要的数据先筛选再串联。
你贴图中的做法是先串联全部数据再做筛选，效率差好多！

条件放在第一个分支的话效率没多少变化的。
reenjie 2014-10-20
  • 打赏
  • 举报
 回复
引用 24 楼 F172515968 的回复:
[quote=引用 6 楼 reenjie 的回复:] [quote=引用 4 楼 F172515968 的回复:] [quote=引用 3 楼 reenjie 的回复:] 不知道入庫數量怎麼有為空?不是為0嗎? 假設入庫數量的字段類型是數字 

with cte as 
(
select *,row_number() over(order by getdate() asc) as rId from T1
)
select a.[日期],a.[入库数量],a.[出库数量],(select sum(入库数量) from cte where rId<=a.rId)-a.[出库数量]  from cte as a
对，是为0的，数据是我用文本手动写的[/quote] 那就行,上面的sql可以得到你想要的結果嗎?[/quote] 这个方法没有报错，不过计算结果有一部份不对；另外效率也同样很低。返回上面那个1368行的记录所用时间也是两分四十多秒。不知还有没有什么更好的方法？[/quote] 先執行你得到源數據的查詢，看耗時多長。 可能那一部分應該是你要優化的部分。
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
引用 26 楼 F172515968 的回复:
[quote=引用 21 楼 ky_min 的回复:] 每次都去动态执行查询 大数据的递归运算肯定慢 可不可以考虑做成静态的，一次执行，多次受益，体现那神一样的速度~~
做静态估计也不现实，因为返回的记录需要动态根据不同条件返回不同结果；[/quote] 应该到最后也是由程序来显示吧 可以在显示时加个中间变量，这样就算是一两千行的数量也只遍历一次 这样应该会提高很高的速度
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 21 楼 ky_min 的回复:
每次都去动态执行查询 大数据的递归运算肯定慢 可不可以考虑做成静态的，一次执行，多次受益，体现那神一样的速度~~
做静态估计也不现实，因为返回的记录需要动态根据不同条件返回不同结果；
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 22 楼 Tiger_Zhao 的回复:
你可以先去掉 CTE 部分，做个简单的 SELECT * FROM [前一个表达式]，比较两种方法的运行时间看看。
如果不执行时没有CTE当然快呀，就是下面这一句用到JOIN CTE，所以才慢。 SELECT A.RN,a.id,A.rq,A.djbh,A.djlx,A.zy,A.ckbm,A.hcbz,A.spbm,A.ph,A.rkjs,A.rksl,A.ckjs,A.cksl,CAST(A.rkjs-A.ckjs+B.kcjs AS numeric(18, 2)) as kcjs,CAST(A.rksl-A.cksl+B.kcsl AS numeric(18, 2)) as kcsl,a.dj,a.je FROM T1CTE A JOIN CTE B ON A.RN=B.RN+1
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 6 楼 reenjie 的回复:
[quote=引用 4 楼 F172515968 的回复:] [quote=引用 3 楼 reenjie 的回复:] 不知道入庫數量怎麼有為空?不是為0嗎? 假設入庫數量的字段類型是數字 

with cte as 
(
select *,row_number() over(order by getdate() asc) as rId from T1
)
select a.[日期],a.[入库数量],a.[出库数量],(select sum(入库数量) from cte where rId<=a.rId)-a.[出库数量]  from cte as a
对，是为0的，数据是我用文本手动写的[/quote] 那就行,上面的sql可以得到你想要的結果嗎?[/quote] 这个方法没有报错，不过计算结果有一部份不对；另外效率也同样很低。返回上面那个1368行的记录所用时间也是两分四十多秒。不知还有没有什么更好的方法？
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
如 #20 Tiger_Zhao 嗯，你可以在串联每个查询里面加条件，这个可以优化一点~~
Tiger_Zhao 2014-10-20
  • 打赏
  • 举报
 回复
你可以先去掉 CTE 部分，做个简单的 SELECT * FROM [前一个表达式]，比较两种方法的运行时间看看。
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
每次都去动态执行查询 大数据的递归运算肯定慢 可不可以考虑做成静态的，一次执行，多次受益，体现那神一样的速度~~
Tiger_Zhao 2014-10-20
  • 打赏
  • 举报
 回复
你中间处理的数据远远超过一千多条！！！
CTE 前面一个表达式中，将 where 条件应用到每个 union all 分支的 select 语句中，这样才是将需要的数据先筛选再串联。
你贴图中的做法是先串联全部数据再做筛选，效率差好多！
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
引用 18 楼 ky_min 的回复:
[quote=引用 16 楼 F172515968 的回复:]
不过感觉这种方法是不是效率不高？我查询记录为264条记录，所用时间10秒； 有没有更好的方法？

这个查询用 子查询也是可以的，你试下 reenjie 的语句的速度，先不管结果对不对
你这个是有顺序的执行，游标也可以考虑，但感觉最好不要[/quote]

游标估计也是一样慢，请问在保证结果正常的情况下哪一种方法效果最高？目前的效率肯定是不行的，因为一两千条记录是经常出现的。现在这种方式如果有一千多条记录的话都需要两三分析钟的。如下图：
还在加载中灬 2014-10-20
  • 打赏
  • 举报
 回复
引用 16 楼 F172515968 的回复:
不过感觉这种方法是不是效率不高？我查询记录为264条记录，所用时间10秒； 有没有更好的方法？
这个查询用 子查询也是可以的，你试下 reenjie 的语句的速度，先不管结果对不对 你这个是有顺序的执行，游标也可以考虑，但感觉最好不要
Tiger_Zhao 2014-10-20
  • 打赏
  • 举报
 回复
把库存余额作为永久字段保留下来，每条记录新增时就计算好。
否则数据越多越慢。
想到才能做到 2014-10-20
  • 打赏
  • 举报
 回复
不过感觉这种方法是不是效率不高？我查询记录为264条记录，所用时间10秒； 有没有更好的方法？
加载更多回复（15）
新能源船型研发——以南湖42客位太阳能船为例.pdf
【南湖42客位太阳能船】针对新能源船型的开发，以嘉兴南湖42客位旅游船为例，设计目标是保证巡航速度至少10公里/小时，续航力不少于8小时，同时节能40%以上。船体主尺度包括总长13.80米，设计吃水0.80米，排水量约为...
餐饮6.0触摸屏功能说明
“查看客单”功能方便查询某位顾客的订单详情，如果忘记客位号码，可以利用“客位视图”功能查找。若需解锁已埋单或锁定的账单，只需点击“解锁”，即可恢复操作权限。 “预订市别”功能支持按时间段查看预订情况，...
吾享餐饮管理系统70-前台营业操作手册.docx
- 可以根据客位状态、消费区域、客位类型进行过滤。 - 系统主界面上方显示当前登录POS、用户、日期等基本信息。 - 用户可以通过“问题反馈”功能报告使用过程中遇到的问题。 - 通过双击左上角的吾享餐饮管理系统...
公司餐饮管理系统前台营业操作办法
系统通常会自动关联预订信息，并显示在客位列表中，便于快速定位并完成开单过程。 ##### 1.5 加单 加单是指向已有的订单中添加新的菜品或服务项目。主要有以下几种方式： - **加品项**：在订单中添加单一菜品。 -...
足疗店铺的经营数据分析.pdf
- 除了上述财务指标外，还包括营业收入、销售总收入、总客位数、各项目客位数占总客位数比例、人均消费数额、日均客位数、下午及夜晚上客比例、高峰时段、点号率、接待率、男女顾客比例、批次客数比例、沙发平均...
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章