过滤Autodesk库的垂直数据，获取每次绘图的最新记录
使用版(64位) 10.0.550.0
我正试图从Autodesk Vault服务器中提取数据。获取所需数据所涉及的SQL太高级，对我目前的知识水平来说太高了，所以我正在尝试使用来自Google和StackOverflow的部分来构建一个谜题。使用this excellent answer，我能够将垂直数据转换成可管理的水平格式。
Autodesk Vault数据库存储有关CAD绘图的信息(除其他外)。主垂直表格dbo.Property保存关于每个CAD绘图的所有不同修订的信息。我目前面临的问题是我得到的数据太多了。我只想从每一个CAD图纸的最新修订的数据。
到目前为止，这是我的SQL
select
CreateDate,
EntityID,
PartNumber,
CategoryName,
[Subject],
Title
from
(
select
EntityID,
CreateDate,
[53] as PartNumber,
[28] as CategoryName,
[42] as [Subject],
[43] as Title
from
(
select
p.Value,
p.PropertyDefID,
p.EntityID,
e.CreateDate
from dbo.Property as p
inner join dbo.Entity as e on p.EntityID = e.EntityId
where p.PropertyDefID in(28, 42, 43, 53)
and e.EntityClassID = 8
) t1
pivot
(
max(Value)
for PropertyDefID in([28], [42], [43], [53])
) t2
) t3
where PartNumber is not null
and PartNumber != ''
and CategoryName = 'Drawing'
-- (1) additional condition
order by PartNumber, CreateDate desc其中dbo.Property.Value是sql_variant数据类型。上面的查询产生了一个类似于以下内容的数据集：
CreateDate | EntityID | PartNumber | CategoryName | Subject | Title
---------------------------------------------------------------------
2016-01-01 | 59046 | 10001 | Drawing | Xxxxx | Yyyyy
2016-05-01 | 60137 | 10001 | Drawing | Xxxxx | Yyyyy
2016-08-01 | 62518 | 10001 | Drawing | Xxxx | Yyyyyy
2016-12-16 | 63007 | 10001 | Drawing | Xxxxxx | Yyyyyy
2016-01-01 | 45776 | 10002 | Drawing | Zzzzz | NULL
2016-11-01 | 65011 | 10002 | Drawing | Zzzzzz | NULL
...
(about 23000 rows)我的问题是，我得到了每一张图纸的所有修改。在上面的例子中，我只想知道PartNumber=10001的最新修订版，日期是'2016-12-16‘等等。
我还研究过this answer如何对其中一个列具有最大值的行进行分组和选择，但我似乎不知道如何将两者组合起来。我尝试向上面查询中的注释行添加以下代码段，但在许多不同级别上都失败了。
and (PartNumber, CreateDate) in
(
select PartNumber, max(CreateDate)
from t3
group by PartNumber
)我之所以把这个问题标记为“枢轴”，尽管旋转已经完成了，因为我怀疑旋转是给我带来麻烦的原因。我只是还没能把我的注意力集中在这些旋转的东西上，而且我的SQL优化技巧也严重缺乏。也许过滤应该在一个内部层面进行？
回答 1
Stack Overflow用户
发布于 2016-12-20 16:44:06
从@Strawberry提供的评论中得到灵感，我一直在工作和调整，直到找到了一些似乎可行的东西。我必须在PIVOT中使用PIVOT才能使其全部工作。
编辑：一开始我使用视图，但是当我不得不使用只读数据库用户时，先决条件发生了变化。幸运的是，我仍然可以创建临时表。
这是最后的结果。
if object_id('tempdb.dbo.#Properties', 'U') is not null
drop table #Properties
create table #Properties
(
PartNumber nvarchar(max),
[Subject] nvarchar(max),
Title nvarchar(max),
CreateDate datetime
)
insert into #Properties
(
PartNumber,
[Subject],
Title,
CreateDate
)
select
convert(nvarchar(max), PartNumber),
convert(nvarchar(max), [Subject]),
convert(nvarchar(max), Title),
convert(datetime, CreateDate)
from
(
select
EntityID,
CreateDate,
[53] as PartNumber,
[42] as [Subject],
[43] as Title
from
(
select
p.Value,
p.PropertyDefID,
p.EntityID,
e.CreateDate
from dbo.Property as p
inner join dbo.Entity as e on p.EntityID = e.EntityId
where p.PropertyDefID in (42, 43, 53)
and e.EntityClassID = 8
and p.EntityID in
(
select
max(EntityID) as MaxEntityID
from
(
select
EntityID,
[28] as CategoryName,
[53] as PartNumber
from
(
select
p.Value,
p.EntityID,
p.PropertyDefID
from dbo.Property as p
inner join dbo.Entity as e on p.EntityID = e.EntityId
where p.PropertyDefID in (28, 53)
and e.EntityClassID = 8 -- FileIteration
) as t1
pivot
(
max(Value)
for PropertyDefID in ([28], [53])
) as t2
) as t3
where CategoryName = 'Drawing'
group by PartNumber
)
) as t4
pivot
(
max(Value)
for PropertyDefID in ([42], [43], [53])
) as t5
) as t6
where PartNumber is not null
and PartNumber != ''
order by PartNumber
select * from #Properties;
-- search conditions goes here我不得不将建议的join更改为where x in(y)，因为连接非常慢(4分钟后我终止了查询)。现在，生成的数据集(大约需要2秒来生成)看起来很有希望：
PartNumber | Subject | Title | CreateDate | ...
-----------------------------------------------------------------------
100000 | Xxxxxx | Yyyyyy | 2015-08-17 09-10 | ...
100001 | Zzzzzz | NULL | 2015-09-02 15-23 | ...
...
(about 8900 rows)在片场里没有旧版本了。
https://stackoverflow.com/questions/41187318
复制相似问题
腾讯云开发者
