在SOLIDWORKS PDM系统使用过程中，我们因为使用的具体情况，需要了解一下某个变量中是否有重复值的情况，比如一开始某个变量没有想把它设置成维一值，但后来由于使用需求的变更又想要把它设置成维一值变量了，可是这个时候系统中关于这个变量已经有了相同变量值的存在，因此系统不允许进行维一值设置。那这个时候我们就会需要查找系统中拥有相同变量值的文件功能了。要实现这样的查找，我们可以通过SQL语句的方式来做。

具体SQL语句如下所示：

可以显示出Path、ID、Variable、Value、Version、Configuration、Type、Parent Filepath、DocTypeID，已查找变量名称为“number”为例：

Declare @VarID Int

set @VarID = (Select VariableID from Variable where VariableName = 'number')

       

  select need.*,ddddd.DocTypeID from documents ddddd

join

  (Select

  Case when o.ObjectTypeID != 1 then d.Filename else

    (SELECT top 1  p.path + d.Filename

        from Projects p, DocumentsInProjects dip

        where p.ProjectID = dip.ProjectID

        and dip.DocumentID = d.DocumentID

    )  end as [Path],

    vv.DocumentID as [ID],    

   (Select VariableName from Variable where VariableID = @VarID) as [Variable],

    Cast(vv.ValueText As nvarchar(max)) as [Value],

    vv.RevisionNo as [Version],

   (select ConfigurationName from DocumentConfiguration where ConfigurationID = vv.ConfigurationID) as [Configuration],

   o.Description as [Type],

   Case when o.ObjectTypeID = 1 then '-' else

       (SELECT top 1  p.path + d2.Filename

        from XRefs x

        Join Documents d2 on d2.DocumentID = x.DocumentID

        Join DocumentsInProjects dip On dip.DocumentID = d2.DocumentID

        Join Projects p On p.ProjectID = dip.ProjectID

        where XRefDocument = vv.DocumentID

    ) end as [Parent Filepath]

  From VariableValue vv

  Join Documents d On vv.DocumentID = d.DocumentID

  Join ObjectType o On d.ObjectTypeID = o.ObjectTypeID

  Where vv.VariableID = @VarID And

        vv.ProjectID = 2 And

        vv.RevisionNo = (Select Max(RevisionNo)

                         From VariableValue

                         Where VariableID = vv.VariableID And

                               DocumentID = vv.DocumentID And

                               ProjectID = 2 And

                               ConfigurationID = vv.ConfigurationID) And

        vv.ValueCache <> '' And

        d.Deleted = 0 And

        Exists (Select 1

                From VariableValue vv3

                Join Documents d2 On d2.DocumentID = vv3.DocumentID

                Where d2.Deleted = 0 And

                      vv3.DocumentID <> vv.DocumentID And

                      vv3.VariableID = vv.VariableID And

                      vv3.ProjectID = 2 And

                      vv3.RevisionNo = (Select Max(RevisionNo)

                                        From VariableValue

                                        Where DocumentID = vv3.DocumentID And

                                              ProjectID = 2 And

                                              ConfigurationID = vv3.ConfigurationID And

                                              VariableID = vv3.VariableID) And

                      vv3.ValueCache <> '' And

                      vv3.ValueCache = vv.ValueCache) 

UNION        

Select DISTINCT

P1.Path as [Path],

P1.ProjectID as [ID],

(Select VariableName from Variable where VariableID = @VarID) as [Variable],

Cast(Val1.ValueText As nvarchar(max)) as [Value],

'-' as [Version],

'-' as [Configuration],

'Folder' as [Type],

'-'

      From

        VariableValue Val1,

        VariableValue Val2,

        Projects P1,

        Projects P2

      Where

        Val1.VariableID = @VarID And

        Val2.VariableID = @VarID And

        Val1.ProjectID  = P1.ProjectID And

        Val2.ProjectID  = P2.ProjectID And

        Val1.DocumentID = 1 And

        Val1.RevisionNo = 1 And

        Val1.ConfigurationID = 1 And

        Val2.DocumentID = 1 And

        Val2.RevisionNo = 1 And

        Val2.ConfigurationID = 1 And

        Val1.ProjectID != Val2.ProjectID And

        cast(cast(Val1.ValueText as nvarchar) as varbinary) != 0 And

        Cast( Val1.ValueText As nvarchar(255) ) = Cast( Val2.ValueText As nvarchar(255) )           

 

   ) need

   on ddddd.DocumentID = need.id