回答編集履歴
1
SQLに「品番」について追加。
test
CHANGED
@@ -1,6 +1,6 @@
|
|
1
1
|
SQLパズルのつもりで作成したら、「品番」の部分を失念していました。
|
2
2
|
|
3
|
-
時間切れなので、ヒントとして参考になればと回答しておきます。
|
3
|
+
時間切れなので、ヒントとして参考になればと回答しておきます。
|
4
4
|
|
5
5
|
ただこのSQLを上長に説明するのでしょうか…
|
6
6
|
|
@@ -10,33 +10,39 @@
|
|
10
10
|
|
11
11
|
|
12
12
|
|
13
|
+
2017/06/27
|
14
|
+
|
15
|
+
品番について修正しました。
|
16
|
+
|
17
|
+
|
18
|
+
|
13
19
|
```SQL
|
14
20
|
|
15
21
|
WITH WV AS(
|
16
22
|
|
17
23
|
SELECT
|
18
24
|
|
19
|
-
WT.[連番]
|
25
|
+
WT.[連番]
|
20
|
-
|
26
|
+
|
21
|
-
WT.[入出キー]
|
27
|
+
,WT.[入出キー]
|
22
|
-
|
28
|
+
|
23
|
-
WT.[品番]
|
29
|
+
,WT.[品番]
|
24
|
-
|
30
|
+
|
25
|
-
WT.[区分]
|
31
|
+
,WT.[区分]
|
26
|
-
|
32
|
+
|
27
|
-
WT.[入出額]
|
33
|
+
,WT.[入出額]
|
28
|
-
|
34
|
+
|
29
|
-
WT.[単価]
|
35
|
+
,WT.[単価]
|
30
|
-
|
36
|
+
|
31
|
-
WT.[数量]
|
37
|
+
,WT.[数量]
|
32
|
-
|
38
|
+
|
33
|
-
WT.[在庫数]
|
39
|
+
,WT.[在庫数]
|
34
|
-
|
40
|
+
|
35
|
-
WT.[入庫計]
|
41
|
+
,WT.[入庫計]
|
36
|
-
|
42
|
+
|
37
|
-
WT.[出庫計]
|
43
|
+
,WT.[出庫計]
|
38
|
-
|
44
|
+
|
39
|
-
LAG(WT.[入庫計],
|
45
|
+
,LAG(WT.[入庫計] ,1 ,0) OVER(PARTITION BY WT.[品番] ORDER BY WT.[連番]) AS [L入庫計]
|
40
46
|
|
41
47
|
FROM
|
42
48
|
|
@@ -44,23 +50,23 @@
|
|
44
50
|
|
45
51
|
SELECT
|
46
52
|
|
47
|
-
MT.[連番]
|
53
|
+
MT.[連番]
|
48
|
-
|
54
|
+
|
49
|
-
MT.[入出キー]
|
55
|
+
,MT.[入出キー]
|
50
|
-
|
56
|
+
|
51
|
-
MT.[品番]
|
57
|
+
,MT.[品番]
|
52
|
-
|
58
|
+
|
53
|
-
MT.[区分]
|
59
|
+
,MT.[区分]
|
54
|
-
|
60
|
+
|
55
|
-
MT.[単価]
|
61
|
+
,MT.[単価]
|
56
|
-
|
62
|
+
|
57
|
-
MT.[数量]
|
63
|
+
,MT.[数量]
|
58
|
-
|
64
|
+
|
59
|
-
MT.[入出額]
|
65
|
+
,MT.[入出額]
|
60
|
-
|
66
|
+
|
61
|
-
SUM(MT.[数量]) OVER(ORDER BY MT.[連番]) AS [在庫数]
|
67
|
+
,SUM(MT.[数量]) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [在庫数]
|
62
|
-
|
68
|
+
|
63
|
-
SUM(
|
69
|
+
,SUM(
|
64
70
|
|
65
71
|
CASE
|
66
72
|
|
@@ -70,9 +76,9 @@
|
|
70
76
|
|
71
77
|
END
|
72
78
|
|
73
|
-
) OVER(ORDER BY MT.[連番]) AS [入庫計]
|
79
|
+
) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [入庫計]
|
74
|
-
|
80
|
+
|
75
|
-
SUM(
|
81
|
+
,SUM(
|
76
82
|
|
77
83
|
CASE
|
78
84
|
|
@@ -82,7 +88,7 @@
|
|
82
88
|
|
83
89
|
END
|
84
90
|
|
85
|
-
) OVER(ORDER BY MT.[連番]) AS [出庫計]
|
91
|
+
) OVER(PARTITION BY MT.[品番] ORDER BY MT.[連番]) AS [出庫計]
|
86
92
|
|
87
93
|
FROM
|
88
94
|
|
@@ -90,25 +96,25 @@
|
|
90
96
|
|
91
97
|
SELECT
|
92
98
|
|
93
|
-
ROW_NUMBER() OVER(ORDER BY ZT.[入出キー],
|
99
|
+
ROW_NUMBER() OVER(ORDER BY ZT.[入出キー] ,T.[SEQ]) AS [連番]
|
94
|
-
|
100
|
+
|
95
|
-
ZT.[入出キー]
|
101
|
+
,ZT.[入出キー]
|
96
|
-
|
102
|
+
|
97
|
-
ZT.[品番]
|
103
|
+
,ZT.[品番]
|
98
|
-
|
104
|
+
|
99
|
-
ZT.[区分]
|
105
|
+
,ZT.[区分]
|
100
|
-
|
106
|
+
|
101
|
-
CASE
|
107
|
+
,CASE
|
102
108
|
|
103
109
|
WHEN [区分] = 1 THEN T.[数量]
|
104
110
|
|
105
111
|
WHEN [区分] = 2 THEN - ZT.[数量1]
|
106
112
|
|
107
|
-
END AS [数量]
|
113
|
+
END AS [数量]
|
108
|
-
|
114
|
+
|
109
|
-
T.[単価]
|
115
|
+
,T.[単価]
|
110
|
-
|
116
|
+
|
111
|
-
ZT.[入出額]
|
117
|
+
,ZT.[入出額]
|
112
118
|
|
113
119
|
FROM
|
114
120
|
|
@@ -120,13 +126,13 @@
|
|
120
126
|
|
121
127
|
SELECT
|
122
128
|
|
123
|
-
T1.[SEQ]
|
129
|
+
T1.[SEQ]
|
124
|
-
|
130
|
+
|
125
|
-
T1.[入出キー]
|
131
|
+
,T1.[入出キー]
|
126
|
-
|
132
|
+
|
127
|
-
T1.[単価]
|
133
|
+
,T1.[単価]
|
128
|
-
|
134
|
+
|
129
|
-
T2.[数量]
|
135
|
+
,T2.[数量]
|
130
136
|
|
131
137
|
FROM
|
132
138
|
|
@@ -134,15 +140,15 @@
|
|
134
140
|
|
135
141
|
SELECT
|
136
142
|
|
137
|
-
ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
|
143
|
+
ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
|
138
|
-
|
144
|
+
|
139
|
-
[入出キー]
|
145
|
+
,[入出キー]
|
140
|
-
|
146
|
+
|
141
|
-
[単価]
|
147
|
+
,[単価]
|
142
148
|
|
143
149
|
FROM
|
144
150
|
|
145
|
-
[在庫テーブル] UNPIVOT([単価] FOR COLNAME1 IN([単価1],
|
151
|
+
[在庫テーブル] UNPIVOT([単価] FOR COLNAME1 IN([単価1] ,[単価2])) UP
|
146
152
|
|
147
153
|
WHERE
|
148
154
|
|
@@ -156,13 +162,13 @@
|
|
156
162
|
|
157
163
|
SELECT
|
158
164
|
|
159
|
-
ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
|
165
|
+
ROW_NUMBER() OVER(ORDER BY [入出キー]) AS [SEQ]
|
160
|
-
|
166
|
+
|
161
|
-
[数量]
|
167
|
+
,[数量]
|
162
168
|
|
163
169
|
FROM
|
164
170
|
|
165
|
-
[在庫テーブル] UNPIVOT([数量] FOR COLNAME1 IN([数量1],
|
171
|
+
[在庫テーブル] UNPIVOT([数量] FOR COLNAME1 IN([数量1] ,[数量2])) UP
|
166
172
|
|
167
173
|
WHERE
|
168
174
|
|
@@ -184,27 +190,27 @@
|
|
184
190
|
|
185
191
|
SELECT
|
186
192
|
|
187
|
-
a.[連番]
|
193
|
+
a.[連番]
|
188
|
-
|
194
|
+
|
189
|
-
a.[入出キー]
|
195
|
+
,a.[入出キー]
|
190
|
-
|
196
|
+
|
191
|
-
a.[品番]
|
197
|
+
,a.[品番]
|
192
|
-
|
198
|
+
|
193
|
-
a.[区分]
|
199
|
+
,a.[区分]
|
194
|
-
|
200
|
+
|
195
|
-
a.[入出額]
|
201
|
+
,a.[入出額]
|
196
|
-
|
202
|
+
|
197
|
-
a.[単価]
|
203
|
+
,a.[単価]
|
198
|
-
|
204
|
+
|
199
|
-
a.[数量]
|
205
|
+
,a.[数量]
|
200
|
-
|
206
|
+
|
201
|
-
a.[在庫数]
|
207
|
+
,a.[在庫数]
|
202
|
-
|
208
|
+
|
203
|
-
a.[入庫計]
|
209
|
+
,a.[入庫計]
|
204
|
-
|
210
|
+
|
205
|
-
a.[出庫計]
|
211
|
+
,a.[出庫計]
|
206
|
-
|
212
|
+
|
207
|
-
(
|
213
|
+
,(
|
208
214
|
|
209
215
|
(
|
210
216
|
|
@@ -222,6 +228,8 @@
|
|
222
228
|
|
223
229
|
AND b.[数量] > 0
|
224
230
|
|
231
|
+
AND b.[品番] = a.[品番]
|
232
|
+
|
225
233
|
) - CASE
|
226
234
|
|
227
235
|
WHEN a.[出庫計] > 0 THEN ISNULL((
|
@@ -242,7 +250,9 @@
|
|
242
250
|
|
243
251
|
AND b.[入庫計] <= a.[出庫計]
|
244
252
|
|
253
|
+
AND b.[品番] = a.[品番]
|
254
|
+
|
245
|
-
),
|
255
|
+
) ,0) + ISNULL((
|
246
256
|
|
247
257
|
SELECT
|
248
258
|
|
@@ -270,9 +280,11 @@
|
|
270
280
|
|
271
281
|
AND c.[入庫計] > a.[出庫計]
|
272
282
|
|
283
|
+
AND c.[品番] = a.[品番]
|
284
|
+
|
273
285
|
)
|
274
286
|
|
275
|
-
),
|
287
|
+
) ,0)
|
276
288
|
|
277
289
|
ELSE 0
|
278
290
|
|
@@ -286,7 +298,9 @@
|
|
286
298
|
|
287
299
|
ORDER BY
|
288
300
|
|
301
|
+
a.[品番]
|
302
|
+
|
289
|
-
a.[連番]
|
303
|
+
,a.[連番]
|
290
304
|
|
291
305
|
;
|
292
306
|
|