加入SQL中的两个大查询

Question

我需要加入这两个查询。...请帮助

SELECT P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL) AS URL,
          ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG--INCLUDING THESE FOR INFO ONLY, NOT REQUESTED
              --CAST(P.ACTIVITYDATE AS DATE) PGVIST_DATE
FROM [OPSODSADM].[DBO].[ELQR_PAGEVISIT] P WITH (NOLOCK)
JOIN [OPSSOAADM].[DBO].[ELOQUA_CONTACT_STAGE] ECS WITH (NOLOCK) ON P.EMAIL_ADDRESS = ECS.EMAIL_ADDRESS  --JOINING TO GET NAME AND COMPANY FIELDS
WHERE ACTIVITYDATE >= '2022-01-01' --STARTING DATE INCLUDED IN MARS REQUEST
         AND CHARINDEX('GARTNER.COM', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT GARTNER CONTACTS
         AND CHARINDEX('RAINFOCUS', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT RAINFOCUS CONTACTS
         AND (ECS.EVT_EXCL_FLG IS NULL OR ECS.EVT_EXCL_FLG = 'N') --DON'T WANT TO INCLUDE CONTACTS WITH ACTIVE/CURRENT REGISTRATIONS
         AND ECS.PER_REGION = 'NA'
         AND (UPPER(URL) = 'HTTPS://WWW.GARTNER.COM/EN/CONFERENCES/NA/CFO-FINANCE-US/REGISTER')
         --AND CHARINDEX('FES22/RFR', UPPER(URL),1) > 0 --RUN TO SEE ALL VARIATIONS OF RF PAGES BUT ONLY THE RFR/ORDER URL IS WHAT WE TARGET FOR CART ABANDONS
GROUP BY P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL),
              ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG
----ORDER BY P.EMAIL_ADDRESS




SELECT P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL) AS URL,
          ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG --INCLUDING THESE FOR INFO ONLY, NOT REQUESTED
              --CAST(P.ACTIVITYDATE AS DATE) PGVIST_DATE
FROM [OPSODSADM].[DBO].[ELQR_PAGEVISIT] P WITH (NOLOCK)
JOIN [OPSSOAADM].[DBO].[ELOQUA_CONTACT_STAGE] ECS WITH (NOLOCK) ON P.EMAIL_ADDRESS = ECS.EMAIL_ADDRESS  --JOINING TO GET NAME AND COMPANY FIELDS
WHERE ACTIVITYDATE >= '2022-01-01' --STARTING DATE INCLUDED IN MARS REQUEST
         AND CHARINDEX('GARTNER.COM', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT GARTNER CONTACTS
         AND CHARINDEX('RAINFOCUS', UPPER(P.EMAIL_ADDRESS),1) = 0  --DON'T WANT RAINFOCUS CONTACTS
         AND (ECS.EVT_EXCL_FLG IS NULL OR ECS.EVT_EXCL_FLG = 'N') --DON'T WANT TO INCLUDE CONTACTS WITH ACTIVE/CURRENT REGISTRATIONS
         AND ECS.PER_REGION = 'NA'
         AND (UPPER(URL) = 'HTTPS://REG.GARTNER.COM/FLOW/GARTNER/FES22/RFR/ORDER')
         --AND CHARINDEX('FES22/RFR', UPPER(URL),1) > 0 --RUN TO SEE ALL VARIATIONS OF RF PAGES BUT ONLY THE RFR/ORDER URL IS WHAT WE TARGET FOR CART ABANDONS
GROUP BY P.EMAIL_ADDRESS, ECS.FST_NAME, ECS.LAST_NAME, ECS.COMPANY_NAME, UPPER(P.URL),
              ECS.COUNTRY, ECS.PER_REGION, ECS.EEA_EMAILABLE_FLG, ECS.PHONEABLE_FLG, ECS.MAILABLE_FLG
--ORDER BY P.EMAIL_ADDRESS
ORDER BY P.EMAIL_ADDRESS

Answer 1

如果您想要一个快速而肮脏的解决方案，则 都可以进行两个查询 common Table表达式，然后像两个普通表一样加入它们：

with Tab1 AS ([YOUR FIRST BIG QUERY])
,Tab2 AS ([YOUR SECOUND BIG QUERY])

SELECT * FROM Tab1 INNER JOIN Tab2 ON Tab1.[FIELD] = Tab2.[FIELD]

如果您想要的东西不想要的东西看起来很不愉快，我建议您将URL取出并使其变量，因此您可以更简洁地调用脚本并从那里命名，但这非常依赖上下文。