文章/答案/技术大牛

发布

社区首页 >问答首页 >mysql :删除主键columan中父id匹配的行

问mysql :删除主键columan中父id匹配的行
EN

Database Administration用户

提问于 2017-03-27 05:15:47

回答 2查看 40关注 0票数 0

我不知道这个问题的标题应该是什么。

我有两个表，模式定义如下：

表1 (organization_master)

CREATE TABLE `organization_master` (  `organization_id` int(11) NOT NULL,  `parent_organization_id` int(11) DEFAULT NULL,  `organization_name` varchar(150) NOT NULL,  `c_user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `organization_master` (`organization_id`, `parent_organization_id`, `organization_name`, `c_user_id`) VALUES
(119, NULL, 'Patel-Apps', 220),
(183, 119, 'Patel-Apps sub 1', 220),
(184, 119, 'Patel-Apps sub 2', 220),
(250, 247, 'Patel-Apps2', 222);
ALTER TABLE `organization_master`  ADD PRIMARY KEY (`organization_id`),  ADD KEY `organization_name` (`organization_name`),  ADD KEY `c_user_id` (`c_user_id`);
ALTER TABLE `organization_master`  MODIFY `organization_id` int(11) NOT NULL AUTO_INCREMENT;

表2 (organization_assigned_user)：

CREATE TABLE `organization_assigned_user` (  `organization_assigned_user_id` int(11) NOT NULL,  `organization_id` int(11) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `organization_assigned_user` (`organization_assigned_user_id`, `organization_id`, `user_id`) VALUES (29, 250, 219),(30, 250, 220);
ALTER TABLE `organization_assigned_user`  ADD PRIMARY KEY (`organization_assigned_user_id`),  ADD KEY `user_id` (`user_id`),  ADD KEY `organization_id` (`organization_id`);
ALTER TABLE `organization_assigned_user`  MODIFY `organization_assigned_user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

当我运行以下查询时：

SELECT    om.organization_id, 
          om.parent_organization_id, 
          om.organization_name 
FROM      `organization_master` AS om 
LEFT JOIN organization_assigned_user AS oau 
ON        om.organization_id = oau.organization_id 
WHERE     om.c_user_id =220 
OR        oau.user_id =220 
GROUP BY  om.organization_id;

它给我的产出如下：

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
183             119                     Patel-Apps sub 1
184             119                     Patel-Apps sub 2
250             247                     Patel-Apps2

现在，我的问题是，我不希望parent_organization_id存在于organization_id列中的行。即199 parent_organization_id存在于organization_id中。所以我的输出应该如下所示：

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
250             247                     Patel-Apps2

mysql

回答 2

Database Administration用户

回答已采纳

发布于 2017-03-27 12:33:48

试试这个：

SELECT a.organization_id,
    a.parent_organization_id,
    a.organization_name
FROM (
    SELECT om.organization_id , 
            om.parent_organization_id, 
            om.organization_name 
    FROM `organization_master` AS om 
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id 
    WHERE om.c_user_id =220 OR oau.user_id =220 
    GROUP BY om.organization_id
) a 
WHERE a.parent_organization_id NOT IN
(
    SELECT om.organization_id
    FROM `organization_master` AS om 
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id 
    WHERE om.c_user_id =220 OR oau.user_id =220 
    GROUP BY om.organization_id
)
OR a.parent_organization_id IS NULL

如果这对您有用，请考虑将主SELECT的结果(在我的答复中使用两次)存储在临时表中，并加入其中以提高性能。

票数 0

Database Administration用户

发布于 2017-03-27 13:18:29

使用您自己的查询：

SELECT    om.organization_id, 
          om.parent_organization_id, 
          om.organization_name 
FROM      organization_master AS om 
LEFT JOIN organization_assigned_user AS oau 
ON        om.organization_id = oau.organization_id 
WHERE     (om.c_user_id = 220 
           OR 
           oau.user_id = 220)

AND       (om.parent_organization_id NOT IN (SELECT   om1.organization_id
                                            FROM      organization_master AS om1
                                            LEFT JOIN organization_assigned_user AS oau1
                                            ON        om1.organization_id = oau1.organization_id
                                            WHERE     om1.c_user_id = om.c_user_id
                                            OR        oau1.user_id = oau.user_id)
           OR
           om.parent_organization_id IS NULL)

GROUP BY  om.organization_id
;

我使用了一个子查询来获取主查询的所有organization_id：

SELECT    om1.parent_organization_id
FROM      organization_master AS om1
LEFT JOIN organization_assigned_user AS oau1
ON        om1.organization_id = oau1.organization_id
WHERE     om1.c_user_id = 220
OR        oau1.user_id = 220;

| parent_organization_id |
|------------------------|
| 247                    |
| NULL                   |
| 119                    |
| 119                    |

如您所见，它返回一个空行，因此必须通过在WHERE子句中添加另一个条件来处理这一行：

AND       (om.parent_organization_id NOT IN (SELECT   om1.organization_id
                                            FROM      organization_master AS om1
                                            LEFT JOIN organization_assigned_user AS oau1
                                            ON        om1.organization_id = oau1.organization_id
                                            WHERE     om1.c_user_id = om.c_user_id
                                            OR        oau1.user_id = oau.user_id)
           OR
           om.parent_organization_id IS NULL)

这是最后的结果：

| organization_id | parent_organization_id | organization_name |
|-----------------|------------------------|-------------------|
| 119             | NULL                   | Patel-Apps        |
| 250             | 247                    | Patel-Apps2       |

在这里查看：http://rextester.com/UWSG42619

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/168219

复制

相似问题

问mysql :删除主键columan中父id匹配的行
EN

表1 (organization_master)

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql :删除主键columan中父id匹配的行EN

表1 (organization_master)

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql :删除主键columan中父id匹配的行
EN