有行统计项和列统计项的行列转换,以及EXCEL导入SQL的简单应用
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[单元]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[单元]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[统计]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[统计]
GO
Create Table 单元(
课程代码 int NOT NULL ,
课程名称 varchar(50) NOT NULL ,
单元 int NOT NULL
)
Create Table 统计(
ID int IDENTITY (1, 1) NOT NULL ,
院系名称 varchar(50) NOT NULL ,
助学点名称 varchar(50) NOT NULL ,
专业代码 varchar(50) ,
专业名称 varchar(50) ,
年级 int ,
课程名称 varchar(50) NOT NULL ,
课程代码 int NOT NULL ,
人数 int NOT NULL
)
GO
INSERT INTO 统计(院系名称,助学点名称,专业代码,专业名称,年级,课程代码,课程名称,人数)
SELECT 院系名称,助学点名称,专业代码,专业名称,年级,课程代码,课程名称,报考人数 FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','EXCEL 8.0;IMEX=1;HDR=YES;DATABASE=E:\报考.XLS',长沙市$)
INSERT INTO 单元(课程代码,课程名称,单元)
SELECT 代码,课程名称,单元 FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','EXCEL 8.0;IMEX=1;HDR=YES;DATABASE=E:\单元.XLS',单元$)
GO
SET NOCOUNT ON
--SELECT * FROM 统计
/**/
--按课程统计
SELECT t1.课程名称,t2.课程代码,t1.小计
FROM(
SELECT
(CASE GROUPING(课程名称) WHEN 1 THEN '总计' ELSE 课程名称 END) AS 课程名称,SUM(人数) AS 小计
FROM 统计
GROUP BY 课程名称 WITH ROLLUP
) AS t1
LEFT JOIN 单元 AS t2 ON t1.课程名称=t2.课程名称
ORDER BY t2.课程代码 DESC
/*
--按单元统计
select b.*,(b.单元1+b.单元2+b.单元3+b.单元4+b.单元5+b.单元6) as 学院统计
from(select
(case grouping(院系名称) when 1 then '总计' else 院系名称 end) as 学院名称,
sum(case a.单元 when 1 then a.人数 else 0 end) as 单元1,
sum(case a.单元 when 2 then a.人数 else 0 end) as 单元2,
sum(case a.单元 when 3 then a.人数 else 0 end) as 单元3,
sum(case a.单元 when 4 then a.人数 else 0 end) as 单元4,
sum(case a.单元 when 5 then a.人数 else 0 end) as 单元5,
sum(case a.单元 when 6 then a.人数 else 0 end) as 单元6
from(select n.院系名称,n.课程代码,n.人数,m.单元 from 单元 m,统计 n where m.课程代码=n.课程代码) as a
group by 院系名称 with rollup) as b
*/
drop table 单元
drop table 统计