SQL查询:如何在同一个查询中的多个位置生成和使用查询结果

Question

我正在尝试使用相同的查询结果多个宫殿，其中所有相同的查询在查询中重复(公共查询(。

完整查询

SELECT * 
FROM   (SELECT DISTINCT entityid, parententityid 
FROM taggeditems
INNER JOIN securitygroups
ON entityid = securitygroups.sgentityid
WHERE  securitygroupid IN (
'bbe5df6a-e6be-4943-be4f-d2eafeb7ecb6',
'5076c7f6-da01-412d-a23f-7d44a17a0c2d',
'c01cb194-81d7-479f-9dec-5269ecd7bafa',
'25c8eff1-1607-4a57-bd88-b4be9a1c3b73')
AND projectname = 'MR GAT') AS A 
WHERE A.parententityid NOT IN (
SELECT entityid 
FROM   (SELECT DISTINCT entityid, parententityid 
FROM taggeditems 
INNER JOIN securitygroups
ON entityid = securitygroups.sgentityid
WHERE  securitygroupid IN (
'bbe5df6a-e6be-4943-be4f-d2eafeb7ecb6',
'5076c7f6-da01-412d-a23f-7d44a17a0c2d',
'c01cb194-81d7-479f-9dec-5269ecd7bafa',
'25c8eff1-1607-4a57-bd88-b4be9a1c3b73') 
AND projectname = 'MR GAT') AS B) 
OR A.parententityid IS NULL;

如果我们在上面的查询中看到

SELECT DISTINCT entityid, parententityid
FROM taggeditems
INNER JOIN SecurityGroups
ON entityid = SecurityGroups.SGEntityId
WHERE securityGroupId IN (
'bbe5df6a-e6be-4943-be4f-d2eafeb7ecb6',
'5076c7f6-da01-412d-a23f-7d44a17a0c2d',
'c01cb194-81d7-479f-9dec-5269ecd7bafa',
'25c8eff1-1607-4a57-bd88-b4be9a1c3b73')
AND ProjectName = 'MR GAT'

使用两个相同的位置。我不想执行一次查询，也不想执行相同的结果，我可以在需要进一步查询的地方使用。

我试过这样

SELECT *
FROM   (SELECT DISTINCT entityid,parententityid
FROM taggeditems
INNER JOIN SecurityGroups
ON entityid = SecurityGroups.SGEntityId
WHERE securityGroupId IN (
'bbe5df6a-e6be-4943-be4f-d2eafeb7ecb6',
'5076c7f6-da01-412d-a23f-7d44a17a0c2d',
'c01cb194-81d7-479f-9dec-5269ecd7bafa',
'25c8eff1-1607-4a57-bd88-b4be9a1c3b73')
AND ProjectName = 'MR GAT') AS A
WHERE A.parententityid NOT IN (A) AS B)
OR A.parententityid IS NULL;

但错误类似

Incorrect syntax near the keyword 'as'.

如何反复使用相同的查询结果？

Answer 1

您可以使用通用表表达式来减少查询的重复部分：

WITH FilteredItems as (
SELECT entityid,parententityid FROM taggeditems  INNER JOIN 
SecurityGroups ON entityid = SecurityGroups.SGEntityId
WHERE securityGroupId in ('bbe5df6a-e6be-4943-be4f-d2eafeb7ecb6',
'5076c7f6-da01-412d-a23f-7d44a17a0c2d',
'c01cb194-81d7-479f-9dec-5269ecd7bafa',
'25c8eff1-1607-4a57-bd88-b4be9a1c3b73') and 
ProjectName = 'MR GAT'
)
SELECT distinct entityid,parententityid from FilteredItems
where parententityid not in (select entityid from FilteredItems) or parententityid is null

(由于IN正在评估条目是否出现在集合中，因此它不会受到重复项的影响，我们不需要在那里应用任何DISTINCT(