SQL server查询每个课程的第一节课的id

开发这种事啊，越往后，发现SQL server语法就越重要，现在于我而言就是只要这个SQL server的逻辑搞通了，其他的全部都水到渠成

昨天遇到这么个需求，大概的意思就是要将每个课程的第一节课试看时长改成15分钟，看到这样的需求，第一步大概会想到的是通过聚合函数min(sectionorder)，然后group by(courseinfoid),就能查询出sectionid了

但是这样做是存在问题的，因为这样还得把sectionid放入group by的后面，这样根本无法得到我们想要的结果

机智如我，还是通过不错的搜商，查到了类似的SQL server语句，然后套用成功
最终代码

# 查询
select stuff(( select ','+ ltrim(SectionID) from (select SectionID
from(select  CourseInfoID  
           ,SectionOrder    
           ,SectionID
           ,min(SectionOrder)over(partition by CourseInfoID) as min_login_time
           ,row_number()over(partition by CourseInfoID order by SectionOrder asc) as ranking 
     from IntellManuSchool_CourseInfo_SectionInfo WHERE CourseInfoID <> 0 
    ) as temp
where ranking = 1) as b for xml path('')),1,1,'') as SectionID

# 根据查询的进行修改
update IntellManuSchool_CourseInfo_SectionInfo set Preview=15 where charindex(','+ltrim(SectionID)+',',','+(select stuff(( select ','+ ltrim(SectionID) from (select SectionID
from(select   CourseInfoID  
             ,SectionOrder    
             ,SectionID
             ,min(SectionOrder)over(partition by CourseInfoID) as min_login_time
             ,row_number()over(partition by CourseInfoID order by SectionOrder asc) as ranking 
     from IntellManuSchool_CourseInfo_SectionInfo WHERE CourseInfoID <> 0 
    ) as temp
where ranking = 1) as b for xml path('')),1,1,'') as SectionID)+',')>0

小编目前还没明白其中的原理，就如这些字段我很少用，看样子后面还得找些时间再恶补一下相关SQL server知识

参考代码SQL面试题变式：求出每个会员，最早一次购买对应的订单号

select   dimMemberID  
             ,dimDateID    
             ,salesNo
             ,min(dimDateID)over(partition by dimMemberID) as min_login_time
             ,row_number()over(partition by dimMemberID order by dimDateID asc) as ranking 
     from fct_sales WHERE dimMemberID <> 0 

# 求出每个会员，最早一次购买对应的订单号；
select  dimMemberID  
       ,min_login_time as dimDateID    
       ,salesNo
from(select   dimMemberID  
             ,dimDateID    
             ,salesNo
             ,min(dimDateID)over(partition by dimMemberID) as min_login_time
             ,row_number()over(partition by dimMemberID order by dimDateID asc) as ranking 
     from fct_sales WHERE dimMemberID <> 0 
    ) as temp
where ranking = 1;

昨天有一个在大学时期学英语的群突然热闹了起来
A问：“现在你们还在坚持学英语吗？”
B答：“不了，现在不如在校期间，一下班就根本不想动了…”
C说：“偶尔也就是想想，但是一有空又不想做了…”
D说：“哎，都是这样，间歇性踌躇满志，持续性混吃等死…”