SQL Server查询多对多关系

Question

我在我的SQL服务器中有以下的多对多关系（见下图）。

在大多数情况下，tblWavelengths表与tblSensors表相关联的行数为2（在某些情况下只有1行，在极端情况下可能有20行）。

我制作了以下简单查询来检索这三个表中的数据：

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

运行此查询后，我获得了以下结果：

这里出现了我的问题。我想编写一个查询，仅过滤那些在tblWavelengths表中在给定时间（DateTimeID）有两行（两个不同波长）的传感器（SensorName）。例如，我想要没有77902/001传感器的结果 - 因为它在给定时间只有一行（一个波长）与tblSensors相关联。

- patex1987

Answer 1

您可以使用窗口函数来查找每个sensorname/datetimeid组合的波长数量：

WITH Data AS
(   SELECT  W.DateTimeID,
            S.SensorName,
            S.SensorType,
            W.Channel,
            W.PeakNr,
            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,
            S.SensorName,
            S.SensorType,
            W.Channel,
            W.PeakNr,
            W.Wavelength,
            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;

- GarethD

---

谢谢，这正是我所需要的。我需要在SQL服务器内深入挖掘一下。 - patex1987

在同一时间在同一频道具有相同的波长是不可能的，但无论如何感谢您的提示。这将在未来很有用。 - patex1987

还有一件事我需要知道。如果我想要对波长值进行数据透视，但是这是动态透视，有时只有一个波长，有时候有两个。你能帮我吗？ - patex1987

你能否发布一个新问题，附上你期望的输出结果，我会看一下。谢谢。 - GarethD

问题 - 这是我的新问题 - patex1987