月份 序号 姓名 分数
1 51 张三 5.4
2 51 张三 8.1
3 51 王五 4.2
1 52 李四 6.3
2 52 李四 3.2
3 52 李四 2.3
我想查出每个人一季度的总分加平均分加名次(分数高的在第一名)
查出来后应该是
姓名 一月总分 二月总分 三月总分 季度平均分 名次
张三 5.4 8.1
李四 6.3 3.2 2.3
王五 4.2
我知道季度平均分是(一月+二月+三月) /3
名次是 count(a.总分 > aa.总分)
我想知道怎么样查出我上面写的那样的格式,如果我把每月的总分用别名代替的话就不能查出平均分和名次 。
感谢各位大哥大姐帮忙,高手出招。
解决方案 »
- 分页存储过程,
- 如何写入 Sql Server 日志文件
- nvarchar的数据如:'24/08/2008 17:09:23' 怎么转换为 datetime格式???
- 难,高手请进来指导,菜鸟进来学习!100分
- 求一简单的SQL语句!
- 关于RAND()中的问题?
- 在SQL语句中,字段、表名都用变量带入(表和字段很多),做统计并且count,avg返回给变量,怎么做呀?!
- CASE 语句的用法
- T-SQL怎么访问远程数据库,和过滤字段内容是否有特殊字符
- 数据类型转换和字符串去零的问题
- Hibernate学习笔记(二)--管理Hibernate的缓存
- SQL Server 2005中插入XML数据的方法
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (月份 int,序号 int,姓名 nvarchar(4),分数 numeric(2,1))
insert into #T
select 1,51,'张三',5.4 union all
select 2,51,'张三',8.1 union all
select 3,51,'王五',4.2 union all
select 1,52,'李四',6.3 union all
select 2,52,'李四',3.2 union all
select 3,52,'李四',2.3
/*
姓名 一月总分 二月总分 三月总分 季度平均分 名次
张三 5.4 8.1
李四 6.3 3.2 2.3
王五 4.2
*/
;
with cte as
(
select 姓名,
SUM(case when 月份=1 then 分数 else 0 end) [一月总分],
SUM(case when 月份=2 then 分数 else 0 end) [二月总分],
SUM(case when 月份=3 then 分数 else 0 end) [三月总分],
sum(分数) 总分
from #T
group by 姓名
)
select 姓名,[一月总分],[二月总分],[三月总分],总分,
cast(总分*1.0/3 as decimal(19,2)) as 平均分,
排名=ROW_NUMBER() over(order by 总分 desc)
from cte /*
姓名 一月总分 二月总分 三月总分 总分 平均分 排名
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------
张三 5.4 8.1 0.0 13.5 4.50 1
李四 6.3 3.2 2.3 11.8 3.93 2
王五 0.0 0.0 4.2 4.2 1.40 3(3 行受影响)
*/
If object_id('ta') is not null
Drop table ta
Go
Create table ta(月份 int,序号 int,姓名 varchar(4),分数 numeric(2,1))
Go
Insert into ta
select 1,51,'张三',5.4 union all
select 2,51,'张三',8.1 union all
select 3,51,'王五',4.2 union all
select 1,52,'李四',6.3 union all
select 2,52,'李四',3.2 union all
select 3,52,'李四',2.3
Go
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+ltrim(月份)+'月总分]= sum(case when 月份='+ltrim(月份) + ' then 分数 else 0 end)'
from (select distinct 月份 from ta )a
exec('select 姓名,'+ @s + ',季度平均分=avg(分数) into tmp from ta group by 姓名')
select a.*,名次=(select count(1) from tmp where 季度平均分>= a.季度平均分)
from tmp a
drop table tmp
--Result:
/*姓名 1月总分 2月总分 3月总分 季度平均分 名次
---- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -----------
李四 6.3 3.2 2.3 3.933333 3
王五 .0 .0 4.2 4.200000 2
张三 5.4 8.1 .0 6.750000 1(所影响的行数为 3 行)
*/
--End
create table #T (月份 int,序号 int,姓名 nvarchar(4),分数 numeric(2,1))
insert into #T
select 1,51,'张三',5.4 union all
select 2,51,'张三',8.1 union all
select 3,51,'王五',4.2 union all
select 1,52,'李四',6.3 union all
select 2,52,'李四',3.2 union all
select 3,52,'李四',2.3
declare @sql varchar(8000)
set @sql='select 姓名'
select @sql=@sql+',['+ltrim(月份)+']=sum(case 月份 when '''+ltrim(月份)+''' then 分数 else 0 end)'
from (select distinct 月份 from #t)a
set @sql=@sql+',季度平均分=sum(分数)*1.0/(select count(distinct 月份) from #t) into # from #t group by 姓名;
select *,名次=(select count(1) from # where 季度平均分>=a.季度平均分) from # a'
exec(@sql)
insert into #T
select 1,51,'张三',5.4 union all
select 2,51,'张三',8.1 union all
select 3,51,'王五',4.2 union all
select 1,52,'李四',6.3 union all
select 2,52,'李四',3.2 union all
select 3,52,'李四',2.3select * from #Tselect 姓名,[1M],[2M],[3M] ,Total,(Total/3) as [Avg]
from
(
select 姓名,
sum(case when 月份=1 then 分数 else 0 end ) [1M],
sum(case when 月份=2 then 分数 else 0 end ) [2M],
sum(case when 月份=3 then 分数 else 0 end ) [3M]
,sum(分数)as Total
from #T group by 姓名
) aa
//result
李四 6.3 3.2 2.3 11.8 3.933333
王五 0.0 0.0 4.2 4.2 1.400000
张三 5.4 8.1 0.0 13.5 4.500000