计算两列的独一的值组合的出现次数

2013-11-15

计算两列的唯一的值组合的出现次数大家好，我刚刚开始学sql，有个问题想问大家。比如我有一个四列的表（后两列

计算两列的唯一的值组合的出现次数
大家好，

我刚刚开始学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_termint
)
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
/*
Student1Fall201102
Student1Fall201112
Student1Spring201202
Student1Spring201212
Student2Fall201101
*/

[解决办法]
下面的代码适合2005及以后的版本：

--drop table tb

CREATE TABLE TB(
Student_id  varchar(50)
,Course_term   varchar(50)
,Course_index   int
,Course_total_in_a_termint
)

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

select student_id,
       course_term,
       ROW_NUMBER() over(partition by Student_id,Course_term
                             order by @@servername) as Course_index,

       COUNT(*) over(partition by student_id,course_term) as Course_total_in_a_term
from tb
/*
student_idcourse_termCourse_indexCourse_total_in_a_term
Student1Fall20111            2
Student1Fall20112            2
Student1Spring20121            2
Student1Spring20122            2
Student2Fall20111            1
*/

热点排行

SQL Server

计算两列的独一的值组合的出现次数