group by分组后获得每组中符合条件的那条记录

迷南。 2021-12-18 16:19 531阅读 0赞

当group by单独使用时，只显示出每组的第一条记录。
    如下，未分组时查询出两条记录

SELECT
      info.id,
      info.switch_id,
      info.port_id,
      info.mac_addr,
      info.ip,
      info.rec_time,
      info.dev_name,
      info.is_active,
      info.port_type,
      info.real_type,
      cfg.port_describe,
      cfg.port_value,
      switchers.name,
      switchers.switch_ip
    FROM
      (
        T_NET_SWITCHBOARD_PORT_INFO AS info
        LEFT JOIN T_NET_SWITCHBOARD_PORT_CFG AS cfg
          ON info.port_id = cfg.port_id
      )
      LEFT JOIN T_NET_SWITCHBOARD_CFG AS switchers
        ON info.switch_id = switchers.switch_id
    WHERE info.mac_addr != ''
      AND info.`real_type` = 0
      AND info.port_type != 1
      AND info.dev_name LIKE "%"  "%"
      AND info.ip LIKE "%" "%"
      AND info.mac_addr LIKE "%"  "%"
      AND info.mac_addr LIKE '%00:10:7F:52:0D:FD%'

结果

id       switch\_id  port\_id  mac\_addr               ip                         rec\_time                 dev\_name  is\_active  port\_type  real\_type  port\_describe          port\_value  name                           switch\_ip        
\------  ---------  -------  -----------------  --------------  -------------------  --------  ---------  ---------  ---------  ---------------------  ----------  -----------------------------   
 59591         18     3697  00:10:7F:52:0D:FD   168.160.15.197   2018-08-08 15:40:32                    0          2          0  GigabitEthernet5/0/2           33  地下UPS室-华为S9306                 168.160.15.66    
 59798         20     3758  00:10:7F:52:0D:FD                            2018-08-08 15:47:16                    0          0          0  GigabitEthernet0/0/14          19  地下总控室-华为S5700-1                168.160.15.76

这个时候加上group by 默认加载显示第一条，如果我想要port\_type最小的那条该如何实现？

**先对分组前进行排序，然后再分组获取。**

SELECT a.* FROM (SELECT
      info.id,
      info.switch_id,
      info.port_id,
      info.mac_addr,
      info.ip,
      info.rec_time,
      info.dev_name,
      info.is_active,
      info.port_type,
      info.real_type,
      cfg.port_describe,
      cfg.port_value,
      switchers.name,
      switchers.switch_ip
    FROM
        T_NET_SWITCHBOARD_PORT_INFO AS info
      LEFT JOIN T_NET_SWITCHBOARD_PORT_CFG AS cfg
          ON info.port_id = cfg.port_id 
      LEFT JOIN T_NET_SWITCHBOARD_CFG AS switchers
          ON info.switch_id = switchers.switch_id
    WHERE (info.mac_addr != ''
      AND info.`real_type` = 0
      AND info.port_type != 1
      AND info.mac_addr LIKE '%00:10:7F:52:0D:FD%')
      ORDER BY info.port_type -- 先排序再分组
      ) a
    GROUP BY  a.mac_addr 
    ORDER BY 
    a.switch_ip,
    a.port_describe

转载于:https://www.cnblogs.com/thiaoqueen/p/9553464.html