时间戳

首页 > 解决方案 > 一对多，显示所有关系，包括缺失的关系

mysql - 一对多，显示所有关系，包括缺失的关系

问题描述

我有 3 张桌子：1 张桌子tags，1张桌子languages和 1张桌子translations。

该translations表具有指向该表tags和该languages表的外键 id 列。

表1中的translations标签可以关联多种语言，其结构如下：

id | tag | language | translation
1    1     1          "hello"
2    1     2          "olá"
3    1     3          "bonjour"
4    2     1          "world"
5    3     1          "dog"

我不想要的是加入 3 个表的方法，但如果 atag没有与所有语言相关联，我希望翻译和其他字段显示为 null：我已经尝试了所有可能的连接组合。

在示例的情况下，最多有 3 种语言，标签 2 和 3 仅与一种语言相关。

我正在使用 MySQL。

这是表格的代码：

CREATE TABLE `tags` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'ascii_bin',
    `isActive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `tag` (`tag`) USING BTREE
)
COLLATE='ascii_bin'
ENGINE=InnoDB
AUTO_INCREMENT=15
;

CREATE TABLE `languages` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `language` VARCHAR(5) NOT NULL DEFAULT '' COLLATE 'ascii_bin',
    `tag` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'ascii_bin',
    `nativeName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `isActive` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `code` (`language`) USING BTREE,
    UNIQUE INDEX `name` (`name`) USING BTREE,
    UNIQUE INDEX `tag` (`tag`) USING BTREE,
    CONSTRAINT `FK_languages_tags` FOREIGN KEY (`tag`) REFERENCES `hello`.`tags` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9
;

CREATE TABLE `translations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1',
    `language` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1',
    `translation` VARCHAR(5000) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `createdAt` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updatedAt` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK_translations_tags` (`tag`) USING BTREE,
    INDEX `FK_translations_languages` (`language`) USING BTREE,
    CONSTRAINT `FK_translations_languages` FOREIGN KEY (`language`) REFERENCES `hello`.`languages` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT `FK_translations_tags` FOREIGN KEY (`tag`) REFERENCES `hello`.`tags` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=InnoDB
AUTO_INCREMENT=72
;

这是我到目前为止所得到的：

SELECT
`translations`.`tag` AS `tagId`,
`tags`.`tag`,
`tags`.`isActive` AS `tagIsActive`,
`translations`.`language` AS `languageId`,
`languages`.`language`,
`languages`.`tag` AS `languageTagId`,
--
(SELECT `tag` FROM `tags` WHERE `id` = `languages`.`tag`) AS `languageTag`,
--
`languages`.`nativeName` AS `languageNativeName`,
`languages`.`isActive` AS `languageIsActive`,
`translations`.`id` AS `translationId`,
`translations`.`translation`
FROM `translations`
--
LEFT OUTER JOIN `tags`
ON `tags`.`id` = `translations`.`tag`
--
LEFT OUTER JOIN `languages`
ON `languages`.`id` = `translations`.`language`
--
ORDER BY `tags`.`tag` ASC,
`languages`.`language` ASC;

就像我说的，如果标签与语言不匹配all，我至少需要将翻译显示为 null 或空字符串。

标签： mysqlsqljoin

解决方案

SELECT *
FROM tags
CROSS JOIN languages
LEFT JOIN translations ON translations.tag = tags.id
                      AND translations.language = languages.id

小提琴

推荐阅读