SQL Server：如何使用distinct将这两个查询合并在一起

I have a query that gets a list of distinct deviceID's, now I want to join that query with another query that has an inner join.

我有一个获取不同deviceID列表的查询，现在我想将该查询与另一个具有内部联接的查询联系起来。

This is my first query

这是我的第一个查询

Select distinct deviceid 
from session 
where cast(createdon as date) between '01/01/2018' and '01/30/2018' 
  and len(deviceid) > 10

That query returns the distinct DeviceId's I need. I now want to use that DeviceID from that query and replace it with the DeviceID in this query

该查询返回我需要的不同DeviceId。我现在想要使用该查询中的DeviceID并将其替换为此查询中的DeviceID

Second query:

第二个查询：

select f.PracticeID, F.Name, D.DeviceID, d.SerialNumber 
from device d
inner join facility f on f.id = d.locationid
inner join session s on s.deviceid = d.deviceid 
where d.deviceID = 'deviceID'

How can I merge those 2? I want to get the results from 1st query and use it for the 2nd query DeviceID column. Right now I am manually executing the DeviceID in the second query that I get from the first. I am using SQL Server 2012

我如何合并那些2？我想从第一个查询中获取结果并将其用于第二个查询DeviceID列。现在我在第一个查询中手动执行DeviceID。我正在使用SQL Server 2012

2 个解决方案

#1

You want to get all the data from the second query for the distinct DeviceID from the first one? This should do it.

您想从第二个查询获取第一个不同DeviceID的所有数据吗？这应该做到这一点。

select f.PracticeID,F.Name,D.DeviceID,d.SerialNumber from device d
inner join facility f on f.id=d.locationid
inner join session s on s.deviceid=d.deviceid 
where d.deviceID in (
     Select distinct deviceid from session where cast(createdon as date) between 
     '01/01/2018' and '01/30/2018' and len(deviceid)>10
)

#2

You can use a sub query to achieve this :-

您可以使用子查询来实现此目的： -

select f.PracticeID,F.Name,D.DeviceID,d.SerialNumber from ( Select distinct deviceid from session where cast(createdon as date) between 
'01/01/2018' and '01/30/2018' and len(deviceid)>10) d
inner join facility f on f.id=d.locationid
inner join session s on s.deviceid=d.deviceid 
where d.deviceID='deviceID'

#1