Emotion about T-SQL

 

I have not learned SQL server formally, all the knowledge obtained in the system in the scattered, feel not system, disorganized, the more pondering, the more I think the T-SQL function is powerful.

For example, if I am working on a project and want to count the data in the database, the sample data is as follows:

Table 1 (raw data)

Student ID name department name Class Name

0001 Li Qing Information System software 01

0002 Wang Wei Information System software 01

0003 Zhang Hua Information Department application 01

10001 Sun Hao Machinery Manufacturing 05

10002 Wang Bo Machinery Manufacturing 05

Table 2 (table of participants in an activity)

Student ID name department name Class Name

0001 Li Qing Information System software 01

0002 Wang Wei Information System software 01

10001 Sun Hao Machinery Manufacturing 05

10002 Wang Bo Machinery Manufacturing 05

Note that table 2 may be a subset of table 1.

I want to obtain the following statistical table:

Department name class name original number of participants ratio (%)

Information System software 01 45 23 51.11

Information System software 01 41 9 21.95

Information System Application 01 39 0 0

Mechanical Manufacturing 05 36 19 52.78

Mechanical Manufacturing 05 42 39 92.86

There are a lot of solutions to the problems. I put this job into the SQL Server to do, using a T-SQL to generate a view, it is easy to achieve the goal:

Step 1: Count the original table and generate the view v1:

Select top (100) percent case when grouping (Class Name) = 0 THEN Department name when grouping (Department name) = 1 then' total 'else Department name end as department name, case when grouping (Class Name) = 0 THEN class name when grouping (class name) = 1 and grouping (Department name) = 0 then' total 'else class name end as Class Name, COUNT (ID) AS total number of people

FROM dbo. v_student

Group by department name, class name WITH ROLLUP

Order by department name, class name

Step 2: Calculate the activity table and generate view v2

Select top (100) percent case when grouping (Class Name) = 0 THEN Department name when grouping (Department name) = 1 then' total 'else Department name end as department name, case when grouping (Class Name) = 0 THEN class name when grouping (class name) = 1 and grouping (Department name) = 0 then' total 'else class name end as Class Name, COUNT (id) AS trainee

FROM dbo. v_student_hd

Group by department name, class name WITH ROLLUP

Order by department name, class name

Step 3: make further statistics on the two views:

SELECT. department name,. class Name,. total number, B. trainee, CONVERT (float, B. trainee)/CONVERT (float,. total number of users) * 100 AS Ratio

FROM dbo. v1 AS a left outer join dbo. v2 AS B ON. department name = B. department name AND. class Name = B. class Name OR. department name = B. department name and isnull (. class Name, 0) = ISNULL (B. class Name, 0)

The above T_ SQL statements are complex. I will not analyze them in detail. If you are interested, you can check the information.

The actual effect is as follows: