主页 / dba / 问题 / 206586
Accepted
Gajus
Asked: 2018-05-14 02:33:17 +0800 CST

如何有条件地将行附加到结果集中？

  • 772

我有一个复杂的查询表，用于识别和之间的SELECT关系。这是一个表达式的简化：event_idattributeVALUES

SELECT event_id, attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute)

我想(event_id, '2D')为每个event_id尚未与3Dor2D属性关联的记录包含一个额外的记录。如何有条件地将行附加到结果集中？

在上表的情况下，预期结果将是：

(1, '2D'),
(1, 'IMAX'),
(2, 'IMAX'),
(2, '2D'),
(3, '3D')

还有一个表格event，每个相关的id.

postgresql

4 个回答

  1. Best Answer

    假设（就像稍后添加的那样）一个event具有所有相关唯一性的单独表id- 这有助于提高性能：

    WITH cte(event_id, attribute) AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT e.id, '2D'
FROM   event e
LEFT   JOIN cte ON cte.event_id = e.id
               AND cte.attribute IN ('2D','3D')
WHERE  cte.event_id IS NULL;

    有关的：

    如果您的查询仅返回 all 的一个子集event_id，则不能使用该表event来进行这样的简化。没有表的替代方案event：

    WITH cte AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT event_id, '2D'
FROM   cte
GROUP  BY 1
HAVING count(*) FILTER (WHERE attribute IN ('2D', '3D')) = 0;

    这与您自己回答的内容有些相似，只是更短且更快。特别是总FILTER条款应该是工具性的。有关的：

    由于从 CTE 派生的表上没有索引，因此开始第二个查询可能会更快。

    • 5

  2. cfr。http://sqlfiddle.com/#!17/82868/6

    create table t as SELECT
  event_attribute.event_id,
  event_attribute.attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute);


select E, '2D' a from (
  select distinct event_id E from t
   where not exists (
     select null from t t1
      where t1.event_id = t.event_id and t1.attribute = '3D')) X;

    对于较大的数据集可能更快：

    select E, '2D' a from (
  select distinct event_id E from t
  except
  select distinct event_id E from t
   where attribute = '3D') T2;

    见http://sqlfiddle.com/#!17/82868/14

    因为，根据已编辑的问题，最好避免重复的“2D”条目，“attribute = '3D'”应替换为“attribute in ('3D','2D')”。

    • 2

  3. 为了记录，这就是我想出的：

    WITH
  event_attribute AS
  (
    SELECT
      event_attribute.event_id,
      event_attribute.attribute
    FROM (
      VALUES
        (1, '2D'),
        (1, 'IMAX'),
        (2, 'IMAX'),
        (3, '3D')
    ) event_attribute (event_id, attribute)
  ),
  append_attribute_event AS
  (
    SELECT event_id
    FROM event_attribute
    GROUP BY event_id
    HAVING
      MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND
      MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0
  )
SELECT * FROM event_attribute
UNION ALL
SELECT event_id, '2D' "attribute"
FROM append_attribute_event

    但是，我很确定杰拉德的建议更好。

    • 2

  4. 一个直接的解决方案是：

    select event_id, attribute
from event_attribute
union all
select event_id, '2D' 
from event_attribute t1 
where not exists (
    select 1 from event_attribute t2 
    where t1.event_id = t2.event_id 
      and t2.attribute in ('2D','3D')
);
    • 1

相关问题