MySQL takes out a record of the maximum value of a field in a table, and the SQL statement

One for the trading table, with a unique ID, alias trade_id

One is the transaction Status detail table, which records multiple states of the trade_id.

Two table queries were made. Inquire about the trading situation and the latest status of a trade_id.

Paste this SQL directly and execute the known.

-- ----------------------------
--Table structure for ' trade_test '
-- ----------------------------
DROP TABLE IF EXISTS ' trade_test ';
CREATE TABLE ' Trade_test ' (
' id ' int (one) not NULL auto_increment,
' user_id ' int (one) not NULL COMMENT ' user ID ',
' Create_at ' int (one) not NULL COMMENT ' creation time ',
' Totalmoney ' decimal (10,2) not NULL COMMENT ' total amount ',
' Tid ' int (one) not NULL COMMENT ' transaction number ',
PRIMARY KEY (' id ')
Engine=innodb auto_increment=4 DEFAULT Charset=utf8 comment= ' transaction initiation form ';

-- ----------------------------
--Records of Trade_test
-- ----------------------------
INSERT into ' trade_test ' VALUES (' 1 ', ' 1 ', ' 1451700000 ', ' 60.00 ', ' 1111 ');
INSERT into ' trade_test ' VALUES (' 3 ', ' 2 ', ' 1451800000 ', ' 50.00 ', ' 2222 ');


DROP TABLE IF EXISTS ' status_detail_test ';
CREATE TABLE ' Status_detail_test ' (
' id ' int (one) not NULL auto_increment,
' trade_id ' int (one) is not NULL,
' status_name ' varchar DEFAULT NULL,
' Brief ' text COMMENT ' state description ',
' Create_at ' int (one) DEFAULT NULL COMMENT ' creation time ',
PRIMARY KEY (' id ')
Engine=innodb auto_increment=12 DEFAULT Charset=utf8 comment= ' Trading status Details table ';

-- ----------------------------
--Records of Status_detail
-- ----------------------------
INSERT into ' status_detail_test ' VALUES (' 1 ', ' 1 ', ' START ', null, ' 1453595414 ');
INSERT into ' status_detail_test ' VALUES (' 2 ', ' 1 ', ' AGREE ', null, ' 1453595753 ');
INSERT into ' status_detail_test ' VALUES (' 3 ', ' 2 ', ' START ', null, ' 1453898857 ');
INSERT into ' status_detail_test ' VALUES (' 4 ', ' 2 ', ' AGREE ', null, ' 1453941720 ');
INSERT into ' status_detail_test ' VALUES (' 5 ', ' 2 ', ' pay ', null, ' 1453941924 ');
INSERT into ' status_detail_test ' VALUES (' 6 ', ' 1 ', ' pay ', null, ' 1453942924 ');
INSERT into ' status_detail_test ' VALUES (' 7 ', ' 1 ', ' TRANSFER ', null, ' 1455564464 ');
INSERT into ' status_detail_test ' VALUES (' 8 ', ' 1 ', ' complete ', NULL, ' 1455670471 ');

SELECT
a.ID as trade_id,
A.create_at as Trade_create_at,
b.ID as status_final_id,
B.status_name,
B.create_at as Status_create_at
From
Trade_test as A,
Status_detail_test as B
WHERE
b.trade_id = a.ID
and
B.create_at = (select Max (create_at) from Status_detail_test)
and
a.ID = 1

--

Refer to a simple example:

Http://zhidao.baidu.com/link?url=15DnazEMsBVsoIs_M-WiFfLjt-YI8tEtuvL2yN7aEDe3x8cr4u_OgpgyUy1vJ4Pas3zzhZpQsY1fGs-KdFnI-q

Example: Table field
Id,name,age,

Requirement: Take out one of the oldest records of all ages

Select top 1 id,name,age from table order BY age DESC

Sort by age in reverse order, then take the first one.

Consider that there may be many people of the same age, if you need to take out, you can write:
Select Id,name,age from table where age= (select Max (age) from table)

MySQL takes out a record of the maximum value of a field in a table, and the SQL statement