我已经在共享名为id的公共键的三个表上编写了一个查询。当从内部选择语句返回值时,外部选择无法解析 id。我查看了堆栈溢出中的帖子,如下所示:[1052:字段列表中的列"id"不明确
它建议我们使用别名来消除问题的歧义,但它似乎对我不起作用。以下是我在 mysql 中的查询。
SELECT
e2.`exchange_code`
e1.`id`,
e1.`error_time`,
e1.`last_update_time`
FROM
`exchange_error_details` e1,
`exchange_products` e2
WHERE e1.`id` IN
(SELECT
id
FROM
exchange_products)
AND e1.`id` NOT IN
(SELECT
id
FROM
mdd_suppressed_keys)
AND e1.`clear_time` IS NULL
编辑:
exchange_error_details
id error_time clear_time last_update_time clear_ref_ticker
ADF.10052.1.Q.FEED 2014-11-25 10:36:25 N 2014-11-25 02:36:25 THD
ADF.10052.1.Q.FEED 2014-11-28 13:50:06 2014-11-28 13:50:37 2014-11-28 13:49:51 AMLP
ADF.10052.1.Q.FEED 2014-11-28 13:53:08 2014-11-28 13:53:38 2014-11-28 13:52:44 ONVO
ADF.10052.1.Q.FEED 2014-11-28 13:55:09 2014-11-28 13:56:10 2014-11-28 13:54:54 ACAD
ADF.10052.1.Q.FEED 2014-11-28 13:58:10 2014-11-28 13:58:41 2014-11-28 13:57:56 HABT
exchange_products:
id exchange_code iso_code issue_type
ADF.10052.1.Q.FEED 10052 ADF 1
ADF.10052.1.T.FEED 10052 ADF 1
ADF.10074.1.T.FEED 10074 ADF 1
ADF.11099.1.T.FEED 11099 ADF 1
ADF.11099.14.T.FEED 11099 ADF 14
mdd_suppressed_keys
id user date
ADF.10052.1.Q.FEED droy 2015-03-09 03:16:22
HAM.17.41.Q.FEED droy 2015-03-09 03:16:26
HAM.17.41.T.FEED droy 2015-03-09 03:16:30
IND.114.1.T.FEED droy 2015-03-09 03:16:22
SWX.4.41.Q.FEED droy 2015-03-09 03:16:22
错误信息
1 queries executed, 0 success, 1 errors, 0 warnings
Query: select e2.`exchange_code`, e1.`id`, e1.`error_time`, e1.`last_update_time` from `exchange_error_details` e1, `exchange_products`...
Error Code: 1052
Column 'id' in field list is ambiguous
Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.388 sec
我为什么写这个查询:
我希望仅返回 id 字段与 exchange_products 表中的字段匹配的 exchange_error_details 表中的值,并排除mdd_suppressed_keys表中存在的值。
谢谢!
如果我的结构正确,您的查询必须LEFT JOIN
:)
尝试:
SELECT
e2.`exchange_code`
e1.`id`,
e1.`error_time`,
e1.`last_update_time`
FROM
`exchange_error_details` e1
LEFT JOIN
`exchange_products` e2
ON
e1.id = e2.id
WHERE e1.`id` IN
(SELECT
id
FROM
exchange_control_info)
AND e1.`id` NOT IN
(SELECT
id
FROM
mdd_suppressed_keys)
AND e1.`clear_time` IS NULL
也许是这样的:
WHERE e1.`id` IN
(SELECT
exchange_control_info.id
FROM
exchange_control_info)
AND e1.`id` NOT IN
(SELECT
mdd_suppressed_keys.id
FROM
mdd_suppressed_keys)
AND e1.`clear_time` IS NULL