我在我的sql服务器中有以下多对多关系(见下图).
在大多数情况下,表tblWavelengths中有2行与表tblSensors相关,(在某些情况下只有1行,在极端情况下可能有20行)
我做了以下简单查询来从这3个表中检索数据：
select W.DateTimeID,S.SensorName,S.SensorType,W.Channel,W.PeakNr,W.Wavelength
from tblWavelengths as W
Left Join tblSensorWavelengths as SW on W.tblWavelengthID = SW.WavelengthID
Left Join tblSensors as S on SW.SensorID = S.SensorID
order by W.DateTimeID
运行此查询后,我得到以下结果：
这是我的问题.我想写一个查询,它只过滤那些传感器(SensorName),它在给定的时刻(DateTimeID)在tblWavelengths表中有两行(两个不同的波长).所以例如我想要没有结果
77902/001传感器 – 因为它在给定时刻只有一行(一个波长)与tblSensors相关
解决方法
您可以使用窗口函数来查找每个sensorname / datetimeid组合的波长数：
WITH Data AS
( SELECT W.DateTimeID,W.Wavelength,[Wcount] = COUNT(*) OVER(PARTITION BY s.SensorName,d.DateTimeID)
from tblWavelengths as W
LEFT JOIN tblSensorWavelengths as SW
ON W.tblWavelengthID = SW.WavelengthID
LEFT JOIN tblSensors as S
ON SW.SensorID = S.SensorID
)
SELECT DateTimeID,SensorName,SensorType,Channel,PeakNr,WaveLength
FROM Data
WHERE Wcount = 2
ORDER BY DateTimeID;
附录
后来我意识到你可能有两个结果,同时一个传感器具有相同的波长,这将返回2个记录,但没有两个不同的波长.由于窗口函数不支持使用DISTINCT,因此下面是另一种选择
WITH Data AS
( SELECT W.DateTimeID,W.tblWaveLengthID
from tblWavelengths as W
LEFT JOIN tblSensorWavelengths as SW
ON W.tblWavelengthID = SW.WavelengthID
LEFT JOIN tblSensors as S
ON SW.SensorID = S.SensorID
)
SELECT d.DateTimeID,d.SensorName,d.SensorType,d.Channel,d.PeakNr,d.WaveLength
FROM Data d
INNER JOIN
( SELECT DateTimeID,SensorName
FROM Data
GROUP BY DateTimeID,SensorName
HAVING COUNT(DISTINCT tblWaveLengthID) = 2
) t
ON t.DateTimeID = d.DateTimeID
AND t.SensorName = d.SensorName
ORDER BY d.DateTimeID;
