主页 / user-69996

Beytun's questions

Martin Hope
Beytun
Asked: 2017-01-19 03:01:43 +0800 CST
  • 5

一个 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