MySQL:连接两个表并根据连接表中的两列填充字段

Question

我正在开发现有的画廊系统，我想为它制作一些多语言的字段(标题、来源、描述)。一个不同的表“translations”用于存储这些翻译后的文本。此表的“lang”列存储语言代码(“fr”、“en”、“de”)。 “file_id”列存储翻译所涉及的文件。

我正在寻找 MySQL 中输出记录的最佳方式，该文件包含所有列，包括所有翻译为“title_fr、title_en、...、description_de”列。

表结构:

    CREATE TABLE `files` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `src` varchar(128) NOT NULL default '',
      `name` varchar(128) NOT NULL default '',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

    CREATE TABLE `translations` (
      `id` int(10) unsigned NOT NULL,
      `file_id` int(10) unsigned NOT NULL,
      `lang` char(2) NOT NULL default '',
      `title` varchar(255) NOT NULL default '',
      `source` varchar(32) default '',
      `description` text,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

我尝试了两个想法，但没有一个奏效。

1) 第一个想法 是使用 VIEW。由于缺少对共享主机的访问权限而失败:

GRANT CREATE VIEW ON *.* TO 'the-user@the-host' IDENTIFIED BY PASSWORD  'here-the-password';

CREATE VIEW French AS
  SELECT f.id, title AS title_fr, source AS source_fr, description AS description_fr
  FROM files AS f LEFT OUTER JOIN translations AS t ON t.file_id = f.id WHERE (f.id=11 AND t.lang='fr');

CREATE VIEW English AS
  SELECT f.id, title AS title_en, source AS source_en, description AS description_en
  FROM files AS f LEFT OUTER JOIN translations AS t ON t.file_id = f.id WHERE (f.id=11 AND t.lang='en');

CREATE VIEW German AS
  SELECT f.id, title AS title_de, source AS source_de, description AS description_de
  FROM files AS f LEFT OUTER JOIN translations AS t ON t.file_id = f.id WHERE (f.id=11 AND t.lang='de');

SELECT * FROM  files AS f
  LEFT OUTER JOIN French ON f.id=French.id
  LEFT OUTER JOIN English ON f.id=English.id
  LEFT OUTER JOIN German ON f.id=German.id
  GROUP BY f.id;

2) 第二个想法的灵感来自this thread中的例子。使用数据透视表。就我而言，我不能使用 COUNT() 并且以下 SQL 查询无效，但给出了想法。抱歉，有一些冗余。

SELECT f.*,
  (CASE
    WHEN t.`lang`='fr'
    THEN t.title
    ELSE NULL
   ) AS title_fr,
  (CASE
    WHEN t.`lang`='en'
    THEN t.title
    ELSE NULL
   ) AS title_en,
  (CASE
    WHEN t.`lang`='de'
    THEN t.title
    ELSE NULL
   ) AS title_de,
  (CASE
    WHEN t.`lang`='fr'
    THEN t.source
    ELSE NULL
   ) AS source_fr,
  (CASE
    WHEN t.`lang`='en'
    THEN t.source
    ELSE NULL
   ) AS source_en,
  (CASE
    WHEN t.`lang`='de'
    THEN t.source
    ELSE NULL
   ) AS source_de 
  (CASE
    WHEN t.`lang`='fr'
    THEN t.description
    ELSE NULL
   ) AS description_fr,
  (CASE
    WHEN t.`lang`='en'
    THEN t.description
    ELSE NULL
   ) AS description_en,
  (CASE
    WHEN t.`lang`='de'
    THEN t.description
    ELSE NULL
   ) AS description_de
FROM files AS f WHERE id=11
LEFT OUTER JOIN
translations AS t
ON f.id=t.file_id
GROUP BY id

我还阅读了有关 CONCAT 和 CONCAT_WS 的内容，但它们不是我要找的，因为我想让进一步的 PHP 处理尽可能简单。

Answer 1

虽然你不能使用count，但你可以使用max，所以试试这个查询，它应该是你要找的:

SELECT 
  f.id, f.src, f.name,
  MAX(CASE WHEN t.lang='fr' THEN t.title ELSE NULL END) AS title_fr,
  MAX(CASE WHEN t.lang='en' THEN t.title ELSE NULL END) AS title_en,
  MAX(CASE WHEN t.lang='de' THEN t.title ELSE NULL END) AS title_de,
  MAX(CASE WHEN t.lang='fr' THEN t.source ELSE NULL END) AS source_fr,
  MAX(CASE WHEN t.lang='en' THEN t.source ELSE NULL END) AS source_en,
  MAX(CASE WHEN t.lang='de' THEN t.source ELSE NULL END) AS source_de,
  MAX(CASE WHEN t.lang='fr' THEN t.description ELSE NULL END) AS description_fr,
  MAX(CASE WHEN t.lang='en' THEN t.description ELSE NULL END) AS description_en,
  MAX(CASE WHEN t.lang='de' THEN t.description ELSE NULL END) AS description_de
FROM files AS f 
LEFT OUTER JOIN translations AS t ON f.id=t.file_id
WHERE f.id=11
GROUP BY f.id, f.src, f.name;