求一条SQL语句（在线等解）
求：每个学生每个月迟到总数及占总数的百分比,
表a记录的是学生每天迟到信息
表a：id,stucode(学生编号),Reas(迟到理由),lateDate(迟到日期)，id为主键
相关数据：
id stucode Reas lateDate
1001 001 2013-01-02
1002 002 2013-01-12
1003 001 2013-01-14
1004 001 2013-01-15
1005 003 2013-03-01
1006 004 2013-03-01
1007 001 2013-03-02
1008 002 2013-03-04
1009 003 2013-5-1
结果：
学生标号 月份 每月迟到次数 比例
001 2013-01 3 30%
001 2013-03 1 11%
002 2013-01 1 11%
002 2013-03 1 11%
003 2013-03 1 11%
003 2013-05 1 11%
004 2013-03 1 11% SQL 百分比
[解决办法]
create table tab(
id int,stucode varchar(20),Reas varchar(20),lateDate datetime
)
insert into tab
select '1001','001','','2013-01-02' union all
select '1002','002','','2013-01-12' union all
select '1003','001','','2013-01-14' union all
select '1004','001','','2013-01-15' union all
select '1005','003','','2013-03-01' union all
select '1006','004','','2013-03-01' union all
select '1007','001','','2013-03-02' union all
select '1008','002','','2013-03-04' union all
select '1009','003','','2013-05-01'
-------------查询语句--------------------
with cte as
(
select id,stucode,left(convert(varchar,lateDate,121),7)as lateDate from tab
),cte1 as
(
select stucode N'学生编号',lateDate N'月份',count(*)as N'每月迟到次数'
from cte c
group by stucode,lateDate
)
select 学生编号,月份,[每月迟到次数],
convert(varchar,(convert(numeric(18,2),(convert(numeric(18,2),[每月迟到次数])/(select count(*) from cte))*100)))+'%' as '比例'
from cte1
order by 学生编号
--查询结果----------------------------------------------
学生编号 月份 每月迟到次数 比例
-------------------- -------------- ----------- -------------------------------
001 2013-01 3 33.33%
001 2013-03 1 11.11%
002 2013-01 1 11.11%
002 2013-03 1 11.11%
003 2013-03 1 11.11%
003 2013-05 1 11.11%
004 2013-03 1 11.11%
(7 行受影响)
select stucode as 学生标号,month(latedate) as 月份,count(stucode) as 每月迟到数
,count(stucode)/(select count(stucode) from a where month(stucode)=月份 )*100. as 比例
from a group by month(latedate)
create table #tab(
id int,stucode varchar(20),Reas varchar(20),lateDate datetime
)
insert into #tab
select '1001','001','','2013-01-02' union all
select '1002','002','','2013-01-12' union all
select '1003','001','','2013-01-14' union all
select '1004','001','','2013-01-15' union all
select '1005','003','','2013-03-01' union all
select '1006','004','','2013-03-01' union all
select '1007','001','','2013-03-02' union all
select '1008','002','','2013-03-04' union all
select '1009','003','','2013-05-01'
;
WITH a1 AS
(
SELECT convert(CHAR(7),lateDate,20) mm,stucode
FROM #tab
)
SELECT mm,stucode,CAST(CAST(100.*COUNT(*)/(SELECT COUNT(*) FROM a1) AS decimal(4,2)) AS VARCHAR)+'%'
FROM a1
GROUP BY mm,stucode
