如何在多个列上选取不同的选项?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (3)
  • 关注 (0)
  • 查看 (43)

我需要检索一个表中所有的行,其中两个列的组合不同。我想要所有商品不管是销售还是没有销售的加个。然后基于当日和价格的销售量将一些商品设置为活动状态。

所以我想:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)
提问于
用户回答回答于
SELECT DISTINCT a,b,c FROM t

相当于:

SELECT a,b,c FROM t GROUP BY a,b,c

对于你的查询,我会这样做:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )
用户回答回答于

您的查询的问题是,在使用GROUP BY子句时(实际上是通过使用不同的方法),您只能使用GROUP BY或聚合函数的列。您不能使用列的id,因为存在潜在的不同值。在您的情况中,因为有了HAVING子句,所以总是只有一个值,但是RDBMS目前还没有这么智能。

可以使用下面的语句(且不需要联接):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

您也可以使用MAX或AVG代替MIN。

用户回答回答于

你可以:

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

这个要快得多。通过因子10 - 15(在PostgreSQL 8.4和9.1中进行测试)对当前语句的性能进行了分析。

但这不是最好的。可以用NOT EXISTSEXISTS是标准SQL语句,它至少从PostgreSQL 7.2开始,就已经存在了:

UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT 1
   FROM   sales s1
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   );
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

SQL Fiddle

标识行的唯一键

如果没有表的主键或唯一键(id(在本例中,您可以用系统列替换ctid为了这个查询的目的(但不是为了其他目的):

   AND    s.ctid <> s1.ctid

每个表都应该有一个主键。如果你还没有一个,就加一个吧。我建议serial或者IDENTITY在Postgres 10+。

怎么会更快?

可以发现控件中的子查询第一个dupe之后停止评估。对于几乎没有重复的基表,这只是稍微提高了效率。有了大量的重复,效率提高的更明显。

排除空更新

如果一些或多个行已经有status = 'ACTIVE',您的更新不会更改任何内容,但是仍然会以全部成本插入新的行版本(只适用轻微的例外情况)。通常,你不想这样。再加一个WHERE如上文所示,这样可以使这一过程更快:

如果status被定义NOT NULL,您可以简化为:

AND status <> 'ACTIVE';

所属标签

可能回答问题的人

  • HKC

    红客学院 · 创始人 (已认证)

    26 粉丝7 提问5 回答
  • Dingda

    Dingda · 站长 (已认证)

    4 粉丝0 提问3 回答
  • 西风

    renzha.net · 站长 (已认证)

    9 粉丝1 提问3 回答
  • 螃蟹居

    1 粉丝0 提问2 回答

扫码关注云+社区

领取腾讯云代金券