联接两个查询表 [英] Join two queried tables

问题描述

我如何加入这两个表?我想它会是左连接还是右连接，但我无法确定语法.

How do I join these two tables? Im guessing it will either a left or right join but I can't determine the syntax.

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

Two queries from the same table generated two tables:

表1.在ReservationMAC和ReservationIP列中获得所有具有相同条目的对象:

Table 1. Get all those with the same entry in ReservationMAC and ReservationIP column:

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

结果:

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列

Table2. Get all those with the same entry only in ReservationMAC column

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列不匹配.我不在乎其他专栏，但结果中需要它. 结果:

I need to rejoin these two and the end result should be a table whose ReservationMAC column match but the ReservationIP column doesnot match. I don't care about the other columns but I need it in the result. 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

推荐答案

您只需要将查询用括号括起来，并为它们提供别名即可加入.

You just need to wrap your queries in parenthesis, and give them an alias to join on.

  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

或者，您可以在两个查询中创建视图，以使查询更易于管理.

As an alternative, you could create views out of your two queries in order to make the query a little bit more manageable.

这篇关于联接两个查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！