我有一个相当直截了当的问题,我不明白为什么CONCAT
不起作用。
我是JOINING
表格domains
client_content
和domain_status
并试图insert into
vhosts
。在我使用CONCAT
组合domain_status.status_directory
和client_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/