求sql分组查询语句

Question

表A
班级  学号  次数  时间
1     1001   1    20100101
1     1001   2     20100102
1     1002   1    20100202
2     2001   1    20100506
2     2002   1    20100809
2     2002   2    20100810表B
班级  学号   时间
1     1001  20101101
1     1002  20101101
2     2001  20101101
2     2002  20101101
3     3001  20101101
要求将B表的数据追加到A表中，如果B表的学号没有在A出现过，次数是1，否则，次数是最大的次数加1。
学号3001追加到A的形式是 3   3001  1  20101101
学号1001追加到A的形式是 1   1001  3  20101101 （A表中原来1001的最大次数是2）
求简洁高效的语句

Answer 1

--> 测试数据: [表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A] (班级 int,学号 int,次数 int,时间 datetime)
insert into [表A]
select 1,1001,1,'20100101' union all
select 1,1001,2,'20100102' union all
select 1,1002,1,'20100202' union all
select 2,2001,1,'20100506' union all
select 2,2002,1,'20100809' union all
select 2,2002,2,'20100810'
--> 测试数据: [表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B] (班级 int,学号 int,时间 datetime)
insert into [表B]
select 1,1001,'20101101' union all
select 1,1002,'20101101' union all
select 2,2001,'20101101' union all
select 2,2002,'20101101' union all
select 3,3001,'20101101'insert into [表A]
select 班级,学号,(select isnull(MAX(次数),0)+1 from [表A] where 学号=t.学号) as 次数,时间
from [表B] tselect * from [表A] order by 班级,学号/*
班级 学号 次数 时间
1 1001 1 2010-01-01 00:00:00.000
1 1001 2 2010-01-02 00:00:00.000
1 1001 3 2010-11-01 00:00:00.000
1 1002 2 2010-11-01 00:00:00.000
1 1002 1 2010-02-02 00:00:00.000
2 2001 1 2010-05-06 00:00:00.000
2 2001 2 2010-11-01 00:00:00.000
2 2002 3 2010-11-01 00:00:00.000
2 2002 1 2010-08-09 00:00:00.000
2 2002 2 2010-08-10 00:00:00.000
3 3001 1 2010-11-01 00:00:00.000
*/

Answer 2

--借用#1演示数据,用一条语句似乎不行（2005）--存在的最大加1
update a set 次数 = 次数 + 1 
from (select rowid = row_number() over(partition by a.班级,a.学号 order by a.次数 desc),a.次数 from [表A] a  join [表B] b on a.班级 = b.班级 and a.学号 = b.学号) a
where a.rowid = 1--不存在的新增
insert [表A]
select 班级,学号,1,时间 from [表B] b where not exists (select 1 from [表A] where 班级 = b.班级 and 学号 = b.学号)select * from [表A]
/*
班级          学号          次数          时间
----------- ----------- ----------- -----------------------
1           1001        1           2010-01-01 00:00:00.000
1           1001        3           2010-01-02 00:00:00.000
1           1002        2           2010-02-02 00:00:00.000
2           2001        2           2010-05-06 00:00:00.000
2           2002        1           2010-08-09 00:00:00.000
2           2002        3           2010-08-10 00:00:00.000
3           3001        1           2010-11-01 00:00:00.000(7 行受影响)
*/

Answer 3

--存在,最大加1
update tbA set 次数=(select max(次数)+1 from tba t where t.学号=tba.学号) from tba where exists(select 1 from tbB where tbB.学号=tba.学号)
--不存在,插入,次数为1
insert into tba
select tbB.班级,tbb.学号,1,tbb.时间 from tbB  where not exists(select 1 from tb where tbB.学号=tba.学号)

Answer 4

insert into [表A]select  班级,学号,(select count(次数) from  [表A] where 班级=b.班级 and  学号=b.学号)+1,时间 from  [表B] b

Answer 5

IF object_id('t_a') IS NOT NULL
DROP TABLE t_a
go
CREATE TABLE t_a(班级 int,学号 int,次数 int,时间 datetime)
INSERT INTO t_a
SELECT 1, 1001, 1, '20100101' UNION ALL 
SELECT 1, 1001, 2, '20100102' UNION ALL 
SELECT 1, 1002, 1, '20100202' UNION ALL 
SELECT 2, 2001, 1, '20100506' UNION ALL 
SELECT 2, 2002, 1, '20100809' UNION ALL 
SELECT 2, 2002, 2, '20100810'
go
IF object_id('t_b') IS NOT NULL
DROP TABLE t_b
go
CREATE TABLE t_b(班级 int,学号 int,时间 datetime)
INSERT INTO t_b
SELECT 1, 1001, '20101101' UNION ALL
SELECT 1, 1002, '20101101' UNION ALL
SELECT 2, 2001, '20101101' UNION ALL
SELECT 2, 2002, '20101101' UNION ALL
SELECT 3, 3001, '20101101'SELECT * FROM t_a
SELECT * FROM t_b
INSERT INTO t_a 
SELECT 班级,学号,次数= isnull((select max(次数) from t_a 
                               where 学号=t_b.学号 
                               group by 学号),0)+1,时间
FROM t_b
SELECT * FROM t_a ORDER BY 班级,学号,次数1 1001 1 2010-01-01 00:00:00.000
1 1001 2 2010-01-02 00:00:00.000
1 1001 3 2010-11-01 00:00:00.000
1 1002 1 2010-02-02 00:00:00.000
1 1002 2 2010-11-01 00:00:00.000
2 2001 1 2010-05-06 00:00:00.000
2 2001 2 2010-11-01 00:00:00.000
2 2002 1 2010-08-09 00:00:00.000
2 2002 2 2010-08-10 00:00:00.000
2 2002 3 2010-11-01 00:00:00.000
3 3001 1 2010-11-01 00:00:00.000