SQL Server:如何根据两个不同的列组合多行

Question

我拥有的数据与：

SId       Program      Term       Year       Grad term     Grad year
-------------------------------------------------------------------
1         P           2          2001          3            2005
1         P           3          2001          3            2005
1         P           2          2002          3            2005
2         M           2          2002          2            2004
2         M           3          2002          2            2004

现在，我能够根据一列"日期"组合记录，但是在我的情况下，我需要检查年度和学期以确保它们连续并组合。从1-3(。因此，执行查询后，结果应为：

SID  Program  startterm  startyear  EndTerm   EndYear  Gradterm   Gradyear
--------------------------------------------------------------------------
1       P     2         2001         3     2001        3         2005
1       P     2         2002         2     2002        3         2005
2       M     2         2002         3     2002        2         2004

持续年度和学期的记录合并在一起，与Program-P，Term-2，2002年期限的记录并不连续到上一张记录，因此该记录最终将在单独的行中以相同的开始学期，年结束期开始，结束年。任何帮助将不胜感激。

Answer 1

此查询取决于术语列的值，如果值在1到3之间。

declare @t table (Id int, Program char(1), Term int, Year int, GradTerm int, GradYear int)
insert into @t
values (1, 'P', 2, 2001, 3, 2005)
    , (2, 'P', 3, 2001, 3, 2005), (3, 'P', 2, 2002, 3, 2005)
    , (4, 'M', 2, 2002, 2, 2004), (5, 'M', 3, 2002, 2, 2004)
select
    ID = row_number() over (order by grp), Program, startterm = min(Term)
    , startyear = min(Year), EndTerm = max(Term), EndYear = max(Year), GradTerm, GradYear
from (
    select
        *, grp = Year * 3 + Term - row_number() over (partition by Program, GradTerm, GradYear order by Year, Term)
    from 
        @t
) t
group by Program, GradTerm, GradYear, grp

输出：

ID  Program  startterm  startyear  EndTerm  EndYear  GradTerm  GradYear
-----------------------------------------------------------------------
1   P        2          2001       3        2001     3         2005
2   P        2          2002       2        2002     3         2005
3   M        2          2002       3        2002     2         2004

编辑：

SID也应在group by中，您还需要将其放入row_number中。另外，我在答案中的查询也有一些错误。因此，您可能会遇到错误的结果。这是一个正确的版本

declare @t table (SID int, Program char(1), Term int, Year int, GradTerm int, GradYear int)
insert into @t
values (1, 'P', 2, 2001, 3, 2005)
    , (1, 'P', 3, 2001, 3, 2005), (1, 'P', 2, 2002, 3, 2005)
    , (2, 'M', 2, 2002, 2, 2004), (2, 'M', 3, 2002, 2, 2004)
select
    SID, Program, startterm = right(min(val), 1)
    , startyear = left(min(val), 4), EndTerm = right(max(val), 1)
    , EndYear = left(max(val), 4), GradTerm, GradYear
from (
    select
        *, grp = Year * 3 + Term - row_number() over (partition by SID, Program, GradTerm, GradYear order by Year, Term)
        , val = concat(Year, Term)
    from 
        @t
) t
group by SID, Program, GradTerm, GradYear, grp

Answer 2

检查此查询是否对您有效。将列和表格与您使用的列进行。

               Select o.Program ,min(o.Term) AS StartTerm,
                 o.year AS StartYear,
                max(Term) AS EndTerm,
                 (Select max(g.year) from #GradProg g where g.[Grad term] = 
                       o.[Grad term] AND  g.Program = o.Program AND g.[Grad 
                  year] = o.[Grad year]) AS EndYear,
                 [Grad term],[Grad year]
               from #GradProg o
              group by   Program ,[Grad term],[Grad year],Year
              order by [Grad year] desc 

Answer 3

 /***This is a raw query which should be optimized using self joins***/     
 Select o.Program ,min(o.Term) AS StartTerm,
 min(o.year) AS StartYear,
 max(Term) AS EndTerm,
max(o.year) AS EndYear,
(Select max(g.year) from #GradProg g where g.[Grad term] = o.[Grad term] 
AND  g.Program = o.Program AND g.[Grad year] = o.[Grad year]) AS EndYear,
[Grad term],[Grad year]
,Iscontinous   
 from 
(
  Select *,
  case when (Select i.Program from #GradProg i where i.Term = t.nextterm and 
  i.Year = t.nexttermyear)  is not null then
   1
  when t.year = (Select max(y.Year) from #GradProg y) and t.Term = (Select 
  max(tm.Term) from #GradProg tm where t.Year = tm.Year)  then 
   1
 else
  0
 end as Iscontinous     
  from 
 (Select * ,
    case o1.Term when 3 then 1 
    else (o1.Term +1) 
    end as nextterm,
    case o1.Term when 3 then o1.Year + 1 
    else (o1.Year) 
    end as nexttermyear    
    from #GradProg o1
  )t
 )o
group by   o.Program ,o.[Grad term],o.[Grad year],
Iscontinous 
order by [Grad year] desc
/*********end *************/

Answer 4

尝试此

    create table #tmp (id int, Program VARCHAR(1),Term INT,[Year] INT,[Grad Term] INT, [Grad year] INT)
    insert into #tmp 
    SELECT 1,'P',2,2001,3,2005
    union 
    SELECT 2,'P',3,2001,3,2005
    union
    SELECT 3,'P',2,2002,3,2005
    union
    SELECT 4,'M',2,2002,2,2004
    union
    SELECT 5,'M',3,2002,2,2004
    ;with cte 
    AS(
        select *,
            RANK() OVER(partition by Program,[Year] order by id,[year],term) as [Minrank],
            RANK() OVER(partition by Program,[Year] order by id desc,[year]desc,term desc) as [Maxrank]
        from #tmp
    )

    select c1.id,c2.Program,c1.term as Startterm,c1.[year] as StartYear,
    c2.term as EndTerm, c2.[Year] As EndYear,c1.[Grad Term],c2.[Grad year] from cte c1
    JOIN cte c2 on c1.Program=c2.program and c1.[year]=c2.[year] and c1.Minrank=c2.[Maxrank]
    WHERE c1.Minrank=1
    order by c1.id

    drop table #tmp