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

检查一个子查询中的所有行是否在另一个子查询中具有所有行

sql-server-2014 performance sql-server sql

Magnus · 技术社区 · 7 年前

一个策略有一组 insuranceTypes 在里面 PolicyCoverage .
一个策略有一组 保险类型 对于特定组织 PolicyPermissionInsuranceType .

我正试图得到 全部的 保险类型表 投保范围 在里面 保单许可保险类型 对于特定的组织、用户和权限。

在C中,我将规则(对于为组织找到的单个策略)评估为:

 public class ReadPolicyLimitedPermission
 {
        private IEnumerable<Guid> InsuranceTypeIds { get; }

        public bool Validate(Policy entity)
        {
            return !entity.InsuranceTypes.Except(InsuranceTypeIds).Any();
        }
 }

我试图为数据库中的所有策略编写一个与该规则相同的查询。我的查询如下所示,但在提供 userId 表中没有记录。所以问题是,有没有更好的方法来执行这种检查?

查询:

declare @UserId uniqueidentifier = newId() --Does not exist
declare @Permission nvarchar(150) = 'ReadPolicyLimitedPermission'

select p.Id
from test.Policy p
where
not exists
(
    select 
        pc.insuranceTypeId
    from
        test.PolicyCoverage pc
    where
        pc.PolicyId = p.Id

    except

    select
        ppit.InsuranceType 
    from
        test.PolicyPermissionInsuranceType ppit
    where
        ppit.UserId = @UserId and
        ppit.Permission = @Permission and
        ppit.OrganizationId = p.OrganizationId
)

桌子尺寸:

Policy 201762 rows
PolicyCoverage 393004 rows
PolicyPermissionInsuranceType 36984 rows

执行计划:

表结构:

CREATE TABLE [test].[Policy](
    [Id] [uniqueidentifier] NOT NULL,
    [OrganizationId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [test].[PolicyCoverage](
    [PolicyId] [uniqueidentifier] NOT NULL,
    [InsuranceTypeId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

CREATE TABLE [test].[PolicyPermissionInsuranceType](
    [UserId] [uniqueidentifier] NOT NULL,
    [OrganizationId] [uniqueidentifier] NOT NULL,
    [Permission] [nvarchar](50) NOT NULL,
    [InsuranceType] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_PolicyPermissionInsuranceType] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC,
    [OrganizationId] ASC,
    [Permission] ASC,
    [InsuranceType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

或者可以用不同的方式存储表中的数据吗? 保单许可保险类型

例子:

Policy 1
  -Org 1 
    -Type 1
    -Type 2
Policy 2
  -Org 1
    -Type 1
    -Type 3


PolicyPermission 1
-Org1
-Type1
-Type2
-Type5

they查询应返回policy1,因为它在policyPermission表中具有所有类型(type1,type2),但不返回policy2,因为它具有policyPermission1不具有的type3。

2 回复 | 直到 7 年前

valii 7 年前

如果你试试这个怎么办:

select a.id from (
    select p.id,ppt.userid From test.Policy p
    join test.PolicyCoverage pc on pc.policyid = p.id
    left join test.PolicyPermissionInsuranceType ppt on ppt.InsuranceType = pc.insurancetypeid
      and ppt.OrganizationId = p.OrganizationId
      and ppt.UserId = @UserId 
      and ppt.Permission = @Permission 
)a
group by a.id
having  COUNT(a.id) = COUNT(a.userid)

Tanner 7 年前

我将此作为答案发布,因为我想添加一些您可以测试的代码,所以似乎不适合发表评论。很高兴删除,除非你觉得有用。

您可以先在子查询中创建第二个表作为临时表,然后在子查询中引用该表,这可能会加快速度:

declare @UserId uniqueidentifier = newId() --Does not exist
declare @Permission nvarchar(150) = 'ReadPolicyLimitedPermission'

-- temp table here
select ppit.InsuranceType, ppit.OrganizationId 
into #temp
from test.PolicyPermissionInsuranceType ppit
where ppit.UserId = @UserId and
      ppit.Permission = @Permission

-- original modified query with temp table
select p.Id
from test.Policy p
where
not exists
(
    select pc.insuranceTypeId
    from test.PolicyCoverage pc
    where pc.PolicyId = p.Id

    except

    select ppit.InsuranceType 
    from #temp ppit        
    where ppit.OrganizationId = p.OrganizationId
)