sql - 在不使用分组或透视的情况下对动态字段表应用搜索条件

Question

我有以下两张表，一张表是关于人的，另一张表是用来存储关于人的各种动态属性/信息的。

 Id | Persons               PersonId | Field  | Value         
----+-------------         ----------+--------+-----------
 1  | Peter                      1   | City   | New York 
 2  | Jane                       1   | Age    | 26
                                 2   | City   | New York
                                 2   | Age    | 50

1. 我可以在 person with age > 25 and city = 'New York' 没有 grouping 或 pivoting 的情况下应用搜索条件吗> table 。
2. 以最少的性能开销应用搜索条件的最佳方式是什么。

Answer 1

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'New York' 

更新

我做了一些测试，INNER JOIN 看起来足够快。这里是结果和测试脚本

SET NOCOUNT ON 
SET STATISTICS IO ON

CREATE TABLE KeyValue (
    ID INT NOT NULL IDENTITY CONSTRAINT [PK_KeyValue] PRIMARY KEY CLUSTERED
    ,PersonId INT NOT NULL
    ,Field varchar(30) NOT NULL
    ,Value varchar(255) NOT NULL
    ,CONSTRAINT UQ__KeyValue__PersonId_Field UNIQUE (PersonId, Field)
)
GO
--INSERT INTO KeyValue 500K "users", 4 "Fields" - 2M rows

CREATE NONCLUSTERED INDEX [IX__KeyValue__Field_Value_ID]
ON [dbo].[KeyValue] ([Field],[Value]) INCLUDE ([PersonId])
GO

select PersonId from (
    select PersonId, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) RowNumber from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = 'xxxxx@gmail.com' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
    ) x
) y where RowNumber = 4
--Table 'KeyValue'. Scan count 20, logical reads 1510, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

select PersonId from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = 'xxxxx@gmail.com' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
) x GROUP by PersonId
HAVING COUNT(*) = 4
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'KeyValue'. Scan count 4, logical reads 1377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
    INNER JOIN KeyValue key3 ON key1.PersonId = key3.PersonId
    INNER JOIN KeyValue key4 ON key1.PersonId = key4.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'Sydney'
    AND key3.[Field] = 'Email' and key3.[Value] = 'xxxxx@gmail.com' 
    AND key4.[Field] = 'Name' and key4.[Value] = 'UserName' 
-- Table 'KeyValue'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO OFF
GO