# 统计问题

znswhx 2008-03-05 09:32:23

id rq mr dd
1 2005-5-5 aaa www
2 2005-2-6 a f
3 2005-6-9 w w
4 2005-6-9 f f
5 2005-8-9 c c
6 2005-4-7 a a

2005-01 2005-02 2005-03 2005-04 2005-05 2005-06
0 1 0 1 1 2
...全文
39 点赞 收藏 6

6 条回复

tcxx2008 2008-03-05

honghuali 2008-03-05
``````DECLARE @TABLE TABLE(id INT, rq VARCHAR, mr VARCHAR, dd VARCHAR)
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT CONVERT(VARCHAR(7), rq, 102), COUNT(rq)
FROM @TABEL  WHERE CONVERT(VARCHAR(7),rq, 102) >= @STARTDATE AND CONVERT(VARCHAR(7), rq, 102) <=@ENDDATE
GROUP BY CONVERT(VARCHAR(7), rq, 102)``````

dawugui 2008-03-05
``````select m.rq , isnull(n.cnt,0) cnt from
(
select '2005-01' rq union '2005-02' union '2005-03' union '2005-04' union '2005-05' union '2005-06'
) m
left join
(
select convert(varchar(7),rq,120) rq , count(*) cnt from tb group by convert(varchar(7),rq,120)
) n
on m.rq = n.rq
``````

``````/*

(爱新觉罗.毓华 2007-11-18于海南三亚)

Name Subject Result

*/

-------------------------------------------------------------------------
/*

---------- ----------- ----------- -----------

*/

create table tb
(
Name    varchar(10) ,
Subject varchar(10) ,
Result  int
)

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go

--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*

---------- ----------- ----------- -----------

*/

--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*

---------- ----------- ----------- -----------

*/

-------------------------------------------------------------------
/*加个平均分，总分

---------- ----------- ----------- ----------- -------------------- -----------

*/

--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*

---------- ----------- ----------- ----------- -------------------- -----------

*/

--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*

---------- ----------- ----------- ----------- -------------------- -----------

*/

drop table tb

---------------------------------------------------------
---------------------------------------------------------
/*

Name       Subject Result
---------- ------- -----------

*/

create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)

select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end

--------------------------------------------------------------------
/*加个平均分,总分
Name       Subject     Result
---------- -------    --------------------

*/

select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb1``````

MS-SQL Server

3.3w+

MS-SQL Server相关内容讨论专区

2008-03-05 09:32