大家好，
我刚刚开始学sql，有个问题想问大家。
比如我有一个四列的表（后两列现在是空的）：
Student_id Course_term Course_index Course_total_in_a_term
Student1 Fall2011
Student1 Fall2011
Student1 Spring2012
Student1 Spring2012
Student2 Fall2011
我想把第一列和第二列的值的组合的个数放到第四列。对于第三列，想放入从0开始到第四列的对应的值，以1为单位逐渐递增的数字。所以最后想要的结果如下：
Student_id Course_term Course_index Course_total_in_a_term
Student1 Fall2011 0 2
Student1 Fall2011 1 2
Student1 Spring2012 0 2
Student1 Spring2012 1 2
Student2 Fall2011 0 1
请问怎么用sql语言实现呢？谢谢大家！
sql mysql
------解决方案--------------------
分开实现吧，简单一点~
--1
update tb set Course_total_in_a_term=
from (select Student_id,Course_term,count(1)num from tb group by Student_id,Course_term)a
where tb.Student_id=a.Student_id and tb.Course_term=a.Course_term
--2
;with cte as(
select *,ROW_NUMBER() over(partition by Student_id,Course_term order by Course_total_in_a_term) rn
from tb
)update cte set Course_index=rn-1
------解决方案--------------------
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
Student_id varchar(50)
,Course_term varchar(50)
,Course_index int
,Course_total_in_a_term int
)
insert into tb(Student_id,Course_term)
SELECT 'Student1','Fall2011' UNION ALL
SELECT 'Student1','Fall2011' UNION ALL
SELECT 'Student1','Spring2012' UNION ALL
SELECT 'Student1','Spring2012' UNION ALL
SELECT 'Student2','Fall2011'
GO
;WITH MU AS (
SELECT
Student_id
,Course_term
,ROW_NUMBER() OVER(PARTITION BY STUDENT_ID,COURSE_TERM ORDER BY GETDATE())-1 COL1
,(SELECT COUNT(1) FROM TB T2 WHERE T2.Student_id=T1.Student_id AND T2.Course_term=T1.Course_term) COL2
,Course_index
,Course_total_in_a_term
FROM TB T1
)
UPDATE MU SET Course_index=COL1,Course_total_in_a_term=COL2
GO
SELECT *
FROM TB
/*
Student1 Fall2011 0 2
Student1 Fall2011 1 2
Student1 Spring2012 0 2
Student1 Spring2012 1 2
Student2 Fall2011 0 1
*/
------解决方案--------------------
下面的代码适合2005及以后的版本：
--drop table tb
CREATE TABLE TB(
Student_id varchar(50)
,Course_term varchar(50)
,Course_index int
,Course_total_in_a_term int
)
insert into tb(Student_id,Course_term)
SELECT 'Student1','Fall2011' UNION ALL