主页 / dba / 问题 / 161433
Accepted
Beytun
Asked: 2017-01-19 03:01:43 +0800 CST

SQL查询查找子记录是特定集合的所有父记录

  • 772

一个 Item 有很多 ItemDetails。ItemDetail 具有“type”、“value”和“item_id”字段。

当且仅当项目具有受某些可变条件限制的确切ItemDetails 时，我才需要查找所有项目。例如，我需要找到 ItemDetails 为 (type=10, value=1000) 和 (type=20 and value=2000) 的所有项目

我的第一个解决方案是这样的：

select p.*
from item p 
where not exists 
    ( 
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    );
-- Execution Time: 17.819 ms

但我意识到它只使用一个 ItemDetail(type=10, value=1000) 获取项目。然后我发现了这个问题并改变了如下查询。

select p.*
from item p 
where not exists 
    ( 
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    )
and 2 = (
  select count(c.item_id) from item_detail c
  where c.item_id=p.id);
-- Execution Time: 2426.596 ms

但是第二个子查询导致性能问题。第一次查询的执行时间是 5 毫秒，但第二次是 800 毫秒。有没有更好的方法来做到这一点？

我正在使用 PostgreSQL 9.5。

这是它的小提琴。

Edit-1：对于那些无法到达它的人来说，这是小提琴示例：

CREATE TABLE public.item
(
  id integer NOT NULL,
  name  character varying(10) NOT NULL, 
  CONSTRAINT item_pkey PRIMARY KEY (id)
);

CREATE TABLE public.item_detail
(
  id bigint NOT NULL,
  item_id integer NOT NULL,
  type  integer NOT NULL,
  value integer NOT NULL,
  CONSTRAINT item_detail_pkey PRIMARY KEY (id),
  CONSTRAINT fk_item_id FOREIGN KEY (item_id)
      REFERENCES item (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uq_item_type_value UNIQUE (item_id, type, value)
);

INSERT INTO public.item VALUES (1, 'Item1'),(2, 'Item2'),(3, 'Item3'),(4, 'Item4'),(5, 'Item5');

INSERT INTO public.item_detail
VALUES
  (1,1,10,1000),
  (2,1,20,2000),
  (3,2,10,1000),
  (4,3,10,1000),
  (5,3,20,2000),
  (6,3,30,3000),
  (7,4,10,1000),
  (8,4,10,1500);

编辑 2：我通过从ypercubeᵀᴹ 给出的源中选择最适合我的选项来提出这样的解决方案。

SELECT p.* FROM item p 
WHERE EXISTS (SELECT item_id FROM item_detail 
                WHERE item_id = p.id AND (type, value) = (10, 1000))
AND   EXISTS (SELECT item_id FROM item_detail 
                WHERE item_id = p.id AND (type, value) = (20, 2000))
AND NOT EXISTS (SELECT item_id FROM item_detail 
                WHERE item_id = p.id AND (type, value) NOT IN ((10, 1000), (20,2000)));
-- Execution Time: 0.984 ms
postgresql relational-division

3 个回答

  1. Best Answer

    我们试图解决的问题称为精确关系划分。它需要关系划分的额外条件，并且仍然有很多（实际上太多）方法可以解决此类问题。

    检查这个相关的 SO 问题，您将在 Postgres 和性能测试中找到 10 多种不同的方法来执行此操作。那里讨论的问题是（简单，不精确）关系划分，因此您必须调整答案：

    如何过滤 SQL 结果中的 has-many-through 关系

    这是另一个：

    select p.*
from item p 
  join 
    ( select item_id from item_detail 
      where (type, value) = (10, 1000)
      intersect
      select item_id from item_detail 
      where (type, value) = (20, 2000)
      except
      select item_id from item_detail 
      where (type, value) not in ((10, 1000), (20, 2000))
    ) as c
  on c.item_id = p.id ;

    我建议在(type, value, item_id).

    • 4

  2. PostgreSQL 特定查询

    SELECT
    i.id, i.name
FROM
    item i
WHERE
    ARRAY[(10, 1000), (20, 2000)] /* must be sorted! */ 
    = 
    ARRAY(SELECT (type, value)
            FROM item_detail d 
           WHERE d.item_id = i.id
        ORDER BY type /* sorted! */)

    此查询依赖于您可以将 anySELECT转换为 an ARRAYof ROWs，然后使用=（数组相等）数组运算符的事实。

    这是 PostgreSQL 特定的。作为非标准，如果您想从一个数据库切换到另一个数据库，它可能会导致问题。它有一个优势：表达时间更短，执行速度可能更快。（这可能意味着：尝试在您的真实案例中进行比较）。

    标准替代方案：

    如果您想同时查找item具有多个相关详细信息的 s，则应将其编写为具有as many EXISTS，加上一个额外条件以确保没有更多信息（这可以是相关信息的计数）记录，或者它也可能是 NOT EXISTS）：

    SELECT
    i.id, i.name
FROM
    item i
WHERE
    -- There is a (type, value) = (10, 1000) item_detail related to this item
    EXISTS 
       (SELECT * 
          FROM item_detail d 
         WHERE d.item_id = i.id AND d.type=10 AND d.value = 1000)
    AND 
    -- There is a (type, value) = (20, 2000) item_detail related to this item
    EXISTS 
       (SELECT * 
          FROM item_detail d 
         WHERE d.item_id = i.id AND d.type=20 AND d.value = 2000) 
    AND
    -- There are NO MORE item_details related (there must be 2, and only 2)
       2 = (SELECT count(*) 
              FROM item_detail d 
             WHERE d.item_id = i.id) ;

    根据@Beytun 的评论，执行时间是：

    增加了所有问题和答案查询的执行时间。第一个查询是 829.786 毫秒，第二个查询是 9.390 毫秒。

    因此，PostgreSQL 特定选项比使用标准选项差得多 （大约 2 个数量级）。所以，即使需要一些额外的输入（特别是如果你有一组相对较大的限制）......选择是显而易见的。

    • 2

  3. 第一个挑战是找到具有两个字段组合的相同 item_id。我认为以下应该有效：

    选择 p.*
从项目 p
p.id 在哪里
    (
      选择 c.item_id
      来自 item_detail c
        其中 (c.type = '10' 和 c.value '1000') 或 (c.type = '20' 和 c.value '2000')
        按 c.item_id 分组
        计数（*）> 1
    );
    • 0

相关问题