代码之家 › 专栏 › 技术社区 › GloryFish

选择实体未提供的项类型

not-exists select sql

GloryFish · 技术社区 · 17 年前

所以我有一些数据。有实体。实体具有任意数量的项。项可以是定义的一组类型之一。一个实体可以有多个给定类型的项。我可以得到一个实体拥有的项目列表。我想要的是获得一个实体没有项目的类型列表。

这是我的模式:

entities
id name
1  Bob
2  Alice

item_types
id      name 
1       red
2       yellow
3       green
4       blue
5       orange

items
entity_id item_type_id name
1         1            apple
1         2            banana
1         3            lime
1         3            tree
2         3            money
2         5            traffic cone

我想查询Bob的ID(1)并获取此列表:

4   blue
5   orange

并查询Alice的ID(2)并获取:

1   red
2   yellow
4   blue

可能是从我的脸上开始的。我会继续努力的,但我敢打赌,皮普斯打败了我。谢谢你抽出时间来。

4 回复 | 直到 17 年前

Shannon Severance 17 年前

select id, name
from item_types
where id not in
    (select i.item_type_id
    from items i
    inner join entities e
        on e.id = t.entity_id
    where e.Name = 'Bob')

或者(有时更快,但优化器一直在改进):

select disctinct t.id, t.name
from item_types t
left outer join items i
    on i.item_type_id = t.id
left outer join entities e
    on e.id = i.entity_id
    and e.Name = 'Bob'
where e.id is null

oscarkuo 17 年前

为了鲍伯

SELECT
  t.id, t.name
FROM
  items i
INNER JOIN
  entities e ON e.id = i.entity_id
INNER JOIN
  item_types t ON t.id = i.item_type_id
WHERE
  e.id <> 1

对于Alice,只需将e.id<gt;1交换为e.id<gt;2

Roee Adler 17 年前

我想这就是你要找的:

SELECT id, name
FROM item_types
WHERE id NOT IN
(
    SELECT DISTINCT item_type_id
    FROM items
    WHERE entity_id = 1
)

“entity_id=1”表示Bob,必要时进行更改。

Raj More 17 年前

我会重做这个使它更好,但这里有一个可行的解决方案

set nocount on
go
drop table #entities
drop table #itemtype
drop table #items
create table #Entities
(
EntityId int,
EntityName  varchar (250)
)
create table #ItemType
(
ItemTypeId int,
ItemTypeName    varchar (250)
)

create table #Items
(
EntityId int,
ItemTypeId int,
ItemName    varchar (250)
)
go
insert into #entities values (1, 'Bob')
insert into #entities values (2, 'Alice')
go
insert into #ItemType values (1, 'red')
insert into #ItemType values (2, 'yellow')
insert into #ItemType values (3, 'green')
insert into #ItemType values (4, 'blue')
insert into #ItemType values (5, 'orange')
go
insert into #Items values (1, 1, 'apple')
insert into #Items values (1, 2, 'banana')
insert into #Items values (1, 3, 'lime')
insert into #Items values (1, 3, 'tree')
insert into #Items values (2, 3, 'money')
insert into #Items values (2, 5, 'traffic cone')
go


;WITH ENTITY AS (
SELECT #Entities.EntityId, EntityName, ItemTypeId, ItemName
FROM #Entities, #Items
WHERE #Entities.EntityId = #Items.EntityId
AND #Entities.EntityName = 'Bob'
) 
SELECT #ItemType.* FROM ENTITY
RIGHT JOIN #ItemType ON ENTITY.ItemTypeId = #ItemType.ItemTypeId
WHERE EntityId is NULL


;WITH ENTITY AS (
SELECT #Entities.EntityId, EntityName, ItemTypeId, ItemName
FROM #Entities, #Items
WHERE #Entities.EntityId = #Items.EntityId
AND #Entities.EntityName = 'Alice'
) 
SELECT #ItemType.* FROM ENTITY
RIGHT JOIN #ItemType ON ENTITY.ItemTypeId = #ItemType.ItemTypeId
WHERE EntityId is NULL