請問各位這種查詢得怎么做好
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`card` varchar(30) NOT NULL,
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');
CREATE TABLE IF NOT EXISTS `device` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ap` varchar(30) NOT NULL,
`card` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='設備表' 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');
CREATE TABLE IF NOT EXISTS `device_now` (
`ap` varchar(30) NOT NULL,
`lasttime` datetime NOT NULL,
UNIQUE KEY `device` (`ap`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='設備記錄表';
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');
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');
CREATE TABLE IF NOT EXISTS `user_bind` (
`uid` int(11) NOT NULL,
`ap_id` int(11) NOT NULL,
`phone` varchar(11) NOT NULL,
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 test.device_order AS o
LEFT JOIN (SELECT u.phone,c.card,ap.ap FROM test.card AS c
LEFT JOIN test.device AS ap ON ap.card = c.card
LEFT JOIN test.device_now AS now ON ap.ap = now.ap
LEFT JOIN test.user_bind AS u ON u.ap_id = ap.id
GROUP BY ap.card ORDER BY now.lasttime DESC ) AS t
ON o.card = t.card
查出如下：
但我要的是：
uj5u.com熱心網友回復：
盡量把 group by 在盡量少的表中uj5u.com熱心網友回復：
參考下貼中的多種方法http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分組取最大N條記錄方法征集，及散分....
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116682.html
標籤：MySQL