如何将 SQL 查询与不同的表达式结合起来? [英] How can I combine SQL queries with different expressions?

问题描述

我的三个查询已经达到了我的 SQL 知识的顶峰(Microsoft SQL 2005，如果这很重要的话)——现在我需要将它们组合成一个查询，所有值都在一行上.

I've got three queries that are already at the peak of my SQL knowledge (Microsoft SQL 2005, if that matters) - and now I need to combine them into a single query with all of the values on a single row.

我的实际查询如下，但我认为如果我在这里提供一个简单的版本会更容易:

My actual queries are below, but I thought it'd be easier if I provided a simple version here:

查询一:

-- Provides School District summary based on a CountyID
SELECT DistrictID, Count(Schools) as NumberofSchools
FROM Schools
WHERE (CountyID = 207)
GROUP BY DistrictID

查询一个样本输出:

DistrictID  |  NumberofSchools
345         |  26
567         |  17
211         |  9

查询二:

-- Summarizes Activity from our Contact Manager (GoldMine)
SELECT DistrictID, Count(Contacts) as NumberofContacts, MAX(Contact) as LastActivity
FROM ContactManager JOINED WITH CONTACT MANAGER TABLES
WHERE (CountyID = 207)
GROUP BY DistrictID

查询两个样本输出:

DistrictID  |  NumberofContacts  |  LastActivity
345         |  29                |  Nov 12, 2010
567         |  31                |  Dec 5, 2010
211         |  4                 |  Oct 9, 2010

查询三:

-- Summarizes data from our Opt-In Email Newsletter
SELECT DistrictID, Count(EmailSubscribers) AS NumberofSubscribers, MAX(Date) AS LastSent
FROM SubscribeList JOINED WITH Schools Tables
WHERE (CountyID = 207)
GROUP BY DistrictID

查询三个样本输出:

DistrictID  |  NumberofSubscribers  |  LastSent
345         |  2                    |  Sep 4, 2010
567         |  3                    |  Oct 22, 2010
211         |  1                    |  NULL

我尝试使用父 SELECT 语句对它们进行巨大的 UNION，(遵循 this weblink 并为每个数据集引入 SELECT NULL AS MissingColumnName) 但它真的很丑 - 并且不会在一行中返回所有内容.

I've tried making a huge UNION of them with a parent SELECT statement, (following details from this weblink and by introducting SELECT NULL AS MissingColumnName for each dataset) but it's really ugly - and doesn't return everything on one row.

我正在寻找这样的结果:

I'm looking for results like this:

DistrictID  |  NumberofSchools  |  NumberofContacts  |  LastActivity  |  NumberofSubscribers  |  LastSent
345         |  26               |  29                |  Nov 12, 2010  |  2                     |  Sep 4, 2010
567         |  17               |  31                |  Dec 5, 2010   |  3                     |  Oct 22, 2010 
211         |  9                |  4                 |  Oct 9, 2010   |  1                     |  NULL

我怎样才能做到这一点?(如果你好奇，我要加入的真正问题如下)

How can I make this work? (And if you're curious, the real queries I'm joining are below)

感谢您的帮助！，

罗素舒特

尽我所能清理这些 - 抱歉，它们的显示效果不太好.(这些也可能存在问题 - 它们是我的 SQL 知识的顶级，但到目前为止结果似乎是准确的.):-)

Cleaned up these as best I can - sorry they don't display really nice. (There may be issues with these too - they're at the top of my SQL knowledge, but so far the results seem accurate.) :-)

查询一:

SELECT
    institutionswithzipcodesadditional_1.DistrictID, institutionswithzipcodesadditional_1.InstitutionName, institutionswithzipcodesadditional_1.Latitude, institutionswithzipcodesadditional_1.Longitude,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS OthersEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS OthersCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (13) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS K12SchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (13) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS K12SchoolsCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (12) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS HighSchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (12) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS HighSchoolsCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (10, 11) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS MiddleSchoolsEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (10, 11) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS MiddleSchoolsCount,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (9) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS ElementariesEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (9) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS ElementariesCount,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20, 13, 12, 10, 11, 9) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS AllSchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20, 13, 12, 10, 11, 9) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS AllSchoolsCount
FROM zipcodes 
    INNER JOIN users_link_territory ON zipcodes.CountyID = users_link_territory.CountyID
    INNER JOIN institutionswithzipcodesadditional ON zipcodes.ZIP = institutionswithzipcodesadditional.ZIP
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1 ON institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
WHERE
    (institutionswithzipcodesadditional_1.CountyID = 207)
AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.DistrictID, institutionswithzipcodesadditional_1.InstitutionName, institutionswithzipcodesadditional_1.Latitude, institutionswithzipcodesadditional_1.Longitude

查询二:

SELECT
institutionswithzipcodesadditional_1.InstitutionID AS DistrictID,
COUNT(GoldMine.dbo.CONTACT1.ACCOUNTNO) AS GM,
MAX(CASE WHEN GoldMine.dbo.CONTHIST.USERID NOT IN ('DEBRA', 'TRISH', 'RUSSELL', 'GREG') THEN GoldMine.dbo.CONTHIST.OnDate ELSE NULL END) AS LastActivity
FROM institutionswithzipcodesadditional
    LEFT OUTER JOIN contacts
    LEFT OUTER JOIN GoldMine.dbo.CONTACT1
    RIGHT OUTER JOIN GoldMine_Link_Russell 
    ON GoldMine.dbo.CONTACT1.KEY3 = GoldMine_Link_Russell.GoldMineKeyThree
    ON contacts.ContactID = GoldMine_Link_Russell.ContactID 
    ON institutionswithzipcodesadditional.InstitutionID = contacts.InstitutionID
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1
    ON institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
    LEFT OUTER JOIN GoldMine.dbo.CONTHIST ON GoldMine.dbo.CONTHIST.ACCOUNTNO = GoldMine.dbo.CONTACT1.ACCOUNTNO
WHERE (institutionswithzipcodesadditional_1.CountyID = 207) AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.InstitutionID

查询三:

SELECT
COUNT(NewsletterContacts.Email) AS EMailableContacts,
institutionswithzipcodesadditional_1.InstitutionID AS DistrictID,
MAX(newsletterregister.Sent) AS LastSent
FROM newsletterregister
    RIGHT OUTER JOIN contacts ON newsletterregister.ContactID = contacts.ContactID
    RIGHT OUTER JOIN institutionswithzipcodesadditional ON contacts.InstitutionID = institutionswithzipcodesadditional.InstitutionID
    LEFT OUTER JOIN EmailableContacts ON institutionswithzipcodesadditional.InstitutionID = EmailableContacts.InstitutionID
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1 ON 
    institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
WHERE
    (institutionswithzipcodesadditional_1.CountyID = 207)
    AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.InstitutionID

推荐答案

我偷了 Mark 的部分解决方案，但我想向你展示这个布局是为了它的可读性.这样一来，您就不会将所有三个查询都卡在一个 select 语句中.这为您提供了一些使用临时表或表变量可能会获得的可维护性好处，但下面的更改要灵活得多，因为您不必在每次添加/删除列时都弄乱表声明.

I stole part of Mark's solution, but I wanted to show you this layout for its readability. That way you don't have all three queries jammed into the one select statement. This gives you some of a maintainability benefits you might get with a temp table or table variables, but the below is much more flexible to change, because you don't have to mess with table declarations everytime you add/remove columns.

With SomeGoodName as
(
  SELECT ...
)
,
AnotherDescriptiveName as
(
  Select ...
)
,
AThirdNiceName as
(
  Select ...
)
SELECT
    T1.DistrictID,
    T1.NumberofSchools,
    T2.NumberofContacts,
    T2.LastActivity,
    T3.NumberofSubscribers,
    T3.LastSent
FROM SomeGoodName T1
JOIN AnotherDescriptiveName T2 ON T1.DistrictID = T2.DistrictID
JOIN AThirdNiceName T3 ON T1.DistrictID = T3.DistrictID

这篇关于如何将 SQL 查询与不同的表达式结合起来?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！