如何制作可以在单个 SQL 子查询中引用先前数据条目的 SQL 层次结构

Question

如何创建一个可以使用数据层次结构(通过透视数据(的 SQL，并构建一个可以引用回先前记录的子查询，与 id 匹配。该 ID 可以有一个或多个评估类型，我需要检索没有退出"6 个月重新评估"记录的所有assessment_type。如果同一 id 有另一个引用，例如"6 个月评估"，则跳过。我只需要assessment_type字段中没有先前记录的 id 行。

数据示例如下：id、date_of_assessment、assessment_type。 创建此数据布局的 SQL 位于代码示例中。

id     date-of_assessment assessment_type
1      2019-09-06         Initial
2      2019-09-06         Initial
1      2017-06-23         6-Month Reassessment
2      2017-09-01         6-Month Reassessment
3      2017-09-09         Initial

SELECT a.patid as id, date_of_assessment, 'Initial' assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
a.patid
WHERE Type_Assessment_Value = 'Initial'
UNION ALL
SELECT a.patid as id, date_of_assessment, '6-Month Reassessment' 
assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
a.patid
WHERE Type_Assessment_Value = '6-Month Reassessment'
UNION ALL
SELECT a.patid as id, date_of_assessment, 'Transition/Discharge' 
assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = 
a.patid
WHERE Type_Assessment_Value = 'Transition/Discharge'

预期结果：所有其他具有先前或之后(日期(的内容将被排除在外。

id     date-of_assessment assessment_type
3      2017-09-09         Initial

Answer 1

你可以试试这个。如上面的查询所示，您从上面的查询中获得了结果的一部分，只是结果中有一些额外的记录。

你说"预期结果：所有其他有之前或之后(日期(的将被排除在外。 因此，您需要一个过滤器来排除所有日期，少于最后一条6-Month Reassessment记录。因此，我们将您的记录分组到子查询中，并代表date_of_assessment筛选记录，如下所示。

SELECT  id, date_of_assessment, assessment_type FROM (
SELECT a.patid as id, date_of_assessment, 'Initial' assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
WHERE Type_Assessment_Value = 'Initial'
UNION ALL
SELECT a.patid as id, date_of_assessment, '6-Month Reassessment' assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
WHERE Type_Assessment_Value = '6-Month Reassessment'
UNION ALL
SELECT a.patid as id, date_of_assessment, 'Transition/Discharge' assessment_type
FROM cw_cans_assessment a INNER JOIN DCFS.CANS c ON c.patid = a.patid
WHERE Type_Assessment_Value = 'Transition/Discharge'
) AS TAB 
WHERE TAB.Id NOT IN (SELECT a.patid FROM cw_cans_assessment a 
INNER JOIN DCFS.CANS c ON c.patid = a.patid
GROUP BY a.patid, Type_Assessment_Value HAVING COUNT(a.patid)>1 )

编辑：- 正如您所说，您要过滤总数小于2的所有记录，因此我们根据单个ID的计数过滤记录，您可以最后更改分组以应用更多过滤器。

Answer 2

我希望这个查询是你正在寻找的：

select t1.id, t1.date_of_assessment, t1.assessment_type
from mytable t1
where t1.id not in
(
select t2.id
from mytable t2
where t2.assessment_type = '6-Month Reassessment'
)