我有一个相当直截了当的问题,我不明白为什么CONCAT不起作用。
我是JOINING表格domainsclient_contentdomain_status并试图insert intovhosts。在我使用CONCAT组合domain_status.status_directoryclient_configuration.user_file之前,所有操作都按预期工作。此时,我分配的别名是否超出范围?

                      ----------------------------------------
domains               |  domain   |   enabled   |   status   |
                      ----------------------------------------

                      -------------------------------------
client_configuration  |   contractor_id   |   user_file   |
                      -------------------------------------

                      ---------------------------------
domain_status         |   id   |   status_directory   |
                      ---------------------------------

                      ------------------------------------------------------------
vhosts                |   site_name   |   directory   |   enabled   |   status   |
                      ------------------------------------------------------------


INSERT INTO vhosts (`site_name`, `directory`, `enabled`, `status`)
    SELECT d.domain, CONCAT(`s.status_directory`,'/',`c.user_file`), d.enabled, d.status
    FROM domains d

    LEFT JOIN client_configuration c
        ON d.contractor_id = c.contractor_id
    LEFT JOIN domains_status s
        ON d.status = s.id
    WHERE d.domain = 'example.com';

据我所知,我应该能够以这种方式使用CONCAT,但我得到一个MySQL错误:
Error Code: 1054. Unknown column 's.status_directory' in 'field list'

如果我不能像这样使用CONCAT。。怎么回事?

最佳答案

问题是您已经用与列名相同的反引号将表别名括起来。从那些引号中去掉表别名。请改为:

INSERT INTO vhosts
(
  `site_name`,
  `directory`,
  `enabled`,
  `status`
)
SELECT
  d.domain,
  CONCAT(s.`status_directory`,'/', c.`user_file`),
  d.enabled,
  d.status
FROM domains d
LEFT JOIN client_configuration c
  ON d.contractor_id = c.contractor_id
LEFT JOIN domains_status s
  ON d.status = s.id
WHERE d.domain = 'example.com';

关于mysql - MySQL CONCAT找不到带别名的表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44268829/

10-12 12:28
查看更多