問題：
要求查詢結果如下圖所示：
-- mysql 表結構 腳本
CREATE TABLE `clt_forex` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol` varchar(255) DEFAULT NULL,
`bid` varchar(255) DEFAULT NULL,
`ask` varchar(255) DEFAULT NULL,
`price` varchar(255) DEFAULT NULL,
`timestamp` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6861 DEFAULT CHARSET=utf8;
-- -----mysql原始資料-----------------
INSERT INTO `clt_forex` VALUES ('66', 'GBPUSD', '1.40533', '1.40537', '1.40535', '1524212525');
INSERT INTO `clt_forex` VALUES ('67', 'EURUSD', '1.23305', '1.23306', '1.23306', '1524212525');
INSERT INTO `clt_forex` VALUES ('68', 'USDJPY', '107.55', '107.55', '107.55', '1524212525');
INSERT INTO `clt_forex` VALUES ('69', 'USDCAD', '1.26731', '1.26734', '1.26733', '1524212525');
INSERT INTO `clt_forex` VALUES ('70', 'AUDUSD', '0.77069', '0.77069', '0.77069', '1524212525');
INSERT INTO `clt_forex` VALUES ('71', 'GBPUSD', '1.40536', '1.40539', '1.40537', '1524212526');
INSERT INTO `clt_forex` VALUES ('72', 'EURUSD', '1.23308', '1.23309', '1.23308', '1524212526');
INSERT INTO `clt_forex` VALUES ('73', 'USDJPY', '107.549', '107.551', '107.55', '1524212526');
INSERT INTO `clt_forex` VALUES ('74', 'USDCAD', '1.26731', '1.26734', '1.26733', '1524212526');
INSERT INTO `clt_forex` VALUES ('75', 'AUDUSD', '0.7707', '0.7707', '0.7707', '1524212526');
INSERT INTO `clt_forex` VALUES ('76', 'GBPUSD', '1.40536', '1.40539', '1.40537', '1524212527');
INSERT INTO `clt_forex` VALUES ('77', 'EURUSD', '1.23308', '1.23309', '1.23308', '1524212527');
INSERT INTO `clt_forex` VALUES ('78', 'USDJPY', '107.55', '107.55', '107.55', '1524212527');
INSERT INTO `clt_forex` VALUES ('79', 'USDCAD', '1.26731', '1.26734', '1.26733', '1524212527');
INSERT INTO `clt_forex` VALUES ('80', 'AUDUSD', '0.7707', '0.7707', '0.7707', '1524212527');
INSERT INTO `clt_forex` VALUES ('81', 'GBPUSD', '1.40536', '1.40539', '1.40537', '1524212528');
INSERT INTO `clt_forex` VALUES ('82', 'EURUSD', '1.23308', '1.23309', '1.23308', '1524212528');
INSERT INTO `clt_forex` VALUES ('83', 'USDJPY', '107.548', '107.549', '107.5485', '1524212528');
INSERT INTO `clt_forex` VALUES ('84', 'USDCAD', '1.26731', '1.26734', '1.26733', '1524212528');
INSERT INTO `clt_forex` VALUES ('85', 'AUDUSD', '0.7707', '0.7707', '0.7707', '1524212528');
INSERT INTO `clt_forex` VALUES ('86', 'GBPUSD', '1.40536', '1.40539', '1.40537', '1524212529');
INSERT INTO `clt_forex` VALUES ('87', 'EURUSD', '1.23309', '1.2331', '1.2331', '1524212529');
INSERT INTO `clt_forex` VALUES ('88', 'USDJPY', '107.549', '107.549', '107.549', '1524212529');
INSERT INTO `clt_forex` VALUES ('89', 'USDCAD', '1.26728', '1.26732', '1.2673', '1524212529');
INSERT INTO `clt_forex` VALUES ('90', 'AUDUSD', '0.7707', '0.7707', '0.7707', '1524212529');
INSERT INTO `clt_forex` VALUES ('91', 'GBPUSD', '1.40536', '1.40539', '1.40537', '1524212530');
INSERT INTO `clt_forex` VALUES ('92', 'EURUSD', '1.23309', '1.2331', '1.2331', '1524212530');
INSERT INTO `clt_forex` VALUES ('93', 'USDJPY', '107.548', '107.549', '107.5485', '1524212530');
INSERT INTO `clt_forex` VALUES ('94', 'USDCAD', '1.26728', '1.26732', '1.2673', '1524212530');
INSERT INTO `clt_forex` VALUES ('95', 'AUDUSD', '0.7707', '0.7707', '0.7707', '1524212530');
INSERT INTO `clt_forex` VALUES ('96', 'GBPUSD', '1.40539', '1.40542', '1.40541', '1524212531');
INSERT INTO `clt_forex` VALUES ('97', 'EURUSD', '1.23309', '1.23311', '1.2331', '1524212531');
INSERT INTO `clt_forex` VALUES ('98', 'USDJPY', '107.548', '107.549', '107.5485', '1524212531');
INSERT INTO `clt_forex` VALUES ('99', 'USDCAD', '1.2673', '1.26733', '1.26731', '1524212531');
INSERT INTO `clt_forex` VALUES ('100', 'AUDUSD', '0.77071', '0.77071', '0.77071', '1524212531');
INSERT INTO `clt_forex` VALUES ('101', 'GBPUSD', '1.4054', '1.40542', '1.40541', '1524212532');
INSERT INTO `clt_forex` VALUES ('102', 'EURUSD', '1.23309', '1.2331', '1.2331', '1524212532');
INSERT INTO `clt_forex` VALUES ('103', 'USDJPY', '107.549', '107.549', '107.549', '1524212532');
INSERT INTO `clt_forex` VALUES ('104', 'USDCAD', '1.2673', '1.26733', '1.26731', '1524212532');
INSERT INTO `clt_forex` VALUES ('105', 'AUDUSD', '0.77071', '0.77071', '0.77071', '1524212532');
INSERT INTO `clt_forex` VALUES ('106', 'GBPUSD', '1.40539', '1.40542', '1.40541', '1524212533');
INSERT INTO `clt_forex` VALUES ('107', 'EURUSD', '1.23309', '1.2331', '1.2331', '1524212533');
INSERT INTO `clt_forex` VALUES ('108', 'USDJPY', '107.549', '107.549', '107.549', '1524212533');
INSERT INTO `clt_forex` VALUES ('109', 'USDCAD', '1.2673', '1.26733', '1.26731', '1524212533');
INSERT INTO `clt_forex` VALUES ('110', 'AUDUSD', '0.77071', '0.77071', '0.77071', '1524212533');
uj5u.com熱心網友回復：
求 MYSQL 達人.
uj5u.com熱心網友回復：
頂起來，求幫助。uj5u.com熱心網友回復：
SELECT symbol,DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H:%i') TIMESTAMP,(SELECT t1.price FROM clt_forex t1 WHERE t1.timestamp = MIN(t.timestamp) AND t1.symbol = t.symbol) 第一條,
(SELECT t1.price FROM clt_forex t1 WHERE t1.timestamp = MAX(t.timestamp) AND t1.symbol = t.symbol) 最后一條,
MIN(FROM_UNIXTIME(t.timestamp)) 每分鐘最早時間,
MAX(FROM_UNIXTIME(t.timestamp)) 每分鐘最晚時間,
MAX(t.price) 最高一條,
MIN(t.price) 最低一條 FROM clt_forex t
GROUP BY DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H:%i'),symbol
uj5u.com熱心網友回復：
沒看明白，你那個截圖 就是你要的查詢結果？ 那些資料是那個欄位 bid?ask? 還是price?【MYSQL按時間分組（每分鐘）查詢，求每個時間區段(每分鐘，每5分鐘)的第一條與最后一條記錄 】 每5分鐘 是什么意思？ 是要 每分鐘、每5分鐘 這2個結果集 的 集合？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/86709.html
標籤：MySQL
上一篇：無法定位序數3283于動態資料鏈接庫libmysql.dll上
下一篇：資料庫作業,使用查詢給變數賦值,遇到ERROR 1241 (21000): Operand should contain 1 column(s) 該怎么改呢?