对表1按课程编号分组，找出最小星期的那条记录，请前辈指导！先感谢前辈了！
对表1按课程编号分组，找出最小星期的那条记录，请前辈指导！
表1
课程编号 星期 移动电话 姓名
20001 1 13808140811 张三
20001 2 13808140811 张三
20003 1 13808140813 张三
20004 3 13808140814 张四
20004 5 13808140814 张四
20004 1 13808140814 张四
20005 1 13808140815 张五
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [课程编号] varchar(100), [星期] varchar(100), [移动电话] varchar(100), [姓名] varchar(100));
insert #temp
select '20001','1','13808140811','张三' union all
select '20001','2','13808140811','张三' union all
select '20003','1','13808140813','张三' union all
select '20004','3','13808140814','张四' union all
select '20004','5','13808140814','张四' union all
select '20004','1','13808140814','张四' union all
select '20005','1','13808140815','张五'
--SQL:
;WITH cte AS
(
select rowid=ROW_NUMBER() OVER(PARTITION BY [课程编号] ORDER BY 星期), * from #temp
)
SELECT * FROM cte
WHERE rowid = 1--方法3
SELECT b.* FROM
(SELECT DISTINCT [课程编号] FROM #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM #temp m WHERE m.[课程编号] =a.[课程编号] ORDER BY 星期) b
create table #tb
(课程编号 varchar(10), 星期 int, 移动电话 varchar(20), 姓名 varchar(10))
insert into #tb
select '20001', 1, '13808140811', '张三' union all
select '20001', 2, '13808140811', '张三' union all
select '20003', 1, '13808140813', '张三' union all
select '20004', 3, '13808140814', '张四' union all
select '20004', 5, '13808140814', '张四' union all
select '20004', 1, '13808140814', '张四' union all
select '20005', 1, '13808140815', '张五'
select A.*
from #tb A
inner join (select 课程编号,MIN(星期) as 星期 from #tb group by 课程编号)B
on A.课程编号=B.课程编号 and A.星期=B.星期
/*
课程编号星期移动电话姓名
20001113808140811张三
20003113808140813张三
20004113808140814张四
20005113808140815张五
*/