回答編集履歴
7
推敲
test
CHANGED
@@ -152,7 +152,7 @@
|
|
152
152
|
|
153
153
|
on rd_sum.rh_pk = rrd_sum.rh_pk
|
154
154
|
|
155
|
-
and rd_sum.c_cd = rrd_sum.c_cd
|
155
|
+
and rd_sum.c_cd = rrd_sum.c_cd
|
156
156
|
|
157
157
|
and rd_sum.o_no = rrd_sum.o_no
|
158
158
|
|
6
推敲
test
CHANGED
@@ -64,13 +64,13 @@
|
|
64
64
|
|
65
65
|
from tbl1 rd
|
66
66
|
|
67
|
-
inner join tbl2 sd
|
67
|
+
inner join tbl2 sd
|
68
68
|
|
69
|
-
on rd.rh_pk = sd.rh_pk
|
69
|
+
on rd.rh_pk = sd.rh_pk
|
70
70
|
|
71
|
-
and rd.rd_pk = sd.rd_pk
|
71
|
+
and rd.rd_pk = sd.rd_pk
|
72
72
|
|
73
|
-
and rd.c_cd = sd.c_cd
|
73
|
+
and rd.c_cd = sd.c_cd
|
74
74
|
|
75
75
|
where rd.c_cd = '01'
|
76
76
|
|
@@ -84,13 +84,13 @@
|
|
84
84
|
|
85
85
|
select rrh.rh_pk, rrd.item_no, sum(rrd.diff) as diff
|
86
86
|
|
87
|
-
from tbl3 rrh
|
87
|
+
from tbl3 rrh
|
88
88
|
|
89
|
-
inner join tbl4 rrd
|
89
|
+
inner join tbl4 rrd
|
90
90
|
|
91
|
-
on rrh.r_pk = rrd.r_pk
|
91
|
+
on rrh.r_pk = rrd.r_pk
|
92
92
|
|
93
|
-
and rrh.c_cd = rrd.c_cd
|
93
|
+
and rrh.c_cd = rrd.c_cd
|
94
94
|
|
95
95
|
where rrd.c_cd = '01'
|
96
96
|
|
@@ -102,7 +102,7 @@
|
|
102
102
|
|
103
103
|
on rd_sum.rh_pk = rrd_sum.rh_pk
|
104
104
|
|
105
|
-
|
105
|
+
and rd_sum.item_no = rrd_sum.item_no
|
106
106
|
|
107
107
|
order by item_no
|
108
108
|
|
@@ -124,29 +124,27 @@
|
|
124
124
|
|
125
125
|
from tbl1 rd
|
126
126
|
|
127
|
-
inner join tbl2 sd
|
127
|
+
inner join tbl2 sd
|
128
128
|
|
129
|
-
on rd.rh_pk = sd.rh_pk
|
129
|
+
on rd.rh_pk = sd.rh_pk
|
130
130
|
|
131
|
-
and rd.rd_pk = sd.rd_pk
|
131
|
+
and rd.rd_pk = sd.rd_pk
|
132
132
|
|
133
|
-
and rd.c_cd = sd.c_cd
|
133
|
+
and rd.c_cd = sd.c_cd
|
134
134
|
|
135
135
|
group by rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no
|
136
136
|
|
137
|
-
) rd_sum
|
138
|
-
|
139
|
-
inner join (
|
137
|
+
) rd_sum inner join (
|
140
138
|
|
141
139
|
select rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no, sum(rrd.diff) as diff
|
142
140
|
|
143
|
-
from tbl3 rrh
|
141
|
+
from tbl3 rrh
|
144
142
|
|
145
|
-
inner join tbl4 rrd
|
143
|
+
inner join tbl4 rrd
|
146
144
|
|
147
|
-
on rrh.r_pk = rrd.r_pk
|
145
|
+
on rrh.r_pk = rrd.r_pk
|
148
146
|
|
149
|
-
and rrh.c_cd = rrd.c_cd
|
147
|
+
and rrh.c_cd = rrd.c_cd
|
150
148
|
|
151
149
|
group by rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no
|
152
150
|
|
5
追記
test
CHANGED
@@ -107,3 +107,61 @@
|
|
107
107
|
order by item_no
|
108
108
|
|
109
109
|
```
|
110
|
+
|
111
|
+
追記2
|
112
|
+
|
113
|
+
---
|
114
|
+
|
115
|
+
データ件数が少ないならさほどレスポンスは落ちないでしょうから、条件部分を収斂したパターンも記述しておきます。
|
116
|
+
|
117
|
+
```SQL
|
118
|
+
|
119
|
+
select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff
|
120
|
+
|
121
|
+
from (
|
122
|
+
|
123
|
+
select rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no, sum(sd.quantity) as quantity
|
124
|
+
|
125
|
+
from tbl1 rd
|
126
|
+
|
127
|
+
inner join tbl2 sd
|
128
|
+
|
129
|
+
on rd.rh_pk = sd.rh_pk
|
130
|
+
|
131
|
+
and rd.rd_pk = sd.rd_pk
|
132
|
+
|
133
|
+
and rd.c_cd = sd.c_cd
|
134
|
+
|
135
|
+
group by rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no
|
136
|
+
|
137
|
+
) rd_sum
|
138
|
+
|
139
|
+
inner join (
|
140
|
+
|
141
|
+
select rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no, sum(rrd.diff) as diff
|
142
|
+
|
143
|
+
from tbl3 rrh
|
144
|
+
|
145
|
+
inner join tbl4 rrd
|
146
|
+
|
147
|
+
on rrh.r_pk = rrd.r_pk
|
148
|
+
|
149
|
+
and rrh.c_cd = rrd.c_cd
|
150
|
+
|
151
|
+
group by rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no
|
152
|
+
|
153
|
+
) rrd_sum
|
154
|
+
|
155
|
+
on rd_sum.rh_pk = rrd_sum.rh_pk
|
156
|
+
|
157
|
+
and rd_sum.c_cd = rrd_sum.c_cd a
|
158
|
+
|
159
|
+
and rd_sum.o_no = rrd_sum.o_no
|
160
|
+
|
161
|
+
and rd_sum.item_no = rrd_sum.item_no
|
162
|
+
|
163
|
+
where rd_sum.c_cd='01' and rd_sum.o_no='A100'
|
164
|
+
|
165
|
+
order by item_no
|
166
|
+
|
167
|
+
```
|
4
修正
test
CHANGED
@@ -74,7 +74,7 @@
|
|
74
74
|
|
75
75
|
where rd.c_cd = '01'
|
76
76
|
|
77
|
-
and rd.o_no = '
|
77
|
+
and rd.o_no = 'A100'
|
78
78
|
|
79
79
|
group by rd.rh_pk, rd.rd_pk, rd.item_no
|
80
80
|
|
@@ -94,7 +94,7 @@
|
|
94
94
|
|
95
95
|
where rrd.c_cd = '01'
|
96
96
|
|
97
|
-
and rrh.o_no = '
|
97
|
+
and rrh.o_no = 'A100'
|
98
98
|
|
99
99
|
group by rrh.rh_pk, rrd.item_no
|
100
100
|
|
3
追記
test
CHANGED
@@ -49,6 +49,8 @@
|
|
49
49
|
---
|
50
50
|
|
51
51
|
質問のSQLでは動作しないので、推測での記述ですけど、以下でquantityとdiffは取り出せていると思います。
|
52
|
+
|
53
|
+
こういった場合、そのぞれの集計結果をどの項目で結びつけるかを明らかにすると迷わなくてすみます。
|
52
54
|
|
53
55
|
※diffの複数行については良く分からないので、質問にデータイメージを追記して下さい。
|
54
56
|
|
2
追記
test
CHANGED
@@ -41,3 +41,67 @@
|
|
41
41
|
|
42
42
|
|
43
43
|
sum(diff1), sum(diff2)のように増えるとしても、集計の単位が同じなら、sum()の項目を増やせば良いだけ
|
44
|
+
|
45
|
+
|
46
|
+
|
47
|
+
追記
|
48
|
+
|
49
|
+
---
|
50
|
+
|
51
|
+
質問のSQLでは動作しないので、推測での記述ですけど、以下でquantityとdiffは取り出せていると思います。
|
52
|
+
|
53
|
+
※diffの複数行については良く分からないので、質問にデータイメージを追記して下さい。
|
54
|
+
|
55
|
+
```SQL
|
56
|
+
|
57
|
+
select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff
|
58
|
+
|
59
|
+
from (
|
60
|
+
|
61
|
+
select rd.rh_pk, rd.rd_pk, rd.item_no, sum(sd.quantity) as quantity
|
62
|
+
|
63
|
+
from tbl1 rd
|
64
|
+
|
65
|
+
inner join tbl2 sd
|
66
|
+
|
67
|
+
on rd.rh_pk = sd.rh_pk
|
68
|
+
|
69
|
+
and rd.rd_pk = sd.rd_pk
|
70
|
+
|
71
|
+
and rd.c_cd = sd.c_cd
|
72
|
+
|
73
|
+
where rd.c_cd = '01'
|
74
|
+
|
75
|
+
and rd.o_no = 'a100'
|
76
|
+
|
77
|
+
group by rd.rh_pk, rd.rd_pk, rd.item_no
|
78
|
+
|
79
|
+
) rd_sum
|
80
|
+
|
81
|
+
inner join (
|
82
|
+
|
83
|
+
select rrh.rh_pk, rrd.item_no, sum(rrd.diff) as diff
|
84
|
+
|
85
|
+
from tbl3 rrh
|
86
|
+
|
87
|
+
inner join tbl4 rrd
|
88
|
+
|
89
|
+
on rrh.r_pk = rrd.r_pk
|
90
|
+
|
91
|
+
and rrh.c_cd = rrd.c_cd
|
92
|
+
|
93
|
+
where rrd.c_cd = '01'
|
94
|
+
|
95
|
+
and rrh.o_no = 'a100'
|
96
|
+
|
97
|
+
group by rrh.rh_pk, rrd.item_no
|
98
|
+
|
99
|
+
) rrd_sum
|
100
|
+
|
101
|
+
on rd_sum.rh_pk = rrd_sum.rh_pk
|
102
|
+
|
103
|
+
and rd_sum.item_no = rrd_sum.item_no
|
104
|
+
|
105
|
+
order by item_no
|
106
|
+
|
107
|
+
```
|
1
追記
test
CHANGED
@@ -33,3 +33,11 @@
|
|
33
33
|
GROUP BY RD.RH_PK, RD.RD_PK, RD.Item_no
|
34
34
|
|
35
35
|
```
|
36
|
+
|
37
|
+
> ※QuantityとDiffはレコード増える仕様
|
38
|
+
|
39
|
+
> この例ではDiffは1つですが複数行ある場合を仮定して頂きたいです。
|
40
|
+
|
41
|
+
|
42
|
+
|
43
|
+
sum(diff1), sum(diff2)のように増えるとしても、集計の単位が同じなら、sum()の項目を増やせば良いだけ
|