SELECT AA. [User_id],cc.[user_name],dd. Name as DEPNAME,AA. Module_id,aa. Module_name,
SUM (case AA. Operate_type when ' 2 ' and then Operatenum END ' as ' addnum ',
SUM (case AA. Operate_type when ' 3 ' and then Operatenum END ' as ' editnum ',
SUM (case AA. Operate_type when ' 4 ' then operatenum END) as ' Delnum '
From (
SELECT A.[user_id],a.module_id,c.module_name,a.operate_type,count (1) as Operatenum from dbo. Sys_user_log as a
INNER JOIN dbo. Sys_module as C on a.module_id = c.module_id
WHERE LEN (a.module_id) = 6 and (A.operate_type = ' 2 ' or a.operate_type = ' 3 ' or a.operate_type = ' 4 ')//len () function
GROUP by A.[user_id],a.module_id,c.module_name,a.operate_type
) as AA
INNER JOIN dbo. Sys_user as CC on AA. [user_id] = cc.[user_id]
INNER JOIN dbo. Sys_depinfo as DD on CC. depinfo_id = dd. Id
GROUP by AA. [User_id],cc.[user_name],dd. Name,aa. Module_id,aa. Module_name
ORDER by AA. [User_id],aa. module_id
SQL LEN () function, case-when, usage of aggregate functions
