SQL Server---Troubleshoot SQL Server data subtotals with case

SQL Server---Troubleshoot SQL Server data subtotals with case

Nearly half a year has been responsible for the development and maintenance of a city's personnel file management system, before the customer gave a table, need me to summarize the data, and then fill in the table.

Specific needs: Statistics of how many different letters (or reports) each worker has printed in a certain period of time.

The original idea

1. Identify the staff using the system

Select Realname as ' name ' from T_userwhereuserid in (select distinct UserID from T_operationlog);

Operation Result:

2. Identify the number of different letters printed by different employees (the following is an example of a cadre letter of recommendation)

Select Realname as ' name ', COUNT (*) as ' cadre Letter of introduction ' from T_lettersrecord as l,t_user as Uwherel.userid=u.userid and Lettertype = ' Cadre Letter of introduction ' and L.userid in (select distinct UserID from T_operationlog) group by  L.userid,u.realname;

Operation Result:

But this time there's a problem when we count a type of letter, COUNT (*) is OK, but how do we use the Count function when we're counting other letters? At the same time Lettertype (letter type) as a condition is also different.

As a rookie, the first thing I thought of was not a bunch of your awesome functions in SQL Server, but the idea of using an array-like object to put Lettertype in, not supporting data in SQL Server. So I used a variable table to do something like that.

--Declare a table variable declare @t table (ID int,name varchar) INSERT into @t values (1, ' Cadre Letter of recommendation ') insert into @t values (2, ' Archive letters ') SELECT  realname as ' name ', COUNT (*) from T_lettersrecord as l,t_user as Uwhere L.userid=u.userid and  lettertype in (select n Ame from @t) and L.userid in (select distinct Useridfrom t_operationlog)  GROUP by  L.userid,u.realname,lettertype

Operation Result:

From the results of the operation we can see that the final result is not the horizontal effect I want, so the question came out how to let it have a horizontal effect. I thought of using the left join (left join ... on) to save the results of each isolated to a temporary table, and then use the temporary tables in turn with the left join ... on I finally get the results I want.

The actual practice

The above idea in I think of the moment I was denied, this is the cycle, but also the efficiency of the connection query will be very slow and later people maintenance is also more troublesome. With my colleague's reminder, I used the case to solve the problem.

The final code is as follows:

Select Realname as ' name ',    count (case when lettertype= ' cadre letter of recommendation ' then ' 1 ' end) as ' cadre Letter of introduction ',    count (case when Lettertype= ' Transmitting the file notification ' then ' 1 ' end ' as ' transfer note ',    ...    Count (case when Lettertype= ' title approval form ' Then ' 1 ' end) as ' title approval form ',    COUNT (case when lettertype= ' Party information table ' then ' 1 ' end) as ' Party information Table ',    COUNT (case when lettertype= ' other ' then ' 1 ' end) as ' other ' from T_lettersrecord as l,t_user as Uwhere L.userid=u.userid and L.userid in (select distinct Useridfrom t_operationlog) group by  L.userid,u.realname;

The result of the final operation:

Fully meet the user's final requirements. Before writing this code also asked a colleague next to her, her first reaction is not, so the result is that she really does not, in fact, if the task decomposition is like my original idea is also able to write, after writing to optimize is definitely another feeling (try to get the problem first is always right).

Next Boven introduce Case...when ... The specific usage.

SQL Server---Troubleshoot SQL Server data subtotals with case