According to the last time match-CodePudding

Could you tell me how to do this kind of query was everybody

 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
The SET time_zone="+ 00:00"; 
The CREATE TABLE IF NOT EXISTS ` card ` (
` id ` int (11), NOT NULL AUTO_INCREMENT, 
` card ` varchar (30) NOT NULL, 
The PRIMARY KEY (` id `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5; 
INSERT INTO ` card ` (` id `, ` card `) VALUES 
(1, '8986031774769075002'), 
(2, '8986031774769075004'), 
(3, '8986031774769075005'), 
(4, '8986031774769075006'); 
The CREATE TABLE IF NOT EXISTS ` device ` (
` id ` int (11), NOT NULL AUTO_INCREMENT, 
` ap ` varchar (30) NOT NULL, 
` card ` varchar (30) NOT NULL, 
The PRIMARY KEY (` id `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='equipment table AUTO_INCREMENT=11; 
INSERT INTO ` device ` (` id ` ` ap `, ` card `) VALUES 
(1, 'CC856C0440B1', '8986031774769075002'), 
(2, 'CC856C0440B2', '8986031774769075002'), 
(3, 'CC856C0440B3', '8986031774769075004'), 
(4, 'CC856C0440B4', '8986031774769075005'), 
(5, 'CC856C0440B5', '8986031774769075006'); 
The CREATE TABLE IF NOT EXISTS ` device_now ` (
` ap ` varchar (30) NOT NULL, 
` lasttime ` datetime NOT NULL, 
The UNIQUE KEY ` device ` (` ap `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='equipment record; 
INSERT INTO ` device_now ` (` ap `, ` lasttime `) VALUES 
(' CC856C0440B1 ', '2017-08-05 00:00:00), 
(' CC856C0440B2 ', '2017-08-05 05:12:00), 
(' CC856C0440B3 ', '2017-08-18 00:00:00), 
(' CC856C0440B4 ', '2017-08-25 06:17:00), 
(' CC856C0440B5 ', '2017-08-26 12:40:00'); 
The CREATE TABLE IF NOT EXISTS ` device_order ` (
` oid ` int (11), NOT NULL AUTO_INCREMENT, 
` card ` varchar (30) NOT NULL, 
PRIMARY KEY (` oid `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5; 
INSERT INTO ` device_order ` (` oid `, ` card `) VALUES 
(1, '8986031774769075002'), 
(2, '8986031774769075004'), 
(3, '8986031774769075005'), 
(4, '8986031774769075006'); 
The CREATE TABLE IF NOT EXISTS ` user_bind ` (
` uid ` int (11) NOT NULL, 
` ap_id ` int (11) NOT NULL, 
` phone ` varchar (11) NOT NULL, 
The KEY ` uid ` (uid ` `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
INSERT INTO ` user_bind ` (uid ` `, ` ap_id `, ` phone `) VALUES 
(1, 1, '15625661111'), 
(2, 2, '15625661112'), 
(3, 3 ', 15625661113 '), 
(4, 4, '15625661114'), 
(5, 5, '15625661115');

 
SELECT * FROM the test. The device_order AS o 
LEFT the JOIN (SELECT u.p hone, Arthur c. ard, ap. Ap FROM the test. The card AS c 
LEFT the JOIN the test. The device AS the ap ON ap. Card=Arthur c. ard 
LEFT the JOIN test. Device_now AS now ON ap. Ap=now. Ap 
LEFT the JOIN test. User_bind AS u ON u.a p_id=ap. Id 
GROUP BY ap. Card ORDER BY now. The lasttime DESC) AS t 
ON o. chua ard=tc ard

Find out as follows:

But what I want is:

CodePudding user response:

The group by as much as possible in as little as possible in the table

CodePudding user response:

Refers to a variety of methods in next post

http://blog.csdn.net/acmain_chm/article/details/4126306
[for] take N largest record grouping method of solicitation, and scattered points...