· 9 years ago · Dec 20, 2016, 02:42 PM
1CREATE TABLE IF NOT EXISTS `A` (
2`id` int(11) NOT NULL AUTO_INCREMENT,
3`num` int(6) NOT NULL,
4`docnr` int(6) NOT NULL,
5`docy` varchar(3) NOT NULL,
6`docd` date NOT NULL,
7`ent` int(3) NOT NULL,
8`paym` tinyint(1) NOT NULL,
9`cli` int(6) NOT NULL,
10`rags` varchar(40) NOT NULL,
11`addr` varchar(30) NOT NULL,
12`cap` varchar(5) NOT NULL,
13`city` varchar(30) NOT NULL,
14`pro` varchar(2) NOT NULL,
15`naz` varchar(3) NOT NULL,
16`telp` varchar(6) NOT NULL,
17`teln` int(13) NOT NULL,
18`rif` text NOT NULL,
19`totd` decimal(12,2) NOT NULL DEFAULT '0.00',
20`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
21`deleted` tinyint(1) NOT NULL DEFAULT '0',
22PRIMARY KEY (`id`)
23) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
24
25CREATE TABLE IF NOT EXISTS `B` (
26`id` int(11) NOT NULL,
27`num` int(6) NOT NULL,
28`seq` int(4) NOT NULL,
29`typ` varchar(10) NOT NULL,
30`art` int(6) NOT NULL,
31`art_x` varchar(17) NOT NULL,
32`cod` int(3) NOT NULL,
33`descr` text NOT NULL,
34`qty` decimal(11,4) NOT NULL,
35`um` varchar(2) NOT NULL,
36`val` decimal(12,2) NOT NULL,
37`sc1` decimal(4,1) NOT NULL,
38`sc2` decimal(4,1) NOT NULL,
39`sc3` decimal(4,1) NOT NULL,
40`iv` int(3) NOT NULL,
41`tot` decimal(12,2) NOT NULL DEFAULT '0.00',
42`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
43`deleted` tinyint(1) NOT NULL DEFAULT '0',
44KEY `idseq` (`id`,`seq`)
45) ENGINE=MyISAM DEFAULT CHARSET=latin1;
46
47table A
48id | num | docnr | docy |
491 | 100 | 563 | abc |
502 | 50 | 9978 | abc |
513 | 150 | 56 | abc |
524 | 150 | 679 | abc |
535 | 150 | 67 | def |
546 | 150 | 12 | def |
55
56table B
57id | seq | toti
581 | 1 | 10
592 | 2 | 15
602 | 3 | 5
614 | 4 | 10
623 | 5 | 25
632 | 6 | 567
643 | 7 | 2354
654 | 8 | 768
661 | 88 | 456
671 | 33 | 30
681 | 35 | 89
694 | 31 | 4543
705 | 77 | 223
715 | 9 | 34
726 | 13 | 156
736 | 63 | 6758
745 | 93 | 6
756 | 53 | 22
76
77SUM of all B.toti with id=1 -> 585
78SUM of all B.toti with id=2 -> 587
79SUM of all B.toti with id=3 -> 2379
80SUM of all B.toti with id=4 -> 5321
81SUM of all B.toti with id=5 -> 263
82etc..