当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