teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップSQLに関する質問SQLサーバーで抽出できますでしょうか？

編集履歴

回答編集履歴

1

SQLに「品番」について追加。

2017/06/27 00:15

投稿

shoko1
shoko1

スコア372

answer CHANGED
@@ -1,86 +1,89 @@
1
1
  SQLパズルのつもりで作成したら、「品番」の部分を失念していました。
2
- 時間切れなので、ヒントとして参考になればと回答しておきます。（追記するかも。）
2
+ 時間切れなので、ヒントとして参考になればと回答しておきます。
3
3
  ただこのSQLを上長に説明するのでしょうか…
4
4
 
5
5
  手元の SQL Server 2016 で確認。2008では未確認です。
6
6
 
7
+ 2017/06/27
8
+ 品番について修正しました。
9
+
7
10
  ```SQL
8
11
  WITH WV AS(
9
12
  SELECT
10
- WT.[連番],
13
+ WT.[連番]
11
- WT.[入出キー],
14
+ ,WT.[入出キー]
12
- WT.[品番],
15
+ ,WT.[品番]
13
- WT.[区分],
16
+ ,WT.[区分]
14
- WT.[入出額],
17
+ ,WT.[入出額]
15
- WT.[単価],
18
+ ,WT.[単価]
16
- WT.[数量],
19
+ ,WT.[数量]
17
- WT.[在庫数],
20
+ ,WT.[在庫数]
18
- WT.[入庫計],
21
+ ,WT.[入庫計]
19
- WT.[出庫計],
22
+ ,WT.[出庫計]
20
- LAG(WT.[入庫計], 1, 0) OVER(ORDER BY WT.[連番]) AS [L入庫計]
23
+ ,LAG(WT.[入庫計] ,1 ,0) OVER(PARTITION BY WT.[品番] ORDER BY WT.[連番]) AS [L入庫計]
21
24
  FROM
22
25
  (
23
26
  SELECT
24
- MT.[連番],
27
+ MT.[連番]
25
- MT.[入出キー],
28
+ ,MT.[入出キー]
26
- MT.[品番],
29
+ ,MT.[品番]
27
- MT.[区分],
30
+ ,MT.[区分]
28
- MT.[単価],
31
+ ,MT.[単価]
29
- MT.[数量],
32
+ ,MT.[数量]
30
- MT.[入出額],
33
+ ,MT.[入出額]
31
- SUM(MT.[数量]) OVER(ORDER BY MT.[連番]) AS [在庫数],
34
+ ,SUM(MT.[数量]) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [在庫数]
32
- SUM(
35
+ ,SUM(
33
36
  CASE
34
37
  WHEN MT.[数量] > 0 THEN MT.[数量]
35
38
  ELSE 0
36
39
  END
37
- ) OVER(ORDER BY MT.[連番]) AS [入庫計],
40
+ ) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [入庫計]
38
- SUM(
41
+ ,SUM(
39
42
  CASE
40
43
  WHEN MT.[数量] < 0 THEN - MT.[数量]
41
44
  ELSE 0
42
45
  END
43
- ) OVER(ORDER BY MT.[連番]) AS [出庫計]
46
+ ) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [出庫計]
44
47
  FROM
45
48
  (
46
49
  SELECT
47
- ROW_NUMBER() OVER(ORDER BY ZT.[入出キー], T.[SEQ]) AS [連番],
50
+ ROW_NUMBER() OVER(ORDER BY ZT.[入出キー] ,T.[SEQ]) AS [連番]
48
- ZT.[入出キー],
51
+ ,ZT.[入出キー]
49
- ZT.[品番],
52
+ ,ZT.[品番]
50
- ZT.[区分],
53
+ ,ZT.[区分]
51
- CASE
54
+ ,CASE
52
55
  WHEN [区分] = 1 THEN T.[数量]
53
56
  WHEN [区分] = 2 THEN - ZT.[数量1]
54
- END AS [数量],
57
+ END AS [数量]
55
- T.[単価],
58
+ ,T.[単価]
56
- ZT.[入出額]
59
+ ,ZT.[入出額]
57
60
  FROM
58
61
  [在庫テーブル] ZT
59
62
  LEFT JOIN
60
63
  (
61
64
  SELECT
62
- T1.[SEQ],
65
+ T1.[SEQ]
63
- T1.[入出キー],
66
+ ,T1.[入出キー]
64
- T1.[単価],
67
+ ,T1.[単価]
65
- T2.[数量]
68
+ ,T2.[数量]
66
69
  FROM
67
70
  (
68
71
  SELECT
69
- ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ],
72
+ ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
70
- [入出キー],
73
+ ,[入出キー]
71
- [単価]
74
+ ,[単価]
72
75
  FROM
73
- [在庫テーブル] UNPIVOT([単価] FOR COLNAME1 IN([単価1], [単価2])) UP
76
+ [在庫テーブル] UNPIVOT([単価] FOR COLNAME1 IN([単価1] ,[単価2])) UP
74
77
  WHERE
75
78
  [区分] = 1
76
79
  ) T1
77
80
  INNER JOIN
78
81
  (
79
82
  SELECT
80
- ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ],
83
+ ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
81
- [数量]
84
+ ,[数量]
82
85
  FROM
83
- [在庫テーブル] UNPIVOT([数量] FOR COLNAME1 IN([数量1], [数量2])) UP
86
+ [在庫テーブル] UNPIVOT([数量] FOR COLNAME1 IN([数量1] ,[数量2])) UP
84
87
  WHERE
85
88
  [区分] = 1
86
89
  ) T2
@@ -91,17 +94,17 @@
91
94
  ) WT
92
95
  )
93
96
  SELECT
94
- a.[連番],
97
+ a.[連番]
95
- a.[入出キー],
98
+ ,a.[入出キー]
96
- a.[品番],
99
+ ,a.[品番]
97
- a.[区分],
100
+ ,a.[区分]
98
- a.[入出額],
101
+ ,a.[入出額]
99
- a.[単価],
102
+ ,a.[単価]
100
- a.[数量],
103
+ ,a.[数量]
101
- a.[在庫数],
104
+ ,a.[在庫数]
102
- a.[入庫計],
105
+ ,a.[入庫計]
103
- a.[出庫計],
106
+ ,a.[出庫計]
104
- (
107
+ ,(
105
108
  (
106
109
  SELECT
107
110
  SUM(b.[数量] * b.[単価])
@@ -110,6 +113,7 @@
110
113
  WHERE
111
114
  b.[連番] <= a.[連番]
112
115
  AND b.[数量] > 0
116
+ AND b.[品番] = a.[品番]
113
117
  ) - CASE
114
118
  WHEN a.[出庫計] > 0 THEN ISNULL((
115
119
  SELECT
@@ -120,7 +124,8 @@
120
124
  b.[連番] <= a.[連番]
121
125
  AND b.[数量] > 0
122
126
  AND b.[入庫計] <= a.[出庫計]
127
+ AND b.[品番] = a.[品番]
123
- ), 0) + ISNULL((
128
+ ) ,0) + ISNULL((
124
129
  SELECT
125
130
  (a.[出庫計] - b.[L入庫計]) * b.[単価]
126
131
  FROM
@@ -134,14 +139,16 @@
134
139
  WHERE
135
140
  c.[連番] <= a.[連番]
136
141
  AND c.[入庫計] > a.[出庫計]
142
+ AND c.[品番] = a.[品番]
137
143
  )
138
- ), 0)
144
+ ) ,0)
139
145
  ELSE 0
140
146
  END
141
147
  ) AS [金額]
142
148
  FROM
143
149
  WV a
144
150
  ORDER BY
151
+ a.[品番]
145
- a.[連番]
152
+ ,a.[連番]
146
153
  ;
147
154
  ```