SQL Server查询多对多关系

本文介绍了SQL Server查询多对多关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的SQL Server中具有以下多对多关系(请参见下图).

I have the following Many to many relationship (See the picture below) in my SQL server.

在大多数情况下，表tblWavelengths中有2行与表tblSensors相关(在某些情况下只有1行，在极端情况下可能有20行)

In most cases there's are 2 rows in table tblWavelengths related to the table tblSensors, (in some cases only 1, and in extreme cases there can be 20 rows)

我进行了以下简单查询，以从这3个表中检索数据:

I made the following simple query to retrieve the data from those 3 tables :

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

运行此查询后，我得到以下结果:

After running this query I got the following results :

这是我的问题.我想编写一个查询，该查询仅过滤那些在给定时间点(DateTimeID)在tblWavelengths表中具有两行(两种不同波长)的传感器(SensorName).因此，例如，我希望得到的结果没有 77902/001传感器-因为在给定的时间点只有一排(一个波长)与tblSensors相关

Here comes my problem. I want to write a query which filters only those Sensors (SensorName) which at a given moment in time (DateTimeID) has two rows (two different wavelengths) in the tblWavelengths table. So for example I want to have the results without the 77902/001 Sensor - because it has only one row (one Wavelength) related to the tblSensors at a given moment in time

推荐答案

您可以使用窗口化函数找出每种传感器名称/日期时间组合的波长数:

You could use a windowed function to find out the number of wavelengths for each sensorname/datetimeid combination:

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的使用，因此下面提供了一种替代方法

As an after thought I realised that you might have two results for one sensor at the same time with the same wavelength, which would return 2 records, but not have two different wavelengths. Since windowed functions don't support the use of DISTINCT an alternative is below

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;