質問編集履歴
4
修正依頼の修正
test
CHANGED
File without changes
|
test
CHANGED
@@ -88,13 +88,13 @@
|
|
88
88
|
|
89
89
|
|:--|:--:|--:|:--:|:--:|
|
90
90
|
|
91
|
-
|10|
|
91
|
+
|10|41|1|1|1|
|
92
|
-
|
92
|
+
|
93
|
-
|10|
|
93
|
+
|10|42|2|2|1|
|
94
|
-
|
94
|
+
|
95
|
-
|10|3
|
95
|
+
|10|43|3|2|2|
|
96
|
-
|
96
|
+
|
97
|
-
|10|
|
97
|
+
|10|44|4|4|2|
|
98
98
|
|
99
99
|
|
100
100
|
|
@@ -568,13 +568,13 @@
|
|
568
568
|
|
569
569
|
|:--|:--:|--:|:--:|:--:|
|
570
570
|
|
571
|
-
|10|
|
571
|
+
|10|41|1|1|2|
|
572
|
-
|
572
|
+
|
573
|
-
|10|
|
573
|
+
|10|42|2|2|3|
|
574
|
-
|
574
|
+
|
575
|
-
|10|3
|
575
|
+
|10|43|3|2|2|
|
576
|
-
|
576
|
+
|
577
|
-
|10|
|
577
|
+
|10|44|4|4|3|
|
578
578
|
|
579
579
|
|
580
580
|
|
3
修正依頼の回答
test
CHANGED
File without changes
|
test
CHANGED
@@ -76,8 +76,6 @@
|
|
76
76
|
|
77
77
|
|
78
78
|
|
79
|
-
|
80
|
-
|
81
79
|
今回欲しい結果
|
82
80
|
|
83
81
|
※QuantityとDiffはレコード増える仕様
|
@@ -534,6 +532,58 @@
|
|
534
532
|
|
535
533
|
|
536
534
|
|
535
|
+
↓追記2↓
|
536
|
+
|
537
|
+
|
538
|
+
|
539
|
+
TBL4(追記)
|
540
|
+
|
541
|
+
|R_PK|D_PK|C_Cd|Item_No|Diff|
|
542
|
+
|
543
|
+
|:--|:--:|--:|:--:|:--:|
|
544
|
+
|
545
|
+
|1 |31 |01 |1 |1 |
|
546
|
+
|
547
|
+
|1 |32 |01 |2 |1 |
|
548
|
+
|
549
|
+
|1 |33 |01 |3 |2 |
|
550
|
+
|
551
|
+
|1 |34 |01 |4 |2 |
|
552
|
+
|
553
|
+
|1 |35 |01 |1 |1 |
|
554
|
+
|
555
|
+
|1 |36 |01 |2 |2 |
|
556
|
+
|
557
|
+
|1 |37 |01 |3 |0 |
|
558
|
+
|
559
|
+
|1 |38 |01 |4 |1 |
|
560
|
+
|
561
|
+
|
562
|
+
|
563
|
+
|
564
|
+
|
565
|
+
追記後の取得したい結果
|
566
|
+
|
567
|
+
|RH_PK|RD_PK|Item_No|SUM(SD.Quantity)|SUM(RRD.Diff)|
|
568
|
+
|
569
|
+
|:--|:--:|--:|:--:|:--:|
|
570
|
+
|
571
|
+
|10|36|1|1|2|
|
572
|
+
|
573
|
+
|10|37|2|2|3|
|
574
|
+
|
575
|
+
|10|38|3|2|2|
|
576
|
+
|
577
|
+
|10|39|4|4|3|
|
578
|
+
|
579
|
+
|
580
|
+
|
581
|
+
↑追記2↑
|
582
|
+
|
583
|
+
|
584
|
+
|
585
|
+
|
586
|
+
|
537
587
|
何か良い方法があれば教えて頂きたいです。
|
538
588
|
|
539
589
|
よろしくお願いします。
|
2
サンプルデータのSQL文を追記します。
test
CHANGED
File without changes
|
test
CHANGED
@@ -378,6 +378,162 @@
|
|
378
378
|
|
379
379
|
|
380
380
|
|
381
|
+
|
382
|
+
|
383
|
+
↓追記↓
|
384
|
+
|
385
|
+
CREATE文
|
386
|
+
|
387
|
+
|
388
|
+
|
389
|
+
```
|
390
|
+
|
391
|
+
CREATE TABLE `TBL1` (
|
392
|
+
|
393
|
+
`RH_PK` int (11) NOT NULL DEFAULT '0'
|
394
|
+
|
395
|
+
, `RD_PK` int (11) NOT NULL AUTO_INCREMENT
|
396
|
+
|
397
|
+
, `C_Cd` varchar (4) NOT NULL
|
398
|
+
|
399
|
+
, `O_No` varchar (10) NOT NULL
|
400
|
+
|
401
|
+
, `Item_No` int (11) NOT NULL
|
402
|
+
|
403
|
+
, PRIMARY KEY (`RD_PK`)
|
404
|
+
|
405
|
+
, UNIQUE KEY `C_Cd` (`C_Cd`, `O_No`, `Item_No`)
|
406
|
+
|
407
|
+
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
|
408
|
+
|
409
|
+
|
410
|
+
|
411
|
+
CREATE TABLE `TBL2` (
|
412
|
+
|
413
|
+
`RH_PK` int (11) NOT NULL
|
414
|
+
|
415
|
+
, `RD_PK` int (11) NOT NULL
|
416
|
+
|
417
|
+
, `S_PK` int (11) NOT NULL
|
418
|
+
|
419
|
+
, `C_Cd` varchar (4) NOT NULL
|
420
|
+
|
421
|
+
, `Item_No` int (11) NOT NULL
|
422
|
+
|
423
|
+
, `Quantity` decimal (5, 0) DEFAULT NULL
|
424
|
+
|
425
|
+
, PRIMARY KEY (`RH_PK`, `RD_PK`, `S_PK`)
|
426
|
+
|
427
|
+
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
|
428
|
+
|
429
|
+
|
430
|
+
|
431
|
+
CREATE TABLE `TBL3` (
|
432
|
+
|
433
|
+
`R_PK` int (11) NOT NULL AUTO_INCREMENT
|
434
|
+
|
435
|
+
, `C_Cd` varchar (4) NOT NULL
|
436
|
+
|
437
|
+
, `RH_PK` int (11) NOT NULL
|
438
|
+
|
439
|
+
, `O_No` varchar (10) NOT NULL
|
440
|
+
|
441
|
+
, PRIMARY KEY (`R_PK`)
|
442
|
+
|
443
|
+
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
|
444
|
+
|
445
|
+
|
446
|
+
|
447
|
+
CREATE TABLE `TBL4` (
|
448
|
+
|
449
|
+
`D_PK` int (11) NOT NULL AUTO_INCREMENT
|
450
|
+
|
451
|
+
, `R_PK` int (11) NOT NULL
|
452
|
+
|
453
|
+
, `C_Cd` varchar (4) NOT NULL
|
454
|
+
|
455
|
+
, `Item_No` int (11) NOT NULL
|
456
|
+
|
457
|
+
, `Diff` decimal (5, 0) DEFAULT NULL
|
458
|
+
|
459
|
+
, PRIMARY KEY (`D_PK`)
|
460
|
+
|
461
|
+
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
|
462
|
+
|
463
|
+
|
464
|
+
|
465
|
+
```
|
466
|
+
|
467
|
+
INSERT文
|
468
|
+
|
469
|
+
```
|
470
|
+
|
471
|
+
INSERT
|
472
|
+
|
473
|
+
INTO TBL1(RH_PK, RD_PK, C_Cd, O_No, Item_No)
|
474
|
+
|
475
|
+
values (10, 41, 01, 'A100', 1)
|
476
|
+
|
477
|
+
, (10, 42, 01, 'A100', 2)
|
478
|
+
|
479
|
+
, (10, 43, 01, 'A100', 3)
|
480
|
+
|
481
|
+
, (10, 44, 01, 'A100', 4);
|
482
|
+
|
483
|
+
|
484
|
+
|
485
|
+
INSERT
|
486
|
+
|
487
|
+
INTO TBL2(RH_PK, RD_PK, S_PK, C_Cd, Item_No, Quantity)
|
488
|
+
|
489
|
+
values (10, 41, 1, 01, 1, 1)
|
490
|
+
|
491
|
+
, (10, 41, 2, 01, 1, 0)
|
492
|
+
|
493
|
+
, (10, 42, 1, 01, 2, 1)
|
494
|
+
|
495
|
+
, (10, 42, 2, 01, 2, 1)
|
496
|
+
|
497
|
+
, (10, 43, 1, 01, 3, 0)
|
498
|
+
|
499
|
+
, (10, 43, 2, 01, 3, 2)
|
500
|
+
|
501
|
+
, (10, 44, 1, 01, 4, 1)
|
502
|
+
|
503
|
+
, (10, 44, 2, 01, 4, 3);
|
504
|
+
|
505
|
+
|
506
|
+
|
507
|
+
INSERT
|
508
|
+
|
509
|
+
INTO TBL3(RH_PK, R_PK, C_Cd, O_No)
|
510
|
+
|
511
|
+
values (10, 1, 01, 'A100');
|
512
|
+
|
513
|
+
|
514
|
+
|
515
|
+
INSERT
|
516
|
+
|
517
|
+
INTO TBL4(R_PK, D_PK, C_Cd, Item_No, Diff)
|
518
|
+
|
519
|
+
values (1, 31, 01, 1, 1)
|
520
|
+
|
521
|
+
, (1, 32, 01, 2, 1)
|
522
|
+
|
523
|
+
, (1, 33, 01, 3, 2)
|
524
|
+
|
525
|
+
, (1, 34, 01, 4, 2);
|
526
|
+
|
527
|
+
|
528
|
+
|
529
|
+
```
|
530
|
+
|
531
|
+
|
532
|
+
|
533
|
+
↑追記↑
|
534
|
+
|
535
|
+
|
536
|
+
|
381
537
|
何か良い方法があれば教えて頂きたいです。
|
382
538
|
|
383
539
|
よろしくお願いします。
|
1
データ部分を表として挿入しました。
test
CHANGED
File without changes
|
test
CHANGED
@@ -8,66 +8,64 @@
|
|
8
8
|
|
9
9
|
TBL1
|
10
10
|
|
11
|
-
+-----------------------------+
|
12
|
-
|
13
11
|
|RH_PK|RD_PK|C_Cd|O_No|Item_No|
|
14
12
|
|
13
|
+
|:--|:--:|--:|:--:|:--:|
|
14
|
+
|
15
|
-
|10
|
15
|
+
|10|41|01|A100|1|
|
16
|
-
|
16
|
+
|
17
|
-
|10
|
17
|
+
|10|42|01|A100|2|
|
18
|
-
|
18
|
+
|
19
|
-
|10
|
19
|
+
|10|43|01|A100|3|
|
20
|
-
|
20
|
+
|
21
|
-
|10
|
21
|
+
|10|44|01|A100|4|
|
22
|
-
|
23
|
-
|
22
|
+
|
23
|
+
|
24
24
|
|
25
25
|
|
26
26
|
|
27
27
|
TBL2
|
28
28
|
|
29
|
-
+--------------------------------------+
|
30
|
-
|
31
29
|
|RH_PK|RD_PK|S_PK|C_Cd|Item_No|Quantity|
|
32
30
|
|
33
|
-
|10 |41 |1 |01 |1 |1 |
|
34
|
-
|
35
|
-
|10 |41 |2 |01 |1 |0 |
|
36
|
-
|
37
|
-
|10 |42 |1 |01 |2 |1 |
|
38
|
-
|
39
|
-
|10 |42 |2 |01 |2 |1 |
|
40
|
-
|
41
|
-
|10 |43 |1 |01 |3 |0 |
|
42
|
-
|
43
|
-
|10 |43 |2 |01 |3 |2 |
|
44
|
-
|
45
|
-
|10 |44 |1 |01 |4 |1 |
|
46
|
-
|
47
|
-
|10 |44 |2 |01 |4 |3 |
|
48
|
-
|
49
|
-
|
31
|
+
|:--|:--:|--:|:--:|:--:|:--:|
|
32
|
+
|
33
|
+
|10|41|1|01|1|1|
|
34
|
+
|
35
|
+
|10|41|2|01|1|0|
|
36
|
+
|
37
|
+
|10|42|1|01|2|1|
|
38
|
+
|
39
|
+
|10|42|2|01|2|1|
|
40
|
+
|
41
|
+
|10|43|1|01|3|0|
|
42
|
+
|
43
|
+
|10|43|2|01|3|2|
|
44
|
+
|
45
|
+
|10|44|1|01|4|1|
|
46
|
+
|
47
|
+
|10|44|2|01|4|3|
|
50
48
|
|
51
49
|
|
52
50
|
|
53
51
|
TBL3(TBL1とTBL4の紐付けに必要)
|
54
52
|
|
55
|
-
|
53
|
+
|
56
54
|
|
57
55
|
|RH_PK|R_PK|C_Cd|O_No|
|
58
56
|
|
57
|
+
|:--|:--:|--:|:--:|
|
58
|
+
|
59
|
-
|10
|
59
|
+
|10|1|01|A100|
|
60
|
-
|
61
|
-
+--------------------+
|
62
60
|
|
63
61
|
|
64
62
|
|
65
63
|
TBL4
|
66
64
|
|
67
|
-
+---------------------------+
|
68
|
-
|
69
65
|
|R_PK|D_PK|C_Cd|Item_No|Diff|
|
70
66
|
|
67
|
+
|:--|:--:|--:|:--:|:--:|
|
68
|
+
|
71
69
|
|1 |31 |01 |1 |1 |
|
72
70
|
|
73
71
|
|1 |32 |01 |2 |1 |
|
@@ -76,7 +74,7 @@
|
|
76
74
|
|
77
75
|
|1 |34 |01 |4 |2 |
|
78
76
|
|
79
|
-
|
77
|
+
|
80
78
|
|
81
79
|
|
82
80
|
|
@@ -88,19 +86,17 @@
|
|
88
86
|
|
89
87
|
|
90
88
|
|
91
|
-
+--------------------------------------------------+
|
92
|
-
|
93
89
|
|RH_PK|RD_PK|Item_No|SUM(SD.Quantity)|SUM(RRD.Diff)|
|
94
90
|
|
95
|
-
|10 |36 |1 |1 |1 |
|
96
|
-
|
97
|
-
|10 |37 |2 |2 |1 |
|
98
|
-
|
99
|
-
|10 |38 |3 |2 |2 |
|
100
|
-
|
101
|
-
|10 |39 |4 |4 |2 |
|
102
|
-
|
103
|
-
|
91
|
+
|:--|:--:|--:|:--:|:--:|
|
92
|
+
|
93
|
+
|10|36|1|1|1|
|
94
|
+
|
95
|
+
|10|37|2|2|1|
|
96
|
+
|
97
|
+
|10|38|3|2|2|
|
98
|
+
|
99
|
+
|10|39|4|4|2|
|
104
100
|
|
105
101
|
|
106
102
|
|