当前位置: 代码迷 >> SQL >> SQL LEN() 函数 ,case when,集合函数的用法
  详细解决方案

SQL LEN() 函数 ,case when,集合函数的用法

热度:86   发布时间:2016-05-05 10:42:42.0
SQL LEN() 函数 ,case when,聚合函数的用法
SELECT aa.[User_Id],cc.[User_Name],dd.Name AS DepName,aa.Module_Id,aa.Module_Name,
SUM(CASE aa.Operate_Type WHEN '2' THEN OperateNum END ) AS 'AddNum', 
SUM(CASE aa.Operate_Type WHEN '3' 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() 函数
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
  相关解决方案