MySQL 替换

Question

        +-----------------------------+
        | tables                      |
        +-----------------------------+
        | Date                        |
        | IP                          |
        | Location                    |
        | UserAgent                   |
        +-----------------------------+

为了简单起见，假设这四个表只有两列:ID(int)、name(VARCHAR)

然后我有一个名为 access_log 的表，我在其中仅存储其他四个表中的项目 ID

        +---------+---------+------+-----+---------+----------------+
        | Field   | Type    | Null | Key | Default | Extra          |
        +---------+---------+------+-----+---------+----------------+
        | id      | int(11) | NO   | PRI | NULL    | auto_increment |
        | ip_id   | int(11) | NO   |     | NULL    |                |
        | ua_id   | int(11) | NO   |     | NULL    |                |
        | path_id | int(11) | NO   |     | NULL    |                |
        | date_id | int(11) | NO   |     | NULL    |                |
        +---------+---------+------+-----+---------+----------------+

假设我想从此 access_log 表中选择所有内容，并将 ids 替换为我在其他四个表中的列 NAME。我怎样才能实现这一目标？

Answer 1

您需要加入所有四个表:

SELECT      access_log.id,
            `date`.name AS date_name, 
            ip.name AS ip_name, 
            location.name AS location_name, 
            useragent.name AS useragent_name
FROM        access_log
OUTER JOIN  `date` ON `date`.id = access_log.date_id
OUTER JOIN  ip ON ip.id = access_log.ip_id
OUTER JOIN  location ON location.id = access_log.path_id
OUTER JOIN  useragent ON useragent.id = access_log.ua_id