数据与标准值比较

表准值表，里面放的是对应属性的标准值：SensorName                                         Distance
-------------------------------------------------- -----------
M10                                                55
Thick. Sensor                                      25
FLPR                                               30
NSCL                                               25
SIL                                                26
数据报告值表，里面放的是定期报告的数据值：ReportId                             Name                                               Distance
------------------------------------ -------------------------------------------------- -----------
4E9D003B-A2DE-4528-A861-EF0F108DE284 M10                                                490
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 Thick. Sensor                                      922
4E9D003B-A2DE-4528-A861-EF0F108DE284 FLPR                                               1356
4E9D003B-A2DE-4528-A861-EF0F108DE284 Thick. Sensor                                      922
E3B25C38-24C8-40FF-B3F4-0910F6C942AF NSCL                                               88
96C8F9CA-75B3-4BC8-AAB8-127026249168 Thick. Sensor                                      922
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 NSCR                                               1506
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 Thick. Sensor                                      922
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 M10                                                490
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 NSCR                                               1506
E3B25C38-24C8-40FF-B3F4-0910F6C942AF Thick. Sensor                                      922
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 FLPL                                               330
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 SIL                                                410
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 FLPR                                               1356
96C8F9CA-75B3-4BC8-AAB8-127026249168 FLPL                                               330
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 NSCL                                               88
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 SIL                                                410
E3B25C38-24C8-40FF-B3F4-0910F6C942AF NSCR                                               1506
E3B25C38-24C8-40FF-B3F4-0910F6C942AF FLPR                                               1356
96C8F9CA-75B3-4BC8-AAB8-127026249168 NSCL                                               88
1CDDEDF0-70AB-4635-B863-6CD1EB5871B8 NSCL                                               88
96C8F9CA-75B3-4BC8-AAB8-127026249168 NSCR                                               1508
96C8F9CA-75B3-4BC8-AAB8-127026249168 M10                                                490
E3B25C38-24C8-40FF-B3F4-0910F6C942AF M10                                                490
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 M10                                                491
4E9D003B-A2DE-4528-A861-EF0F108DE284 NSCL                                               88
E3B25C38-24C8-40FF-B3F4-0910F6C942AF FLPL                                               330
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 FLPR                                               1360
96C8F9CA-75B3-4BC8-AAB8-127026249168 FLPR                                               1356
96C8F9CA-75B3-4BC8-AAB8-127026249168 SIL                                                410
E3B25C38-24C8-40FF-B3F4-0910F6C942AF SIL                                                410
4E9D003B-A2DE-4528-A861-EF0F108DE284 NSCR                                               1506
4E9D003B-A2DE-4528-A861-EF0F108DE284 SIL                                                410
9C179E0B-0B8E-4692-8A4F-F80DDD44E3A5 FLPL                                               330
4E9D003B-A2DE-4528-A861-EF0F108DE284 FLPL                                               330

解决方案 »

  1.   

    如何做到数据表里面的值有一项不符合标准值就把该ReportId取出来
      

  2.   

    select ReportId 数据报告值表
    where Name not in(select SensorName from 表准值表)
      

  3.   

    谢楼上，要比较对应Distance值的
      

  4.   

    --1.
    select distinct b.reportid
    from 标准表 a join 报告表 b
    on a.SensorName=b.Name and a.Distance<>b.Distance--2.
    select distinct reportid
    from 报告表 a
    where exists(select 1 from 标准表 where SensorName=a.Name and Distance<>a.Distance)
      

  5.   

    select *
    from t1 s ,t2 t
    where s.SensorName =t.Name and a.Distance<>b.Distance
      

  6.   


    select ReportId
    from t1 s ,t2 t
    where s.SensorName =t.Name and s.Distance<>t.Distance
      

  7.   

    select distinct reportid
    from 报告表 A
    where name in (select SensorName from 标准表 where Distance<>A.Distance)