可以把這次查找出來的作為臨時表#t1
另外寫個查找上次考試名次的,作為#t2
然後#t1 left join #t2 似乎就可以了
另外寫個查找上次考試名次的,作為#t2
然後#t1 left join #t2 似乎就可以了
解决方案 »
- 推荐几本好书,各位大牛。
- sqlserver 2005
- 这个sql查询怎么解决?
- 求两表关系的查询(非Inner Join)
- 急~~~~~送分问题
- service broker 不能启动。
- 多表联合查询问题, 高手帮看下。
- 关于日期转换的问题(在线等待,谢谢帮忙)
- VISTA系统的么而且装的SQL Server 2005的问题
- 谁用过VB+OCRACAL ,与VB+SQL Server 有什么不同? SQL语句是否相同 ?
- sql注册失败!急急!!在线等!
- 安装sql出错!Command line option syntax error. Type Command/? for help.在线等!
if exists(select [name] from sysobjects where [name]='t' and xtype='u')
drop table t
if exists(select [name] from sysobjects where [name]='t1' and xtype='u')
drop table t1
if exists(select [name] from sysobjects where [name]='t2' and xtype='u')
drop table t2
if exists(select [name] from sysobjects where [name]='t3' and xtype='u')
drop table t3
if exists(select [name] from sysobjects where [name]='t4' and xtype='u')
drop table t4/*測試數據*/
--examied=1代表這次考試,examied=0代表上次考試
--有幾個欄位為圖方便沒放上去,不影響結果create table t(classname varchar(10),stname varchar(10),subjectname varchar(10), float,examied int,stid varchar(10))
insert into t
select 'g2c1','liyi','eng',95,1,'20'
union
select 'g2c1','liyi','chin',100,1,'20'
union
select 'g2c1','liyi','math',105,1,'20'
union
select 'g2c1','lihua','eng',110,1,'2'
union
select 'g2c1','lihua','chin',120,1,'2'
union
select 'g2c1','lihua','math',125,1,'2'
union
select 'g2c1','liyi','eng',95,0,'20'
union
select 'g2c1','liyi','chin',100,0,'20'
union
select 'g2c1','liyi','math',105,0,'20'
union
select 'g2c1','lihua','eng',85,0,'2'
union
select 'g2c1','lihua','chin',80,0,'2'
union
select 'g2c1','lihua','math',70,0,'2'/**/
declare @sql varchar(8000),@sql1 varchar(8000),@sql2 varchar(8000)
set @sql=''
select @sql=@sql+',max( case when subjectname='''+subjectname+''' then end ) as '+subjectname from t group by subjectname
set @sql1='select * into t1 from (select classname,stid,stname'+@sql+',sum() as total from t where examied=0 group by classname,stid,stname ) A'
set @sql2='select * into t2 from (select classname,stid,stname'+@sql+',sum() as total from t where examied=1 group by classname,stid,stname ) A'
exec(@sql1)
exec(@sql2)select *,temp1=identity(int,1,1) into t3 from t1 order by total descselect *,temp2=identity(int,1,1) into t4 from t2 order by total descselect t4.*,t3.total as lasttotal,t3.temp1 as lastpm,(t3.temp1-t4.temps) as qk from t4 left join t3 on t3.stid=t4.stid/*The result:*/
classname stid stname chin eng math total temp2 lasttotal lastpm qk
-------------------------------------------------------------------------
g2c1 2 lihua 120.0 110.0 125.0 355.0 1 235.0 2 1g2c1 20 liyi 100.0 95.0 105.0 300.0 2 300.0 1 -1