在论坛中出现的比较难的sql问题:40(子查询 销售和历史库存)

原文:在论坛中出现的比较难的sql问题:40(子查询 销售和历史库存)


最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。

所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。

求教:我有个表有入库时间,有出库时间,我想得到该样式号每月的销售和历史库存

http://bbs.csdn.net/topics/390629790

库结构大概是这样:

货号    样式号    入库时间    出库时间

a001    10    2011-1-10        

b002    10    2011-1-10    2011-2-1

c003    10    2012-1-15    2012-2-2

d004    10    2013-2-3    2013-1-5

e005    10    2013-2-3        

f006    15    2011-2-15    2011-3-16

g007    15    2011-2-16    2012-3-16

h009    15    2013-1-10    

m012    18    2011-1-4        

c009    18    2011-4-5    2012-5-6

f008    18    2012-2-19    2013-1-1

e008    18    2013-1-5    2013-2-6

 

我想得到的结果:

样式号    日期    销售件数    库存件数    

10    2011-2    1    1

10    2012-2    1    1

10    2013-1    1    2

15    2011-3    1    0

15    2012-3    1    1

18    2012-5    1    1

18    2013-1    1    1

18    2013-2    1    1

我的解法:


  1. if object_id('tb') is not null
  2. drop table tb
  3. go
  4. create table tb
  5. (
  6. [货号] varchar(20),[样式号] int,
  7. [入库时间] datetime,
  8. [出库时间] datetime
  9. )
  10. insert into tb
  11. SELECT 'a001',10,'2011-01-10',null UNION ALL
  12. SELECT 'b002',10,'2011-01-10','2011-02-01' UNION ALL
  13. SELECT 'c003',10,'2012-01-15','2012-02-02' UNION ALL
  14. SELECT 'd004',10,'2013-01-03','2013-01-05' UNION ALL
  15. SELECT 'e005',10,'2013-01-03',null UNION ALL
  16. SELECT 'f006',15,'2011-02-15','2011-03-16' UNION ALL
  17. SELECT 'g007',15,'2011-02-16','2012-03-16' UNION ALL
  18. SELECT 'h009',15,'2013-01-10',null UNION ALL
  19. SELECT 'm012',18,'2011-01-04',null UNION ALL
  20. SELECT 'c009',18,'2011-04-05','2012-05-06' UNION ALL
  21. SELECT 'f008',18,'2012-02-19','2013-01-01' UNION ALL
  22. SELECT 'e008',18,'2013-01-05','2013-02-06'
  23. go
  24. ;with t
  25. as(
  26. select *,
  27. row_number() over(partition by 样式号
  28. order by 入库时间,出库时间) as rownum
  29. from tb
  30. ),
  31. tt
  32. as
  33. (
  34. select *,
  35. case when 出库时间 is null
  36. then (select top 1 出库时间
  37. from t t2
  38. where t1.样式号 = t2.样式号 and
  39. t1.rownum > t2.rownum
  40. order by t2.rownum desc)
  41. else 出库时间
  42. end as prior_row,
  43. case when 出库时间 is null
  44. then (select top 1 出库时间
  45. from t t2
  46. where t1.样式号 = t2.样式号 and
  47. t1.rownum < t2.rownum
  48. order by t2.rownum )
  49. else 出库时间
  50. end as next_row
  51. from t t1
  52. ),
  53. ttt
  54. as
  55. (
  56. select 样式号,
  57. convert(varchar(7),isnull(next_row,prior_row),120) as 日期,
  58. count(出库时间) 销售件数,
  59. count(入库时间) 库存件数,
  60. count(入库时间) - count(出库时间) 剩余库存
  61. --row_number() over(partition by 样式号
  62. --order by convert(varchar(7),isnull(next_row,prior_row),120)) as rownum
  63. from tt
  64. group by 样式号,
  65. convert(varchar(7),isnull(next_row,prior_row),120)
  66. )
  67. select t1.样式号,t1.日期,
  68. isnull(t1.销售件数,0) as 销售件数 ,
  69. isnull(t1.库存件数,0) +
  70. isnull((select sum(库存件数)-sum(销售件数) as 剩余库存 from ttt t2
  71. where t2.样式号 = t1.样式号
  72. and t2.日期 < t1.日期
  73. ),0) -
  74. isnull(t1.销售件数,0) as 库存件数
  75. from ttt t1
  76. order by t1.样式号
  77. /*
  78. 样式号 日期 销售件数 库存件数
  79. 10 2011-02 1 1
  80. 10 2012-02 1 1
  81. 10 2013-01 1 2
  82. 15 2011-03 1 0
  83. 15 2012-03 1 1
  84. 18 2012-05 1 1
  85. 18 2013-01 1 1
  86. 18 2013-02 1 1
  87. */

在论坛中出现的比较难的sql问题:40(子查询 销售和历史库存)
在论坛中出现的比较难的sql问题:40(子查询 销售和历史库存)
发布了416 篇原创文章 · 获赞 135 · 访问量 95万+
上一篇:现代C++学习笔记之二入门篇2,数据转换


下一篇:【LeetCode 28】KMP算法