IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [unit]') And ObjectProperty (ID, n'istable ') = 1) DROP TABLE [DBO]. [Unit]
If Exists (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [statistics]') And ObjectProperty (ID, n'istable ') = 1) DROP TABLE [DBO]. [Statistics]
Go
Create Table Unit (Course Code Int Not Null, Course Name VARCHAR (50) Not NULL, Unit Int Not NULL
Create Table Statistics (ID Int Id Id Id Intity (1, 1) NOT NULL, Department Name Varchar (50) Not Null, Assist Center Name Varchar (50) Not Null, Professional Code Varchar (50), Professional Name Varchar (50), Grade INT, Course Name VARCHAR (50) Not Null, Code Int Not Null, Number INT NULL
Go
INSERT INTO statistics (department name, help point name, professional code, professional name, grade, course code, course name, number) SELECT department name, help name, professional code, professional name, grade, courses, Course name, number of applicants fromopenrowset ('Microsoft.jet.OleDb.4.0', 'Excel 8.0; IMEX = 1; HDR = YES; Database = E: / Applications .xls', Changsha City $)
INSERT INTO unit (course code, course name, unit) SELECT code, course name, unit fromopenrowset ('microsoft.jet.OleDb.4.0', 'Excel 8.0; IMEX = 1; HDR = YES; Database = E: / unit. XLS ', unit $)
Go
Set nocount on
--Select * from statistics
/ ** / - According to the courses, SELECT T1. Course name, T2. Course code, T1. Small gauge from (SELECT (CASE Grouping (course name) When 1 THEN 'total' ELSE course Name END) AS course name, SUM (Number) AS small gauges from statistics group by course name with rollup) AS T1 Left JOIN unit AS T2 on t1. Course name = T2. Course name ORDER BY T2. Code DESC
