我的三个查询已经达到了我的 SQL 知识的顶峰(Microsoft SQL 2005,如果这很重要),现在我需要将它们组合成一个查询,并将所有值放在一行上。
我的实际查询如下,但我认为如果我在这里提供一个简单的版本会更容易:
查询一:
-- 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(以下详细信息来自这个网页链接 http://www.sql-server-pro.com/sql-union.html并为每个数据集引入 SELECT NULL AS MissingColumnName),但它真的很难看 - 并且不会返回一行上的所有内容。
我正在寻找这样的结果:
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
我怎样才能做到这一点? (如果您好奇,我要加入的真正查询如下)
感谢你的帮助!,
拉塞尔·舒特
尽我所能清理这些 - 抱歉,它们显示得不太好。 (这些也可能存在问题 - 它们是我 SQL 知识的顶部,但到目前为止结果似乎是准确的。):-)
查询一:
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