首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >如何在临时表上选择多个IN条件?

如何在临时表上选择多个IN条件?
EN

Stack Overflow用户
提问于 2019-03-27 08:52:12
回答 1查看 23关注 0票数 0

问题

我想清理一个混乱的数据库,并将引用替换为重复条目。在这个定制的例子(我的更复杂)中,我有两个表:

  • 章鱼
  • 颜色

我们知道:

  • 章鱼的颜色。
  • colors包含重复项
  • 有些章鱼的颜色可能与其他章鱼相同,但color_id不同。

解决这个问题的方法涉及到TEMPORARY表。为了避免这一错误:

代码语言:javascript
运行
复制
Can't Reopen Table 'duplicates'

我只是多次复制我的TEMPORARY表:

代码语言:javascript
运行
复制
CREATE TEMPORARY TABLE duplicates1 SELECT * FROM duplicates;
CREATE TEMPORARY TABLE duplicates2 SELECT * FROM duplicates;

问题

我想避免克隆TEMPORARY表。

数据

代码语言:javascript
运行
复制
CREATE TABLE `test`.`octopuses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `color_id` INT NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `test`.`colors` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

带有重复颜色的:

代码语言:javascript
运行
复制
INSERT INTO colors (name) VALUES
    ('cream'), ('sepia'), ('daffodil'), ('lipstick'),                        
    ('lipstick'), ('garnet'), ('flamingo'), ('navy'),                        
    ('chartreuse'), ('garnet'), ('flamingo'), ('juniper'),                             
    ('flint'), ('flint'), ('charcoal'), ('garnet');                     

还有一些章鱼:

代码语言:javascript
运行
复制
INSERT INTO octopuses (name, color_id) VALUES
    ('Bubbles', 1), ('Inky', 8), ('Octavius', 1),
    ('Sir Inks-A-Lot', 7), ('Octavia', 16), ('Kraken', 6),
    ('Oncho', 15), ('Big Floppy Sea Spider', 14), ('Calamari', 2),
    ('Scuba Doo', 13), ('Squidward Tentacles', 5), ('Wiggleton', 9),
    ('Cthulhu', 2), ('Octopussy', 3), ('Triton', 10), 
    ('Doctor Octopus', 11), ('Billy The Squid', 4), ('Stretch', 12);

例句

为了解决这个问题,我首先创建一个重复的列表:

代码语言:javascript
运行
复制
CREATE TEMPORARY TABLE duplicates SELECT
    *, COUNT(*) AS count
FROM
    colors
GROUP BY name
HAVING count > 1;

下面是:

代码语言:javascript
运行
复制
mysql> select * FROM duplicates;
+----+----------+-------+
| id | name     | count |
+----+----------+-------+
|  4 | lipstick |     2 |
|  6 | garnet   |     3 |
|  7 | flamingo |     2 |
| 13 | flint    |     2 |
+----+----------+-------+

然后,我想创建一个相应的表,其中我有一个副本的id和要替换的id

代码语言:javascript
运行
复制
CREATE TEMPORARY TABLE duplicates1 SELECT * FROM duplicates;
CREATE TEMPORARY TABLE duplicates2 SELECT * FROM duplicates;

CREATE TEMPORARY TABLE corresponding SELECT
    id, name,
    (SELECT
            id
        FROM
            duplicates2
        WHERE
            duplicates2.name = colors.name) AS first_id
FROM
    colors
WHERE
    name IN (SELECT
            name
        FROM
            duplicates)
        AND id NOT IN (SELECT
            id
        FROM
            duplicates1)
ORDER BY name ASC;

这里的内容:

代码语言:javascript
运行
复制
mysql> SELECT * FROM corresponding;
+----+----------+----------+
| id | name     | first_id |
+----+----------+----------+
| 11 | flamingo |        7 |
| 14 | flint    |       13 |
| 10 | garnet   |        6 |
| 16 | garnet   |        6 |
|  5 | lipstick |        4 |
+----+----------+----------+

然后我只需更新octopuses表:

代码语言:javascript
运行
复制
CREATE TEMPORARY TABLE corresponding1 SELECT * FROM corresponding;

UPDATE octopuses
SET
    color_id = (SELECT
            first_id
        FROM
            corresponding1
        WHERE
            corresponding1.id = color_id)
WHERE
    color_id IN (SELECT
            id
        FROM
            corresponding)

最后,我删除了这些副本:

代码语言:javascript
运行
复制
DELETE FROM colors WHERE id IN (SELECT id FROM corresponding);

小结

这个示例可能不是最好的说明我的问题的例子,但在这里,我希望避免克隆临时表,并找到一种在TEMPORARY表上使用多个TEMPORARY条件进行选择的方法。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-03-27 09:14:39

试着从另一个角度去思考。

你可以这样做:

代码语言:javascript
运行
复制
UPDATE octopuses
        INNER JOIN
    (SELECT 
        *,
            (SELECT 
                    id
                FROM
                    colors
                WHERE
                    colors.name = (SELECT 
                            name
                        FROM
                            colors
                        WHERE
                            color_id = colors.id)
                LIMIT 1) AS first_color_id
    FROM
        octopuses
    HAVING color_id <> first_color_id) AS DUP ON dup.color_id = octopuses.color_id 
SET 
    octopuses.color_id = first_color_id
WHERE
    octopuses.color_id <> first_color_id;


CREATE TEMPORARY TABLE to_delete SELECT id FROM colors WHERE NOT EXISTS (
    SELECT id FROM octopuses WHERE color_id = colors.id
);

DELETE FROM colors WHERE id IN (SELECT id FROM to_delete);

所以你的问题的答案是:

每当您需要克隆一个临时表时,请三思而后行,您将找到另一种不用重新打开临时表的方法!

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55373101

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档