前言
记录常用的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 = '售前销售'
示例:
UserFullName | DomainName | UserId | RoleName |
---|---|---|---|
张三 | zhangsan@sample.com | d04cbf6f-4c9d-321-be37-0117fa06c22d | 售前销售 |
李四 | lisi@sample.com | 41238369-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
示例:
UserFullName | DomainName | UserId | RoleName |
---|---|---|---|
张三 | zhangsan@sample.com | d04cbf6f-4c9d-321-be37-0117fa06c22d | 售前销售,销售主管 |
李四 | lisi@sample.com | 41238369-4c9d-ee11-be36-0017fa078214 | 售前销售,销售总监,系统管理员 |
感谢您的耐心阅读!来选个表情,或者留个评论吧!