CREATE TABLE `t1` ( `f1` char(2) default NULL, `f2` char(2) default NULL, `f3` tinyint(1) unsigned default NULL, `f4` int(8) unsigned default NULL, KEY `f1` (`f1`) ) TYPE=MyISAMINSERT INTO t1 (f1, f2, f3, f4) VALUES ('A', 'B', 1, 20021112); INSERT INTO t1 (f1, f2, f3, f4) VALUES ('A1', 'A2', 2, 20021114); CREATE TABLE `t2` ( `f1` char(2) default NULL, `f2` char(2) default NULL, `f3` tinyint(1) unsigned default NULL, `f4` int(8) unsigned default NULL, KEY `NewIndex` (`f1`) ) TYPE=MyISAMINSERT INTO t2 (f1, f2, f3, f4) VALUES ('A', 'B', 2, 20021113); CREATE TABLE `t3` ( `id` tinyint(3) unsigned NOT NULL default '0', `f1` int(8) unsigned default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAMINSERT INTO t3 (id, f1) VALUES (1, 20021112); INSERT INTO t3 (id, f1) VALUES (2, 20021113); INSERT INTO t3 (id, f1) VALUES (3, 20021114);SELECT LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4, IFNULL(t1.f1,t2.f1) AS f1, IFNULL(t1.f2,t2.f2)AS f2, SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3 FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4 LEFT JOIN t2 ON t3.f1 = t2.f4 GROUP BY 2;
+----------+------+------+------+ | f4 | f1 | f2 | f3 | +----------+------+------+------+ | 20021113 | A | B | 02 | | 20021114 | A1 | A2 | 02 | +----------+------+------+------+ 2 rows in set (0.01 sec) 要求 t3 为主表, 在 t3 中不能有"单号"多与其它二个表的总和 否则会出现如下情况INSERT INTO t3 (id, f1) VALUES (4, 20021115);SELECT LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4, IFNULL(t1.f1,t2.f1) AS f1, IFNULL(t1.f2,t2.f2)AS f2, SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3 FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4 LEFT JOIN t2 ON t3.f1 = t2.f4 GROUP BY 2;
+----------+------+------+------+ | f4 | f1 | f2 | f3 | +----------+------+------+------+ | NULL | NULL | NULL | NULL | | 20021113 | A | B | 02 | | 20021114 | A1 | A2 | 02 | +----------+------+------+------+ 3 rows in set (0.01 sec)
“在表3 单号最大的
表1或者是表2中的数材料数量
表1的材料 规格 与表2的相同”
读着有点绕口。
从表1或者表2中选择
材料的数量
条件是
择择的材料的单号
在表3中是最大的
`f1` char(2) default NULL,
`f2` char(2) default NULL,
`f3` tinyint(1) unsigned default NULL,
`f4` int(8) unsigned default NULL,
KEY `f1` (`f1`)
) TYPE=MyISAMINSERT INTO t1 (f1, f2, f3, f4) VALUES ('A', 'B', 1, 20021112);
INSERT INTO t1 (f1, f2, f3, f4) VALUES ('A1', 'A2', 2, 20021114);
CREATE TABLE `t2` (
`f1` char(2) default NULL,
`f2` char(2) default NULL,
`f3` tinyint(1) unsigned default NULL,
`f4` int(8) unsigned default NULL,
KEY `NewIndex` (`f1`)
) TYPE=MyISAMINSERT INTO t2 (f1, f2, f3, f4) VALUES ('A', 'B', 2, 20021113);
CREATE TABLE `t3` (
`id` tinyint(3) unsigned NOT NULL default '0',
`f1` int(8) unsigned default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAMINSERT INTO t3 (id, f1) VALUES (1, 20021112);
INSERT INTO t3 (id, f1) VALUES (2, 20021113);
INSERT INTO t3 (id, f1) VALUES (3, 20021114);SELECT LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4,
IFNULL(t1.f1,t2.f1) AS f1,
IFNULL(t1.f2,t2.f2)AS f2,
SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3
FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4
LEFT JOIN t2 ON t3.f1 = t2.f4
GROUP BY 2;
+----------+------+------+------+
| f4 | f1 | f2 | f3 |
+----------+------+------+------+
| 20021113 | A | B | 02 |
| 20021114 | A1 | A2 | 02 |
+----------+------+------+------+
2 rows in set (0.01 sec) 要求 t3 为主表, 在 t3 中不能有"单号"多与其它二个表的总和
否则会出现如下情况INSERT INTO t3 (id, f1) VALUES (4, 20021115);SELECT LEFT(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),8) AS f4,
IFNULL(t1.f1,t2.f1) AS f1,
IFNULL(t1.f2,t2.f2)AS f2,
SUBSTRING(MAX(CONCAT(t3.f1,LPAD(IFNULL(t1.f3,t2.f3),2,'0'))),9)AS f3
FROM t3 LEFT JOIN t1 ON t3.f1 = t1.f4
LEFT JOIN t2 ON t3.f1 = t2.f4
GROUP BY 2;
+----------+------+------+------+
| f4 | f1 | f2 | f3 |
+----------+------+------+------+
| NULL | NULL | NULL | NULL |
| 20021113 | A | B | 02 |
| 20021114 | A1 | A2 | 02 |
+----------+------+------+------+
3 rows in set (0.01 sec)