SQL QUERY：如何在同一个查询中的多个位置创建和使用查询结果

r08 发表于 Dev

r08

我正在尝试使用相同的查询结果多个宫殿，其中所有相同的查询重复（常见查询）在查询中。

完整查询

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'.

如何一次又一次地使用相同的查询结果？

Damien_The_Unbeliever

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

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那里应用任何）

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。