我有3个查询已经是我的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，(以下信息来自此网络链接，并为每个数据集引入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)
感谢您的所有帮助！，
罗素·舒特(Russell Schutte)
Russell Schutte
我已尽力清理了这些内容-很抱歉，它们显示的不是很好. (这些可能也有问题-它们在我的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查询与不同的表达式结合在一起?
发布评论