我目前正在努力让我的查询在两行上显示两个日期。即我有两行，两个日期的两列。我需要一行先有空然后日期，第二行有日期然后空。下图中显示的示例 - 每行只需要显示这些日期中的一个。
到目前为止，Google 还不是我的朋友，因为尝试向搜索引擎描述它是一件相当晦涩的事情。任何帮助将不胜感激。完整查询如下：
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY person_code ORDER BY unit_instance_code) AS rn
FROM learner_aims
)
SELECT DISTINCT p.person_code [ID],
p.forename [Forename],
p.surname [Surname],
uio.long_description [CourseDesc],
CASE rn
WHEN 1 THEN NULL
WHEN 2 THEN CONVERT(DATE, sub.[GIL Start Date], 103)
END AS [GIL Start Date],
CASE rn
WHEN 1 THEN CONVERT(DATE, sub.[Non-GIL Start Date], 103)
WHEN 2 THEN NULL
END AS [Non-GIL Start Date]
-- CONVERT(DATE, sub.[GIL Start Date], 103) [GIL Start Date],
-- CONVERT(DATE, sub.[Non-GIL Start Date], 103) [Non-GIL Start Date]
FROM people p
INNER JOIN learner_aims la ON p.person_code = la.person_code
INNER JOIN unit_instance_occurrences uio ON uio.uio_id = la.uio_id
INNER JOIN cte ON cte.person_code = p.person_code
LEFT JOIN (SELECT DISTINCT p.person_code [ID],
p.forename [Forename],
p.surname [Surname],
MIN(fy22.earlieststartdate) [GIL Start Date],
MIN(fy22sub.earlieststartdate) [Non-GIL Start Date]
FROM people p
INNER JOIN learner_aims la ON p.person_code = la.person_code
INNER JOIN unit_instance_occurrences uio ON uio.uio_id = la.uio_id
LEFT OUTER JOIN (
SELECT p.person_code [ID],
p.forename [Forename],
p.surname [Surname],
uio.course_occurrence_code [CourseCode],
uio.long_description [CourseDesc],
uio.owning_organisation [OwningOrg],
uio.offering_organisation [OfferingOrg],
la.uio_id [uio_id],
MIN(CONVERT(DATE, la.start_date, 103)) [EarliestStartDate]
FROM people p
INNER JOIN learner_aims la ON p.person_code = la.person_code
INNER JOIN unit_instance_occurrences uio ON uio.uio_id = la.uio_id
WHERE la.ilr = 'Y'
AND la.funding_year = '22'
AND (
uio.course_occurrence_code LIKE 'GIL%'
OR uio.course_occurrence_code LIKE 'SEED%'
)
GROUP BY p.person_code,
p.forename,
p.surname,
uio.course_occurrence_code,
uio.long_description,
uio.owning_organisation,
uio.offering_organisation,
la.uio_id
) [FY22] ON fy22.id = la.person_code
LEFT OUTER JOIN (
SELECT p.person_code [ID],
p.forename [Forename],
p.surname [Surname],
uio.course_occurrence_code [CourseCode],
uio.long_description [CourseDesc],
uio.owning_organisation [OwningOrg],
uio.offering_organisation [OfferingOrg],
la.uio_id [uio_id],
MIN(CONVERT(DATE, la.start_date, 103)) [EarliestStartDate]
FROM people p
INNER JOIN learner_aims la ON p.person_code = la.person_code
INNER JOIN unit_instance_occurrences uio ON uio.uio_id = la.uio_id
WHERE la.ilr = 'Y'
AND la.funding_year = '22'
AND uio.course_occurrence_code NOT LIKE 'GIL%'
AND uio.course_occurrence_code NOT LIKE 'SEED%'
AND la.person_code IN (
SELECT la.person_code [ID]
FROM people p
INNER JOIN learner_aims la ON p.person_code = la.person_code
INNER JOIN unit_instance_occurrences uio ON uio.uio_id = la.uio_id
WHERE la.ilr = 'Y'
AND la.funding_year = '22'
AND (
uio.course_occurrence_code LIKE 'GIL%'
OR uio.course_occurrence_code LIKE 'SEED%'
)
)
GROUP BY p.person_code,
p.forename,
p.surname,
uio.course_occurrence_code,
uio.long_description,
uio.owning_organisation,
uio.offering_organisation,
la.uio_id
) [FY22SUB] ON fy22sub.id = la.person_code
WHERE la.funding_year = '22' AND la.ilr = 'Y'
GROUP BY p.person_code,
p.forename,
p.surname
HAVING MIN(CONVERT(DATE, fy22sub.earlieststartdate, 103)) > MIN(CONVERT(DATE, fy22.earlieststartdate, 103))
) sub ON sub.id = la.person_code
WHERE la.funding_year = '22' AND la.ilr = 'Y'
ORDER BY 3, 2
不清楚是什么决定了该行是第一还是第二，我在第二列中的文本的基础上做了它，因为它是唯一改变的东西。
所以这里的想法是枚举行（按 2 排序），然后根据需要显示日期：
declare @t table (id int, CourseDesc varchar(100), [GIL Start Date] date, [Non-GIL Start Date] date);
insert into @t values(21571, 'text1', '20151116', '20160222'), (21571, 'text2', '20151116', '20160222');
with cte as
(
select *,
row_number() over(partition by id order by CourseDesc)as rn
from @t
)
select id, CourseDesc,
case rn
when 1 then null
when 2 then [GIL Start Date]
end as [GIL Start Date],
case rn
when 1 then [Non-GIL Start Date]
when 2 then null
end as [Non-GIL Start Date]
from cte;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句