鉴于这两个表,其中containers
中的idcontype
和idinfcontype
与contypes
中的id
相关。我需要一个查询,该查询返回一个containers
行,其中包含idcontype
和idinfcontype
的descrip
列。
--
-- Table structure for table `containers`
--
`id` int(11) NOT NULL AUTO_INCREMENT,
`idarticle` int(11) NOT NULL,
`idzone` int(3) NOT NULL DEFAULT '1',
`idcontype` int(11) NOT NULL DEFAULT '1',
`idinfcontype` int(11) NOT NULL DEFAULT '0',
--
-- Table structure for table `contypes`
--
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` char(1) COLLATE utf8_spanish_ci NOT NULL,
`code` varchar(2) COLLATE utf8_spanish_ci NOT NULL,
`level` tinyint(1) NOT NULL,
`descrip` varchar(16) COLLATE utf8_spanish_ci NOT NULL,
containers
中的idcontype
和idinfcontype
与contypes
中的id
有关 idcontype
总是与contypes
中的id
有关,但idinfcontype
可能0
此查询生成三行,其中包含重复行
SELECT DISTINCT * FROM
(SELECT `containers`.`id`,`idarticle`,`idcontype`,`descrip` FROM `containers` JOIN `contypes` ON containers.idcontype=contypes.id
UNION
SELECT `containers`.`id`,`idarticle`,`idinfcontype`,`descrip` FROM `containers` JOIN `contypes` ON containers.idinfcontype=contypes.id
) CT
Rows: 3
id idarticle idcontype descrip
1 2 1 PACKAGE
2 2 2 BOX
2 2 1 PACKAGE
经过一番挣扎,此查询产生所需的结果:
SELECT * FROM `containers`
JOIN
(SELECT DISTINCT id AS idc FROM
(SELECT `containers`.`id`,`idarticle`,`idcontype`,`descrip` FROM `containers` JOIN `contypes` ON containers.idcontype=contypes.id
UNION
SELECT `containers`.`id`,`idarticle`,`idinfcontype`,`descrip` FROM `containers` JOIN `contypes` ON containers.idinfcontype=contypes.id
) CT ) CTD
ON containers.id=idc
id idarticle idcontype descrip
1 2 1 PACKAGE
2 2 2 BOX
有没有更简单的方法可以做到这一点?
通常,你会用left join
s来解决这个问题:
SELECT c.*, ct.descrip, ctinf.descrip as infdescrip
FROM containers c LEFT JOIN
contypes ct
on c.idcontype = ct.id LEFT JOIN
contypes ctinf
on c.idinfcontype = ctinf.id;
除了left join
之外,此查询:
- 使用表别名。 在这种情况下需要这些表,因为查询在
from
子句中具有两次相同的表。 - 使用列别名来区分两列。
- 不使用反引号。 这些不是必需的,我认为它们使查询更难阅读和编写。
可以使用相关名多次联接到同一个表:
SELECT `containers`.`id`,`idarticle`,`idcontype`,`contypes`.`descrip`, 'idinfcontype', c2.'descrip'
FROM `containers`
JOIN `contypes` ON containers.idcontype='contypes'.contypes.id
JOIN `contypes` c2 ON containers.idinfcontype=c2.id
第二个类型表指定了相关名 c2。第一个 contypes 表没有提供相关名,因此使用表名本身作为相关名。后者也适用于容器表。