SQL语句
主表
CREATE TABLE ma_member
(
id
int(11) unsigned NOT NULL AUTO_INCREMENT,
name
varchar(255) CHARACTER SET utf8 DEFAULT NULL,
sex
tinyint(2) DEFAULT NULL,
status
tinyint(4) DEFAULT NULL,
create_time
timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id
)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
从表
CREATE TABLE ma_warning_log
(
warning_id
int(11) unsigned NOT NULL AUTO_INCREMENT,
mid
int(11) unsigned DEFAULT NULL,
cpu
int(10) DEFAULT NULL,
memory
int(10) DEFAULT NULL,
warning_status
tinyint(2) DEFAULT ‘1’ COMMENT ‘告警状态 1 正常 2告警’,
create_time
timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_deleted
tinyint(4) DEFAULT ‘0’,
PRIMARY KEY (warning_id
)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
$sql = "SELECT
cr.id, cr.name, oi.cpu, oi.memory
FROM
ma_member AS cr
LEFT JOIN (
SELECT a.mid, a.cpu, a.memory, a.warning_status, a.create_time FROM ma_warning_log AS a where a.warning_status = 1 and warning_id = (select max(warning_id) from ma_warning_log as b where a.mid = b.mid)
) AS oi ON oi.mid = cr.id order by cpu desc";
解决的问题:数据一对多,获取多条数据最新的那条数据。