将多个select和update子句截断为单个子句

Question

下面的查询似乎效率低下，因为我所做的只是为每个查询交换一个变量(共同品牌)。是否有方法将此查询合并为一个子句并获得相同的结果？

UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10001372' and month = '2016-09')
WHERE cobrand='10001372' and month = '2016-10' or month = '2016-11';


UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10006164' and month = '2016-09')
WHERE cobrand='10006164' and month = '2016-10' or month = '2016-11';



UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10005640' and month = '2016-09')
WHERE cobrand='10005640' and month = '2016-10' or month = '2016-11';



UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10005244' and month = '2016-09')
WHERE cobrand='10005244' and month = '2016-10' or month = '2016-11';

Answer 1

使用Postgres的语法：

UPDATE temp_08.members
SET distinct_count = dc
FROM (SELECT cobrand, distinct_count dc
      FROM temp_08.members
      WHERE month = '2016-09') x
WHERE temp_08.members.cobrand = x.cobrand
AND month IN ('2016-10', '2016-11')

如果只想更新某些共同品牌，则可以将其添加到内部查询：

AND cobrand IN ('10001372', '10006164', '10005640', '10005244')