MySQL交叉表查询中缺少列
所以我有一个非常复杂的交叉表查询给我的客户，他为不同的大学管理学生资助。查询是动态的，所以它在存储过程中被构建为一个长字符串，然后准备、执行和释放。
我已经能够提取出最终的查询，长度约为2000个字符。
select concat(s.LastName, ', ', s.FirstName, ' ', ifnull(s.MiddleInit, '')) as StudentName, p.SSN,
case @fullSSN when 0 then concat('xxxx-xx-', right(p.ssn, 4)) else concat(left(p.ssn, 3), '-',
substr(p.ssn, 4, 2), '-', right(p.ssn, 4)) end as formattedSSN,
sp.AwardYear, c.CampusName, es.EnrollmentStatus, sp.LoanPeriodFrom, sp.LoanPeriodTo, pg.ProgramName,
concat(s.Address1, ', ', s.City, ', ', s.State, ' ', s.Zip) as Address, s.Email, s.DOB,
sp.StartDate, sp.EndDate as LDA, sp.VerifyType,
sum(case when FedPgmName = 'DL Subsidized' then Amount end) as 'DL Subsidized',
sum(case when FedPgmName = 'DL Unsubsidized' then Amount end) as 'DL Unsubsidized',
sum(case when FedPgmName = 'Pell' then Amount end) as 'Pell',
sum(case when FedPgmName = 'DL PLUS' then Amount end) as 'DL PLUS',
sum(case when FedPgmName = 'FSEOG' then Amount end) as 'FSEOG',
sum(case when FedPgmName = 'FWS' then Amount end) as 'FWS'
from Payments as p inner join Student as s on p.SchoolID = s.SchoolID and p.SSN = s.SSN inner join
(select SchoolID, SSN, Max(StudentProfileID) as MaxProfileID from StudentProfile group by SchoolID,
SSN) as max on p.SchoolID = max.SchoolID and p.ssn = max.ssn inner join StudentProfile as sp on
max.SchoolID = sp.SchoolID and max.SSN = sp.SSN and max.MaxProfileID = sp.StudentProfileID inner
join Program as pg on sp.SchoolID = pg.SchoolID and sp.ProgramID = pg.ProgramID inner join as fp
on p.FedPgmID = fp.FedPgmID inner join Campuses as c on sp.SchoolID = c.SchoolID and sp.CampusID =
c.CampusID inner join EnrollmentStatus as es on sp.EnrollmentStatusID = es.EnrollmentStatusID where
p.SchoolID = 'cbd' and CkDate between '2018-01-01' and '2018-12-31' and sp.ProgramID in(161, 24, 25,
168, 165, 166, 14, 159, 160, 13, 150, 151, 17, 23, 10, 15, 2, 16, 3, 26, 9, 21, 22, 1, 11, 12, 19,
20, 4, 18, 8, 5, 6, 7) and p.FedPgmID in(1, 8, 9, 10, 5, 6, 23) and sp.EnrollmentStatusID in(1, 10,
2, 3, 4, 5, 6, 7, 8, 9) and ifnull(CkNo, 0) > 0 group by StudentName, SSN, formattedSSN, AwardYear,
CampusName, EnrollmentStatus, LoanPeriodFrom, LoanPeriodTo, ProgramName知道这里到底发生了什么并不重要，除了我的问题涉及上面的交叉表代码，其中有几个不同的联邦程序名称的情况。如果底层数据包含所有提到的不同程序，那么一切都会正常工作。如果数据中缺少任何提到的案例(比如没有带有“FWS”的行)，那么该列就不会显示在结果中，并且我的代码有问题，因为它期望返回所有列。
所以我想弄清楚如何确保所有列都被返回。如果我将以下代码添加到生成的SQL代码的开头，它就可以正常工作：
select '' as StudentName, '' as SSN, '' as formattedSSN, '' as AwardYear, '' as CampusName, '' as EnrollmentStatus,
'' as LoanPeriodFrom,
'' as LoanPeriodTo, '' as ProgramName, '' as Address, '' as Email, '' as DOB, '' as StartDate, '' as LDA, '' as VerifyType,
select 0 as 'DL Subsidized', 0 as 'DL Unsubsidized', 0 as 'Pell', 0 as 'DL PLUS', 0 as 'FSEOG', 0 as 'FWS'
union使用上面的代码使查询成为联合查询是可行的。所有的列都被返回，但是我在顶部有一个“空”行，我必须为它编码。
请记住，查询是非常复杂的，并且必须使用大量的连接来构建，此外，我还必须执行一个group_concat才能获得可能的联邦程序的名称。这将为我创建case语句，这些语句被注入到主SQL语句中。
那么，除了执行联合查询之外，还有什么方法可以强制显示所有列吗？希望我把问题说清楚了。
谢谢..。
编辑:事实证明，添加联合查询根本不起作用。我运行的数据已经包含了所有联邦项目的记录。如果我使用不包括某些联邦程序数据集运行联合查询，则会得到关于两个查询不匹配列计数的错误。所以我在返回交叉表部分中所有可能的列时仍然有问题。
回答 1
Stack Overflow用户
发布于 2019-11-26 02:43:26
我不能解决这个问题，所以我用不同的方法解决了这个问题。由于交叉表查询不会在交叉表中提供任何不是基础数据中的有效值的列，因此我创建了一个包含所有可能列的表，然后截断并填充交叉表查询中的表。最后，从该表中进行选择。现在将显示所有可能的列。
https://stackoverflow.com/questions/58985927
复制相似问题
腾讯云开发者
