sql-server - 选择对象具有某些属性的所有日期

Question

我使用动态属性来管理个人的特征。我有一个 IndividusDynPropValues 表，每个人和每个属性都有 1 行例如:

ID  IndividualID    ValueString  ValueFloat      ValueDate StartDate   Prop
108 2               Outside      NULL            NULL      2018-06-17  Out Status
107 2               Male         NULL            NULL      2018-05-17  Sex
106 1               Dead         NULL            NULL      2018-08-17  Out Status
105 2               Unk          NULL            NULL      2018-04-03  Sex
104 2               Adult        NULL            NULL      2018-04-03  Status
103 1               Femal        NULL            NULL      2018-03-27  Sex
102 1               Adult        NULL            NULL      2018-03-27  Status
101 2               Egg          NULL            NULL      2018-03-25  Status
100 1               Egg          NULL            NULL      2018-03-17  Status

我想仅使用一个 SELECT 查看 2 个日期之间所有活着的男性个体(即 Sex='Male' and Out Status='Adult' and Out status <> 'Dead')的表格。所以在这个例子中我想选择:

IndividualID    BeginDate          End Date      Group
2               2018-05-17         2019-05-16    Living male individuals

这可能吗？如何做到这一点？

我已经为“所有成人”组尝试过:

SELECT IDPV1.[IndividualID]
      ,IDPV1.[StartDate] AS BeginDate
     ,GETDATE() AS EndDate
     ,IDPV1.[ValueString]
     ,'All Adult' AS [Group]
   FROM [dbo].[IndividusDynPropValues] IDPV1
   WHERE IDPV1.ValueString='Adulte'

但它不适用于多个属性...

Answer 1

另一种可能的方法是计算匹配项并使用适当的 HAVING 子句:

表:

CREATE TABLE #IDVP1 (
    ID int,
    IndividualID int,
    ValueString varchar(50),
    StartDate date,   
    Prop varchar(50)
)
INSERT INTO #IDVP1 
    (ID, IndividualID, ValueString, StartDate, Prop)
VALUES
    (108, 2, 'Outside', '20180617', 'Out Status'),
    (107, 2, 'Male',    '20180517', 'Sex'),
    (106, 1, 'Dead',    '20180817', 'Out Status'),
    (105, 2, 'Unk',     '20180403', 'Sex'),
    (104, 2, 'Adult',   '20180403', 'Status'),
    (103, 1, 'Femal',   '20180327', 'Sex'),
    (102, 1, 'Adult',   '20180327', 'Status'),
    (101, 2, 'Egg',     '20180325', 'Status'),
    (100, 1, 'Egg',     '20180317', 'Status')

声明:

SELECT 
    IndividualID, 
    MIN(CASE WHEN (Prop = 'Sex') AND (ValueString = 'Male') THEN StartDate END) AS BeginDate,
    MIN(CASE WHEN (Prop = 'Out Status') AND (ValueString = 'Dead') THEN StartDate ELSE GETDATE() END) AS EndDate,
    'Living male individuals' AS [Group]
FROM #IDVP1
GROUP BY IndividualID
HAVING 
    SUM(
        CASE WHEN (Prop = 'Sex') AND (ValueString = 'Male') THEN 1 ELSE 0 END +
        CASE WHEN (Prop = 'Status') AND (ValueString = 'Adult') THEN 1 ELSE 0 END +
        CASE WHEN (Prop = 'Out Status') AND (ValueString <> 'Dead') THEN 1 ELSE 0 END
    ) = 3

输出(根据您的示例数据，只有一行匹配):

IndividualID    BeginDate           EndDate             Group
2               17/05/2018 00:00:00 17/05/2019 11:39:42 Living male individuals