sql - SQL 加入另一个 SQL 查询
问题描述
我已经设法根据默认网关对 SCCM 和 MDT 进行了查询,该查询使我的计算机在过去 7 天内安装在特定位置。我还设法从另一个表中加入信息。
SELECT DH.[ResourceID]
,DH.[Computername]
,DH.[AssetTag]
,DH.[Serialnumber]
,DH.[Manufacturer]
,DH.[Model]
,DH.[Role]
,DH.[StartTime]
,DH.[EndTime]
,DH.[DefaultGateway]
,CIL.[Username]
FROM [MDT].[dbo].[DeploymentHistory] DH
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE [StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate() AND
(DH.[DefaultGateway] = '192.168.10.1' OR DH.[DefaultGateway] = '192.168.20.1')
ORDER BY Starttid DESC
我现在要做的是添加另一个基于 SCCM OSD 集合的列。我有一个工作代码可以给我这些集合中的所有设备
SELECT COL.Name FROM v_R_System SD
JOIN v_FullCollectionMembership FCM ON SD.ResourceID = FCM.ResourceID
JOIN v_Collection COL ON FCM.CollectionID = COL.CollectionID
WHERE (COL.CollectionID = 'XXXXXXX1' OR COL.CollectionID = 'XXXXXXX2' OR COL.CollectionID = 'XXXXXXX3')
但是,但是我尝试将其与上面的内容结合起来,它既没有给我任何东西,也没有给我太多的设备。问题是我现在不知道如何合并它并仍然保留过滤器(WHERE-section)
我试过的一个例子是:
SELECT DH.[ResourceID]
,DH.[Computername]
,DH.[HBGAssetTag]
,DH.[Serialnumber]
,DH.[Manufacturer]
,DH.[Model]
,DH.[Role]
,DH.[StartTime]
,DH.[EndTime]
,DH.[DefaultGateway]
,CIL.[Username]
,COL.[Name]
FROM [MDT].[dbo].[DeploymentHistory] DH
JOIN [v_FullCollectionMembership] FCM ON DH.[ResourceID] = FCM.[ResourceID]
JOIN [v_Collection] COL ON DH.[ResourceID] = COL.[CollectionID]
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE [StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate() AND
(DH.[DefaultGateway] = '192.168.10.1' OR DH.[DefaultGateway] = '192.168.20.1') AND
(COL.[CollectionID] = 'XXXXXXX1' OR COL.[CollectionID] = 'XXXXXXX2' OR COL.[CollectionID] = 'XXXXXXX3')
这给了我这个错误:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'XXXXXXX1' to data type int.
Completion time: 2021-06-14T15:05:41.5142960+02:00
我试图创建另一个 LEFT JOIN 并从那个添加数据,但这也不起作用。我需要像在原始查询中那样进行整理。
谁能指出我正确的方向?
第二次尝试
SELECT DH.[ResourceID]
,DH.[Computername]
,DH.[HBGAssetTag]
,DH.[Serialnumber]
,DH.[Manufacturer]
,DH.[Model]
,DH.[Role]
,DH.[StartTime]
,DH.[EndTime]
,DH.[DefaultGateway]
,CIL.[Username]
,COL.[Name]
FROM [MDT].[dbo].[DeploymentHistory] DH
JOIN [v_FullCollectionMembership] FCM ON DH.[ResourceID] = FCM.[ResourceID]
JOIN [v_Collection] COL ON FCM.[CollectionID] = COL.[CollectionID]
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE [StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate() AND
(DH.[DefaultGateway] = '192.168.10.1' OR DH.[DefaultGateway] = '192.168.20.1') AND
(COL.[CollectionID] = 'XXXXXXX1' OR COL.[CollectionID] = 'XXXXXXX2' OR COL.[CollectionID] = 'XXXXXXX3')
第三次尝试
SELECT Q1.[ResourceID]
, Q1.[Computername]
, Q1.[AssetTag]
, Q1.[Serialnumber]
, Q1.[Manufacturer]
, Q1.[Model]
, Q1.[Role]
, Q1.[StartTime]
, Q1.[EndTime]
, Q1.[DefaultGateway]
, Q1.[Username]
, Q2.[Name] AS 'CollectionName'
FROM (
SELECT DH.[ResourceID]
, DH.[Computername]
, DH.[AssetTag]
, DH.[Serialnumber]
, DH.[Manufacturer]
, DH.[Model]
, DH.[Role]
, DH.[StartTime]
, DH.[EndTime]
, DH.[DefaultGateway]
, CIL.[Username]
FROM [MDT].[dbo].[DeploymentHistory] DH
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE [StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate()
AND DH.[DefaultGateway] IN ('192.168.10.1', '192.168.20.1')
) Q1
LEFT OUTER JOIN (
SELECT SD.[ResourceID]
, COL.Name
FROM [v_R_System] SD
INNER JOIN [MDT].[dbo].[DeploymentHistory] DH ON SD.[ResourceID] = DH.[ResourceID]
INNER JOIN [v_FullCollectionMembership] FCM ON SD.[ResourceID] = FCM.ResourceID
INNER JOIN [v_Collection] COL ON FCM.[CollectionID] = COL.[CollectionID]
WHERE COL.[CollectionID] in ('XXXXXXX1', 'XXXXXXX2', 'XXXXXXX3')
) Q2
ORDER BY DH.[StartTime] DESC
在“订单”处:
错误信息:
解决方案
SELECT Q1.[ResourceID]
,Q1.[Computername]
,Q1.[AssetTag]
,Q1.[Serialnumber]
,Q1.[Manufacturer]
,Q1.[Model]
,Q1.[Role]
,Q1.[StartTime]
,Q1.[EndTime]
,Q1.[DefaultGateway]
,Q1.[Username]
,Q2.[Name] AS 'CollectionName'
FROM (
SELECT DH.[ResourceID]
,DH.[Computername]
,DH.[HBGAssetTag]
,DH.[Serialnumber]
,DH.[Manufacturer]
,DH.[Model]
,DH.[Role]
,DH.[StartTime]
,DH.[EndTime]
,DH.[DefaultGateway]
,CIL.[Username]
FROM [MDT].[dbo].[DeploymentHistory] DH
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE DH.[StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate()
AND DH.[DefaultGateway] IN ('192.168.10.1', '192.168.20.1')
) AS Q1
LEFT OUTER JOIN (
SELECT SD.[ResourceID]
,COL.Name
FROM [v_R_System] SD
INNER JOIN [v_FullCollectionMembership] FCM ON SD.[ResourceID] = FCM.[ResourceID]
INNER JOIN [v_Collection] COL ON FCM.[CollectionID] = COL.[CollectionID]
WHERE COL.[CollectionID] IN ('XXXXXXX1', 'XXXXXXX2', 'XXXXXXX3')
) AS Q2
ON Q1.[ResourceID] = Q2.[ResourceID]
ORDER BY Q1.[StartTime] DESC
解决方案
你似乎缺乏一个可以加入的领域。另外,如果集合名称存在,我怀疑您要尝试为每个资源添加集合名称。尝试将您的第二个查询更改为...
SELECT SD.ResourceID
, COL.Name
FROM v_R_System SD
INNER JOIN v_FullCollectionMembership FCM ON SD.ResourceID = FCM.ResourceID
INNER JOIN v_Collection COL ON FCM.CollectionID = COL.CollectionID
WHERE COL.CollectionID in ('XXXXXXX1', 'XXXXXXX2', 'XXXXXXX3')
从那里,您组合查询。这是一种蛮力简单的方法:
SELECT Q1.[ResourceID]
, Q1.[Computername]
, Q1.[AssetTag]
, Q1.[Serialnumber]
, Q1.[Manufacturer]
, Q1.[Model]
, Q1.[Role]
, Q1.[StartTime]
, Q1.[EndTime]
, Q1.[DefaultGateway]
, Q1.[Username]
, Q2.[Name] AS 'CollectionName'
FROM (
SELECT DH.[ResourceID]
, DH.[Computername]
, DH.[AssetTag]
, DH.[Serialnumber]
, DH.[Manufacturer]
, DH.[Model]
, DH.[Role]
, DH.[StartTime]
, DH.[EndTime]
, DH.[DefaultGateway]
, CIL.[Username]
, Startid
FROM [MDT].[dbo].[DeploymentHistory] DH
LEFT JOIN [MDT].[dbo].[ComputerImportLog] CIL ON DH.[Computername] = CIL.[Computername]
WHERE [StartTime] BETWEEN dateadd(day,-7,getdate()) and getdate()
AND DH.[DefaultGateway] IN ('192.168.10.1', '192.168.20.1')
) Q1
LEFT OUTER JOIN (
SELECT SD.ResourceID
, COL.Name
FROM v_R_System SD
INNER JOIN v_FullCollectionMembership FCM ON SD.ResourceID = FCM.ResourceID
INNER JOIN v_Collection COL ON FCM.CollectionID = COL.CollectionID
WHERE COL.CollectionID in ('XXXXXXX1', 'XXXXXXX2', 'XXXXXXX3')
) Q2
ORDER BY Starttid DESC
这可以在不使用子查询的情况下完成,但是这种记录您的思考过程。(我想看到这个结果与那个结果相结合。)所以这是一个很好的起点。一旦你看到你寻找的结果,然后简化查询。
虽然并不总是需要,但最好对列进行限定。例如,我不知道从哪里来Startid
,所以我不能 100% 确定这段代码。
推荐阅读
- c# - SystemArgumentOutOfRangeException C#
- authentication - 如何在访问 Google App 的多个服务之间共享凭据
- vba - 如何将空白转换为制表符
- sas - SAS:IF 语句中的通配符
- javascript - 单击按钮本身不会播放视频
- autodesk-forge - 无法为 Autodesk BIM 360 docs 项目创建 webhook
- angularjs - 未在 ui-view 中加载的部分 html 之一
- docker - Spring Boot Docker com.netflix.discovery.shared.transport.TransportException:无法在任何已知服务器上执行请求
- text - 与 gedit 中的文本选择进行比较
- javascript - 从javascript数组变量设置元素oninput