mysql - 加入两个查询表

Question

如何连接这两个表？我猜它是左连接还是右连接，但我无法确定语法。

来自同一个表的两个查询生成了两个表:

表 1. 获取 ReservationMAC 和 ReservationIP 列中具有相同条目的所有那些:

Select a.Server, a.Network, a.ReservationIP, a.ReservationMAC, a.ReservationName, dt.cnt 
  from Dashboard.dbo.DHCP_ScopeReservations a
      INNER JOIN (Select ReservationIP, ReservationMAC, COUNT(ReservationMAC) AS cnt from Dashboard.dbo.DHCP_ScopeReservations
                  group by ReservationIP, ReservationMAC
                  having count(ReservationMAC) > 1  

                  ) dt ON a.ReservationMAC=dt.ReservationMAC AND a.ReservationIP=dt.ReservationIP
  order by ReservationMAC

Result:

Server  network ReservationIP   ReservationMAC  ReservationNAme cnt
S1  10.34.57.0  10.34.57.10     0               vhpa101         2
S2  10.34.57.0  10.34.57.10     0               vhvpa101        2
S3  10.206.0.0  10.206.3.22    0000681569af     ac-gpo069       2
S4  10.206.0.0  10.206.3.22    0000681569af     ac-gpo069       2
S5  10.232.8.0  10.232.11.51    6.82E+06        ac-gpob14       2
S6  10.232.8.0  10.232.11.51    6.82E+06        ac-gpob14       2

表 2。仅在 ReservationMAC 列中获取所有具有相同条目的那些

Select a.Server, a.Network, a.ReservationIP, a.ReservationMAC, a.ReservationName, dt.cnt 
from Dashboard.dbo.DHCP_ScopeReservations a
  INNER JOIN (Select ReservationMAC, COUNT(ReservationMAC) AS cnt from Dashboard.dbo.DHCP_ScopeReservations
              group by ReservationMAC
              having count(ReservationMAC) > 1

              ) dt ON a.ReservationMAC=dt.ReservationMAC 
              order by ReservationMAC

Result: 
Server  network ReservationIP   ReservationMAC  ReservationNAme cnt
S1  10.16.175.0 10.16.175.203   0               ups-mume-042a       2
S2  10.64.160.0 10.64.177.61    0               pq2331              2
S3  10.34.57.0  10.34.57.10     0               vhpa101             2
S4  10.34.57.0  10.34.57.10     0               vhvpa101            2
S1  10.206.0.0  10.206.3.22     0000681569af    ac-gpo069           2
S2  10.206.0.0  10.206.3.22     0000681569af    ac-gpo069           2
S3  10.232.8.0  10.232.11.51    6.82E+06        ac-gpob14       2
S4  10.232.8.0  10.232.11.51    6.82E+06        ac-gpob14       2

我需要重新加入这两个，最终结果应该是一个表ReservationMAC 列匹配但 ReservationIP 列不匹配。我不关心其他列，但结果中需要它。结果:

Server  network ReservationIP   ReservationMAC  ReservationNAme cnt
S1  10.16.175.0 10.16.175.203   0               ups-mume-042a   2
S2  10.64.160.0 10.64.177.61    0               pq2331          2

Answer 1

您只需将您的查询括在括号中，并给它们一个别名即可加入。

  select q1.server, q1.network, q1.reservationip, q1.reservationmac, q1.reservationname, q1.cnt
    from
      (your first query) q1
      INNER JOIN
      (your second query) q2
      ON q1.reservationmac = q2.reservationmac and q1.reservationip <> q2.reservationip

作为替代方案，您可以从两个查询中创建 View ，以使查询更易于管理。