请问各位这种查询得怎么做好
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查出如下:但我要的是:
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查出如下:但我要的是:
解决方案 »
- mysql日期格式转化。
- 求按月分组统计数据的sql语句。在线等
- mysql的一个库,既有myisam存储引擎表,又有innodb存储引擎表,如何做备份恢复方案!
- MySQL 中文处理问题?
- mysql 如何存储过程返回记录的更新条数
- mysql中select count(id)效率是不是比select count(*)高啊?
- Linux(ubuntu)如何在mysql5.1里设置Lock wait timeout默认时间
- 100分求教一个简单问题,如果启动linux后自动加载postgresql7.4.2?
- 急!mysql dump出来的文件恢复不回去
- Navicat 乱码 读取数据全是????
- 如果在一个存储过程中,使用另一个存储过程返回的结果集
- PHP连接mysql时出现Fatal error: Call to undefined function mysql_connect()
[征集]分组取最大N条记录方法征集,及散分....