SQL多表关联查询报错Unknown column 'a.dev_act_id'，求排查原因

编写的SELECT查询语句如下：

SELECT
  `a.dev_act_id`,
  `a.dev_act_code`,
  `a.dev_act_desc`,
  `a.dev_act_type`,
  `a.lang_code`,
  `pa.dev_plan_act_id`,
  `pa.action_status`,
  `pa.action_expiration`,
  `cb.competence_id`,
  `cb.credits` AS `avail_credits`,
  `w.credits` AS `settled_credits`
FROM
  `pbq_idp_plan_actions` AS pa
  INNER JOIN `pbq_idp_dev_actions` AS a ON `pa.dev_act_id` = `a.dev_act_id`
  INNER JOIN `pbq_idp_credit_bags` AS cb ON `pa.dev_plan_act_id` = `cb.dev_plan_act_id`
  INNER JOIN `pbq_idp_wallets` AS w ON `a.dev_act_id` = `w.dev_act_id`
WHERE
  `pa.dev_plan_id` = 0
ORDER BY
  `cb.competence_id`

执行后出现错误：

WordPress database error: [Unknown column 'a.dev_act_id' in 'field list']

相关表结构如下：

pbq_idp_dev_actions（别名a） #

CREATE TABLE `pbq_idp_dev_actions` (
  `dev_act_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wallet_id` bigint(20) unsigned NOT NULL,
  `dev_act_code` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `dev_act_desc` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `dev_act_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `lang_code` varchar(7) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  PRIMARY KEY (`dev_act_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

pbq_idp_plan_actions（别名pa） #

CREATE TABLE `pbq_idp_plan_actions` (
  `dev_plan_act_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dev_act_id` bigint(20) unsigned NOT NULL,
  `dev_plan_id` bigint(20) unsigned NOT NULL,
  `action_status` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `not_earlier` datetime DEFAULT NULL,
  `deadline` datetime DEFAULT NULL,
  PRIMARY KEY (`dev_plan_act_id`),
  KEY `dev_act_id` (`dev_act_id`,`dev_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

pbq_idp_credit_bags（别名cb） #

CREATE TABLE `pbq_idp_credit_bags` (
  `credit_bag_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dev_plan_act_id` bigint(20) unsigned NOT NULL,
  `competence_id` varchar(4) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `credits` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`credit_bag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

pbq_idp_wallets（别名w） #

CREATE TABLE `pbq_idp_wallets` (
  `wallet_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dev_act_id` bigint(20) unsigned NOT NULL,
  `competence_id` varchar(4) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `credits` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`wallet_id`),
  KEY `dev_act_id` (`dev_act_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

排查解决方向： #

1. 字段引用的引号错误：查询中a.dev_act_id被整体包裹在反引号中，MySQL会将其识别为一个名为a.dev_act_id的独立字段，而非别名a下的dev_act_id字段。
   修正后的两种写法：

   -- 写法1：去掉不必要的反引号
   SELECT
     a.dev_act_id,
     a.dev_act_code,
     -- 其他字段同理
   

   -- 写法2：正确拆分反引号
   SELECT
     `a`.`dev_act_id`,
     `a`.`dev_act_code`,
     -- 其他字段同理
   

2. 验证执行语句一致性：确认实际执行的SQL和你编写的语句完全一致，没有因复制、转义等操作导致别名或字段名被篡改。
3. WordPress环境验证：如果是通过WordPress的$wpdb执行查询，可尝试直接在数据库客户端（如phpMyAdmin）中执行修正后的SQL，排查是否是WordPress的查询转义逻辑导致的问题。

内容的提问来源于stack exchange，提问作者Dario de Judicibus