代码之家 › 专栏 › 技术社区 › codaddict

编写此查询的最佳方法

mysql sql

codaddict · 技术社区 · 15 年前

我有两个mysql表,如下所示,数据如下:

CREATE TABLE `A` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`another_field` varchar(50) DEFAULT NULL
)

INSERT INTO `A` VALUES ('1', null, 'a');
INSERT INTO `A` VALUES ('2', null, 'b');
INSERT INTO `A` VALUES ('3', null, 'c');


CREATE TABLE `B` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`tableA_id` int(12) DEFAULT NULL,
PRIMARY KEY (`id`)
)

INSERT INTO `B` VALUES ('1', 'aa', '1');
INSERT INTO `B` VALUES ('2', 'aa', '1');
INSERT INTO `B` VALUES ('3', 'aa', '2');
INSERT INTO `B` VALUES ('4', 'aa', '3');
INSERT INTO `B` VALUES ('5', 'bb', '3');

我想知道当a.id=b.tablea_id时,如果所有b.status都相同,是否可以更新a.status 使用单个查询 ?

我希望我的A桌看起来像这样:

('1'、'a a'、'a')-状态更新为'a a',因为b.id 1&2具有相同的状态和b.tablea_id值。
('2'、'aa'、'b')-状态更新为'aa',因为b.id 3的状态相同。
('3',null,'c')-这不会更新,因为b.id 4&5具有不同的状态和相同的table2.table1_id值。

谢谢

4 回复 | 直到 14 年前

Roland Bouman 15 年前

UPDATE A
SET    status = COALESCE((
           SELECT MAX(B.status)
           FROM   B
           WHERE  B.tableA_id = A.id
           HAVING MAX(B.status) = MIN(B.status)
       ), A.status)

(注:我添加了一个更正,您需要 COALESCE(..., A.status) 否则,如果B中有多个状态,则状态将设置为空。

Fedor Hajdu 15 年前

不确定MySQL,但在MSSQL中,您可以编写如下内容:

更新集合A。status='a a' 来自a.id=b.tablea-id上的内部连接b 其中b.status='aa'

它在MySQL中应该是类似的,但我不认为该语言支持在更新时联接。但我仍然希望它能有所帮助。

Anax 15 年前

UPDATE a SET status = 
    (
        SELECT status FROM b WHERE tableA_id = a.id LIMIT 0,1
    )
WHERE id IN
    (
        SELECT tableA_id FROM b
        GROUP BY tableA_id
        HAVING COUNT(DISTINCT status) = 1
    )

更新 :Roland是对的;我已经更新了查询,现在它生成了正确的结果。

-1

Nikita Rybak 14 年前

CREATE TABLE `A` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`another_field` varchar(50) DEFAULT NULL
)

INSERT INTO `A` VALUES ('1', null, 'a');
INSERT INTO `A` VALUES ('2', null, 'b');
INSERT INTO `A` VALUES ('3', null, 'c');


CREATE TABLE `B` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`tableA_id` int(12) DEFAULT NULL,
PRIMARY KEY (`id`)
)

INSERT INTO `B` VALUES ('1', 'aa', '1');
INSERT INTO `B` VALUES ('2', 'aa', '1');
INSERT INTO `B` VALUES ('3', 'aa', '2');
INSERT INTO `B` VALUES ('4', 'aa', '3');
INSERT INTO `B` VALUES ('5', 'bb', '3');