前言

记录常用的SQL,大部分以为查询为主。

查询

查询视图 Id

  • 根据视图名称查询
SELECT name AS 'View name',
       savedqueryid AS 'View Id',
       returnedtypecode AS 'Owning entity',
       description as 'Description'
FROM   savedquery
WHERE  name = '填写视图名称';
  • 查询某个视图下所有的视图
SELECT 
    v.SavedQueryId AS 'View Id', 
    v.Name AS 'View name',
    v.returnedtypecode AS 'Owning entity'
FROM 
    SavedQuery v
WHERE 
    returnedtypecode = 'account'

查询报表/报表 Id

SELECT reportid,
       name,
       filename,
       description,
       owneridname,
       componentstatename,
       createdon,
       createdbyname
FROM   report;

查询附件容量

随手记,不好用。

-- 1 MB = 1024 * 1024 Bytes = 1,048,576 Bytes
SELECT SUM(filesize) / 1048576 AS TotalFileSizeMB
FROM   annotation;

查询某个安全角色分配给哪些用户

SELECT 
    systemuser.fullname AS UserFullName,
    systemuser.domainname AS DomainName,
    systemuser.systemuserid AS UserId,
    role.name AS RoleName
FROM 
    systemuserroles
INNER JOIN 
    systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
INNER JOIN 
    role ON systemuserroles.roleid = role.roleid
WHERE 
    role.name = '售前销售'

示例:

UserFullNameDomainNameUserIdRoleName
张三zhangsan@sample.comd04cbf6f-4c9d-321-be37-0117fa06c22d售前销售
李四lisi@sample.com41238369-4c9d-ee11-be36-0017fa078214售前销售

列出用户的所有安全角色

SELECT 
    su.fullname AS UserFullName,
    su.domainname AS DomainName,
    su.systemuserid AS UserId,
    STRING_AGG(r.name, ', ') AS RoleNames
FROM 
    systemuserroles sur
INNER JOIN 
    systemuser su ON sur.systemuserid = su.systemuserid
INNER JOIN 
    role r ON sur.roleid = r.roleid
GROUP BY 
    su.fullname,
    su.domainname,
    su.systemuserid
ORDER BY 
    su.fullname

示例:

UserFullNameDomainNameUserIdRoleName
张三zhangsan@sample.comd04cbf6f-4c9d-321-be37-0117fa06c22d售前销售,销售主管
李四lisi@sample.com41238369-4c9d-ee11-be36-0017fa078214售前销售,销售总监,系统管理员