当Mac和ScanGroup列在SQL中匹配ALPHA、BRAVO、CHARLIE时，如何返回距离列？

Question

我正在尝试三边装置的位置，并需要隔离这三个测量。B8:27:EB:B6:96:76这三个人都在场，我想在此与之相匹配。

​

Answer 1

由于MariaDB没有枢轴特性，所以您只需执行子查询即可。

SELECT DISTINCT 
  a.Mac,
  a.ScanGroup,
  a.FirstTimeSeen,
  a.distance as alpha_distance,
  b.bravo_distance,
  c.charlie_distance
FROM ScanResults a 
JOIN ( /* hi, I'm pretending to be a new table */
      select Mac, ScanGroup, distance as bravo_distance 
      FROM ScanResults 
      WHERE DeviceName = 'BRAVO') b on a.Mac = b.Mac and a.ScanGroup = b.ScanGroup
JOIN (
      select Mac, ScanGroup, distance as charlie_distance 
      FROM ScanResults 
      WHERE DeviceName = 'CHARLIE') c on a.Mac = c.Mac and a.ScanGroup = c.ScanGroup
WHERE a.DeviceName = 'ALPHA'
  and a.Mac = 'B8:27:EB:B6:96:76';