如何从SolidWorks SQL数据库获取项目中文档的使用数量？

你当前的查询存在别名重复问题（两个Child Part、两个Child File Type），先修正该问题，再添加使用数量字段——这里的使用数量指子文档被父文档引用的次数，可通过分组统计xrefs表的关联记录实现：

修改后的查询语句 #

select 
    p2.path [Child Path], 
    d.filename [Child Filename], 
    LEFT(d.filename,CHARINDEX('.',d.filename)-1) as 'Child Part Name',
    RIGHT(d.filename,CHARINDEX('.',REVERSE(d.filename))-1) as 'Child File Type',
    p1.path [Parent Path], 
    par.filename [Parent Filename],
    LEFT(par.filename,CHARINDEX('.',par.filename)-1) as 'Parent Part Name',
    RIGHT(par.filename,CHARINDEX('.',REVERSE(par.filename))-1) as 'Parent File Type',
    COUNT(x.xrefdocument) as 'Usage Count' -- 添加使用数量统计字段
from documents d
inner join xrefs x on x.xrefdocument = d.documentid and x.xrefrevnr = d.latestrevisionno
inner join documents par on par.documentid = x.documentid and par.latestrevisionno = x.revnr
inner join documentsinprojects dp1 on dp1.documentid = par.documentid
inner join documentsinprojects dp2 on dp2.documentid = d.documentid
inner join projects p1 on p1.projectid = dp1.projectid
inner join projects p2 on p2.projectid = dp2.projectid
group by 
    p2.path, 
    d.filename, 
    LEFT(d.filename,CHARINDEX('.',d.filename)-1),
    RIGHT(d.filename,CHARINDEX('.',REVERSE(d.filename))-1),
    p1.path, 
    par.filename,
    LEFT(par.filename,CHARINDEX('.',par.filename)-1),
    RIGHT(par.filename,CHARINDEX('.',REVERSE(par.filename))-1)

关键说明 #

• COUNT(x.xrefdocument)：统计每个子文档被父文档引用的次数，即你需要的使用数量。
• 修正了原查询中重复的字段别名，避免字段冲突。
• 按照SQL语法要求，所有非聚合字段必须包含在group by子句中。

如果需要统计子文档在指定项目内的总使用次数（不区分具体父文档），可使用以下查询：

select 
    p2.path [Child Path], 
    d.filename [Child Filename], 
    LEFT(d.filename,CHARINDEX('.',d.filename)-1) as 'Child Part Name',
    RIGHT(d.filename,CHARINDEX('.',REVERSE(d.filename))-1) as 'Child File Type',
    COUNT(DISTINCT par.documentid) as 'Total Project Usage Count' -- 统计项目内不同父文档的引用总数
from documents d
inner join xrefs x on x.xrefdocument = d.documentid and x.xrefrevnr = d.latestrevisionno
inner join documents par on par.documentid = x.documentid and par.latestrevisionno = x.revnr
inner join documentsinprojects dp1 on dp1.documentid = par.documentid
inner join documentsinprojects dp2 on dp2.documentid = d.documentid
inner join projects p2 on p2.projectid = dp2.projectid
where p2.projectid = '你的目标项目ID' -- 可指定具体项目ID
group by 
    p2.path, 
    d.filename, 
    LEFT(d.filename,CHARINDEX('.',d.filename)-1),
    RIGHT(d.filename,CHARINDEX('.',REVERSE(d.filename))-1)

内容的提问来源于stack exchange，提问作者Edogm32