在sql中连接两个大查询

Question

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

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

如果您想要一个快速而肮脏的解决方案，您可以将这两个查询都做公共表表达式，然后像两个普通表那样加入它们：

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去掉并使其成为一个变量，这样您就可以更简洁地调用脚本，并从那里开始，但这非常依赖于上下文。