T-SQL 在引用表中查找完全相同的值 [英] T-SQL finding of exactly same values in referenced table

问题描述

假设我的 Sql Serer 2008 数据库中有 3 个表:

Lets assume I have 3 tables in my Sql Serer 2008 database:

CREATE TABLE [dbo].[Properties](
    [PropertyId] [int] NOT NULL,
    [PropertyName] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[Entities](
    [EntityId] [int] NOT NULL,
    [EntityName] [nvarchar](50) NOT NULL
)    

CREATE TABLE [dbo].[PropertyValues](
    [EntityId] [int] NOT NULL,
    [PropertyId] [int] NOT NULL,
    [PropertyValue] [int] NOT NULL
)

1. 表属性包含一组可能的属性，可以为业务对象设置这些值.
2. 表实体包含从应用配置的业务对象.
3. 表 3 包含业务对象的选定属性值.每个业务对象都可以包含自己的一组属性(即，可以为第一个对象配置Property1"，但不能为第二个对象配置).

我的任务是找到与给定对象完全相同的业务对象(具有完全相同的属性集和完全相同的值的业务对象).性能至关重要.

My task is to find business objects which are exactly same as given object (ones which have exactly same set of properties with exactly same values). Performance is critical.

有什么建议吗?

[添加]例如，实体表中有一个条目，EntityId = 1.在 PropertyValues 表中，有 3 行与此条目相关:

[ADDED] For example there is an entry in Entities table with EntityId = 1. In PropertyValues table there are 3 row which are related to this entry:

 EntityId  PropertyId  PropertyValue
 1         4           Val4
 1         5           Val5
 1         6           Val6

要求在 Entity 表中找到其他条目，这些条目在 PropertyValues 表中有 3 个相关行，并且这些行包含与 EntityId = 1 的行相同的数据(除了 EntityId 列)

The requirement is to find other entries in Entity table which have 3 related rows in PropertyValues table and these rows contain the same data as rows for EntityId = 1 (besides of EntityId column)

[添加]请参阅我的新问题:存储具有哪些属性的数据的最佳方法可以变化

[ADDED] Please, see my new question: Best approach to store data which attributes can vary

[赏金1]谢谢大家.答案非常有帮助.我的任务有点复杂(但这种复杂性在性能方面很有用).请查看以下详细信息:

[BOUNTY1] Thanks for all. The answers were very helpful. My task is complicated a little bit (but this complication can be useful in performance purposes). Please, see the details below:

• 添加了名为 EntityTypes 的新表

• The new table named EntityTypes is added

EntityTypeId 列已添加到实体和属性表中

EntityTypeId column has been added into Entities and Properties tables

现在，有几种类型的实体.每个实体都有自己的一组属性.

Now, there are several types of entities. Each entity has it's own set of properties.

是否可以使用此信息提高性能?

Is it possible to increase performance using this information?

[赏金2]还有第二个并发症:

[BOUNTY2] There is the second complication:

• IsDeleted 列被添加到属性表
• PropertyValues 表可以包含已从数据库中删除的属性值.具有此类属性的实体被视为无效.
• 有些实体没有为 EntityType 集的每个属性设置值.这些实体也被视为无效.

问题是:我如何编写一个脚本来为它们选择所有实体和附加列 IsValid.

The question is: How do I can write a script which will select all Entities and additional column IsValid for them.

推荐答案

;with cteSource as
(
  select PropertyId,
         PropertyValue
  from PropertyValues
  where EntityId = @EntityID
)
select PV.EntityId
from PropertyValues as PV
  inner join cteSource as S  
    on PV.PropertyId = S.PropertyId and
       PV.PropertyValue = S.PropertyValue and
       PV.EntityId <> @EntityID
group by PV.EntityId
having count(*) = (select count(*)
                   from cteSource) and
       count(*) = (select count(*)
                   from PropertyValues as PV1
                   where PV1.EntityId = PV.EntityId)

对于您的添加，您可以添加以下 where 子句:

For your addition you can add this where clause:

where -- exlude entities with deleted properties
      PV.EntityID not in (select PV2.EntityID
                          from Properties as P
                            inner join PropertyValues as PV2
                              on P.PropertyID = PV2.PropertyID
                          where P.IsDeleted = 1)
      -- exclude entities with missing EntityType                     
  and PV.EntityID not in (select E.EntityID
                          from Entities as E
                          where E.EntityType is null) 

如果您想针对一些示例数据测试查询，您可以在此处执行此操作:https://data.stackexchange.com/stackoverflow/q/110243/matching-属性

If you want to test the query against some sample data you can do so here: https://data.stackexchange.com/stackoverflow/q/110243/matching-properties

这篇关于T-SQL 在引用表中查找完全相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！