gpt4 book ai didi

mysql - 不能在 INSERT MySQL 中使用 UNION?

转载 作者:可可西里 更新时间:2023-11-01 07:59:29 26 4
gpt4 key购买 nike

我有一个工作正常的选择查询。但是,当我将其包装到 INSERT 语句中时,出现错误。

错误 1064 - 您的 SQL 语法有误;查看与您的 MySQL 服务器版本对应的手册,了解在 '(SELECT DISTINCT NULL AS id,NULL AS core_value_id,NULL AS translation_id< 附近使用的正确语法' 在第 3 行

我可以通过为每个源表使用 1 个插入来解决这个问题。但是有没有办法用一个查询来完成呢?为什么这不起作用?

这是查询。

INSERT INTO `some_table`
(
(SELECT DISTINCT
NULL AS `id`,
NULL AS `core_value_id`,
NULL AS `translation_id`,
t1.`upc` AS `source_value`,
t1.`upc` AS `value`,
COUNT(*) AS `count`
FROM `source_table_1` t1
GROUP BY `upc`)

UNION ALL
(SELECT DISTINCT
NULL,NULL,NULL,
t1.`upc`,
t1.`upc`,
COUNT(*) AS `count`
FROM `source_table_2` t1
GROUP BY `upc`
)
ORDER BY `count` DESC
)

这是表定义:

CREATE TABLE `some_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`core_value_id` int(11) DEFAULT NULL,
`translation_id` int(11) DEFAULT NULL,
`source_value` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `core_value_id` (`core_value_id`),
KEY `translation_id` (`translation_id`),
KEY `source_value` (`source_value`),
KEY `value` (`value`),
KEY `count` (`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

source_tables 看起来像这样:

CREATE TABLE `source_table_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`upc` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

upc 列的数据如下:

123456789012
123456789013
123456789014
123456789015
123456789016
123456789017

最佳答案

我的猜测是您在单个选择的顶部缺少一个 SELECT 子句。此外,括号不合适。试试这个:

INSERT INTO `some_table`
SELECT *
FROM
(
SELECT DISTINCT NULL AS `id`, NULL AS `core_value_id`,
NULL AS `translation_id`, t1.`upc` AS `source_value`,
t1.`upc` AS `value`, COUNT(*) AS `count`
FROM `source_table_1` t1
GROUP BY `upc`

UNION ALL

SELECT DISTINCT NULL, NULL, NULL, t1.`upc`, t1.`upc`,
COUNT(*) AS `count`
FROM `source_table_2` t1
GROUP BY `upc`
) AS dt
ORDER BY `count` DESC

关于mysql - 不能在 INSERT MySQL 中使用 UNION?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13152425/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com